SELECT T1.* FROM (SELECT T0.* FROM USERS T0 WHERE (NOT EXISTS (SELECT 1 FROM Users WHERE ParentId = T0.UserId AND Type >= 2)) AND Type > 2) T1 WHERE EXISTS (SELECT 1 FROM users WHERE ParentID = T1.UserId)测试通过。 第一个Not Exists用来筛选下线没有〉=2的;第二个Exists用来筛选有下线的。两者的位置可以互换。
哦,更正,Select T0.* 更改为 Update T0 Set Type=2
更正的更正...UPDATE USERS SET Type = 2 WHERE (UserID = (SELECT T1.UserId FROM (SELECT T0.* FROM USERS T0 WHERE (NOT EXISTS (SELECT 1 FROM Users WHERE ParentId = T0.UserId AND Type >= 2)) AND Type > 2) T1 WHERE EXISTS (SELECT 1 FROM users WHERE ParentID = T1.UserId)))
set Type=2
from
[User] a
where
exists(select 1 from [User] where ParentId=a.UserId ) and Type<2
set Type=2
from
[User] a
where
exists(select 1 from [User] where ParentId=a.UserId ) and Type<2
检查User表中的所有Type=3的会员,遍历这些会员,如果某会员u123下属的所有会员(ParentId=父会员UserId)的Type都 <2,那么将这个u123会员的Type设置为2。举个列子
UserId Type UserName ParentId(父会员的UserId)
1 3 un12 0
2 1 xbox 1
3 1 com 1
4 1 bess 1
5 2 uwes 0
6 3 w34 0
7 1 ssdf 6
8 1 sd3ff 6
9 1 s3wd 6
10 2 tew2 6这个例子中un12和w34符合Type=3的条件,但是只有un12的Type应该设置为2,因为他下属的xbox,com,bess的Type都<2
SELECT T1.*
FROM (SELECT T0.*
FROM USERS T0
WHERE (NOT EXISTS
(SELECT 1
FROM Users
WHERE ParentId = T0.UserId AND Type >= 2)) AND Type > 2) T1
WHERE EXISTS
(SELECT 1
FROM users
WHERE ParentID = T1.UserId)测试通过。
第一个Not Exists用来筛选下线没有〉=2的;第二个Exists用来筛选有下线的。两者的位置可以互换。
SET Type = 2
WHERE (UserID =
(SELECT T1.UserId
FROM (SELECT T0.*
FROM USERS T0
WHERE (NOT EXISTS
(SELECT 1
FROM Users
WHERE ParentId = T0.UserId AND Type >= 2)) AND Type > 2)
T1
WHERE EXISTS
(SELECT 1
FROM users
WHERE ParentID = T1.UserId)))