update f set state = 4
from f
left join
(select distinct fid from af group by fid having count(1) > 1) t
on f.fid = t.fid
from f
left join
(select distinct fid from af group by fid having count(1) > 1) t
on f.fid = t.fid
update F
set state = 4
from F
inner join AF
on F.FID = AF.FID
inner join (select AID, count(*) as dup from AF where AID = @AID group by AID HAVING COUNT(*) > 1) T
on AF.AID = T.AID
select distinct fid from af WHERE Aid=1 group by fid having count(1) = 1光运行这么一句,你就可以看到AF表中FID为2,4都查出来了.应该查出来的是2. 是由aid作为条件的...........
update f set state = 4
from f
left join
(select distinct fid from af
where aid = @ActivityId group by fid having count(1) > 1) t
on f.fid = t.fid加个条件就好了
汗AF表中有FID为2,4的数据么?
我的SQL这么强大,连没有的数据都能查出来,哇哈哈
aid=1作为条件.要求F表中的结果为
FID FName state
1 f1 3
2 f2 2(这个值更新成2)
3 f3 3
4 f4 3
WHERE exists(SELECT count(fid) FROM AF WHERE F.AID= @ActivityId and F.fid=AF.fid GROUP BY FID HAVING COUNT(FID) = 1)
FID FName state
1 f1 4(这个值更新成4)
2 f2 4(这个值更新成4)
3 f3 3
4 f4 3
WHERE exists(SELECT count(fid) FROM AF WHERE AF.AID= 1 and F.fid=AF.fid GROUP BY FID HAVING COUNT(FID) = 1)
AID FID
1 4(把这个变成4)
1 2
2 1
3 2
3 3
4 3 F中的数据应该更新成:FID FName state
1 f1 3
2 f2 4(只有这里变成4才对.但你写的却更新了两条)
3 f3 3
4 f4 3
没仔细看你的SQLUPDATE F SET [state] = 4
WHERE FID IN (SELECT FID FROM AF WHERE FID IN (SELECT FID FROM AF WHERE AID= @ActivityId) GROUP BY FID HAVING COUNT(FID) = 1)
其实这样你还可以加一个条件的,像下面这样
UPDATE F SET [state] = 4
WHERE FID IN (SELECT FID FROM AF WHERE AID = @ActivityId AND FID IN (SELECT FID FROM AF WHERE AID= @ActivityId) GROUP BY FID HAVING COUNT(FID) = 1)
因为这个语句对索引依赖比较严重,CREATE TABLE F
(
FID char(1),
FName char(10),
state char(1)
)
INSERT INTO F
SELECT '1','f1','3'
UNION SELECT '2','f2','3'
UNION SELECT '3','f3','3'
UNION SELECT '4','f4','3'CREATE TABLE AF
(
AID char(1),
FID char(1)
)
INSERT INTO AF
SELECT '1','1'
UNION SELECT '1','2'
UNION SELECT '2','1'
UNION SELECT '3','2'
UNION SELECT '3','3'
UNION SELECT '4','3'SELECT * FROM F
WHERE exists(
select * from AF t2
RIGHT JOIN
(
SELECT FID FROM AF GROUP BY AF.FID HAVING count(1) > 1
) t4
ON t2.FID = t4.FID
WHERE t2.FID = F.FID AND
exists(
select 1 from AF t3 where t3.AID = '1' and t3.AID = t2.AID
)
)IN并不一定就是坏事呵呵