SQL最大值查询？

[llvsy]

有这样一个表 s
a b c
11 2 1
11 1 0
22 1 1
22 1 0
33 1 1
33 2 2
查询出a字段 其中如果b字段中的值是最大的(如果相等满足c字段等于0) 并且c字段等于0
象这个表s的结果是 11 22
我写的只能查出11
SELECT a FROM s where b=(SELECT max(b) FROM s)

[ws_hgo]

等下

[ws_hgo]

那应该是这个啊
22 1 0

[csdyyr]

DECLARE @TB TABLE(a INT,      b  INT,     c  INT)
INSERT @TB
SELECT 11,  2,  1 UNION ALL 
SELECT 11,  1,  0 UNION ALL 
SELECT 22,  1,  1 UNION ALL 
SELECT 22,  1,  0 UNION ALL 
SELECT 33,  1,  1 UNION ALL 
SELECT 33,  2,  2

SELECT A,MAX(B) AS B FROM @TB WHERE C=0 GROUP BY A
/*
A           B           
----------- ----------- 
11          1
22          1
*/

[caixia615]

SELECT DISTINCT A FROM S WHERE C=0 AND EXISTS(SELECT * FROM(SELECT A,MAX(B)B FROM S GROUP BY A)B WHERE S.A=B.A AND S.B=B.B)

[llvsy]

表 s 敲错了
a b c
11 2 0
11 1 1
22 1 1
22 1 0
33 1 1
33 2 2
SELECT a FROM s where b=(SELECT max(b) FROM s) and c=0

[等不到来世]

SELECT a FROM s t where b=(SELECT  max(b) FROM s where a=t.a) and c=0 

[ws_hgo]

DECLARE @TB TABLE(a INT,b INT,c INT)
INSERT @TB
SELECT 11,2,1 UNION ALL 
SELECT 11,1,0 UNION ALL 
SELECT 22,1,1 UNION ALL 
SELECT 22,1,0 UNION ALL 
SELECT 33,1,1 UNION ALL 
SELECT 33,2,2

select * from @TB BB where not exists (select * from @TB where a=BB.a and b>BB.b) and c=0 

[ChinaJiaBing]

declare @s table(a int,b int,c int)
insert into @s select 11,2,1
    union all  select 11,1,0
    union all  select 22,1,1
    union all  select 22,1,0
    union all  select 33,1,1
    union all  select 33,2,2
--select a from @s j where b=(select b from @s where j.a=a and j.b<b)
--select * from @s
select * from @s j where not exists(select 1 from @s where a=j.a and b>j.b)
and c=0