如何挑选满足条件1，但是不满足条件2的记录

[weasea]

ID SN Type
1 000 1
2 000 2
3 001 1
4 002 1

000既满足type=1 又满足type=2

找出的记录
001
002

[liangCK]

什么意思??

[liangCK]

---------------------------------
--  Author: liangCK 小梁
--  Date  : 2008-11-24 10:02:41
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,SN VARCHAR(3),Type INT)
INSERT INTO @T
SELECT 1,'000',1 UNION ALL
SELECT 2,'000',2 UNION ALL
SELECT 3,'001',1 UNION ALL
SELECT 4,'002',1

--SQL查询如下:

SELECT *
FROM @T AS A
WHERE Type=1
    AND NOT EXISTS(
            SELECT *
            FROM @T
            WHERE SN=A.SN
                AND ID<>A.ID
                AND Type=2
        )

/*
ID          SN   Type
----------- ---- -----------
3           001  1
4           002  1

(2 行受影响)

*/

[中国风]

select * from t a where (select count(distinct Type) from t where SN=a.SN and Type in(1,2))=2

還是

select SN from t where Type in(1,2) group by SN having count(distinct Type)=2

[dawugui]

[Quote=引用楼主 weasea 的帖子:]
ID SN Type
1 000 1
2 000 2
3 001 1
4 002 1

000既满足type=1 又满足type=2

找出的记录
001
002
[/Quote]

select distinct sn from tb where sn = 1 and sn not in (select distinct sn from tb where type = 2)

[dotnba]

问题不清楚噢

[dawugui]

create table tb(ID INT,SN VARCHAR(3),Type INT)
INSERT INTO tb
SELECT 1,'000',1 UNION ALL
SELECT 2,'000',2 UNION ALL
SELECT 3,'001',1 UNION ALL
SELECT 4,'002',1

select distinct sn from tb where sn = 1 and sn not in (select distinct sn from tb where type = 2)
drop table tb

/*
sn   
---- 
001

（所影响的行数为 1 行）

*/

[ljhcy99]

select count(Type),SN
from table
group by SN
having count(type)<2