sql语句急求

[rottenvine]

有这样一个表

col1 col2
A 1
A 2
B 1
B 1
C 2
C 3
D 1
D 3
E 1
E 1


如何用一条sql语句查出

col1
B
E

即要查出col1, col2值一样的记录，而且col2=1

[rottenvine]

col1 col2 A 1 A 2 B 1 B 1 C 2 C 3 D 1 D 3 E 1 E 1 F 2 F 2 G 1 G 1 G 2 要求只查出 B, E

[hery2002]

DECLARE @t_TB TABLE ([col1] NVARCHAR(5),[col2] int);
INSERT INTO @t_TB VALUES
('A','1'),
('A','2'),
('B','1'),
('B','1'),
('C','2'),
('C','3'),
('D','1'),
('D','3'),
('E','1'),
('E','1'),
('F','2'),
('F','2');
select col1 from @t_TB
where col2=1 and col1 in (
SELECT col1 FROM @t_TB
group by col1 
having count(distinct col1+ltrim(col2)) =1
)
group by col1
/*
 col1
-----
B
E
*/

[rottenvine]

哥们，你求急了， 多谢!!