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)
8 个解决方案
#1
等下
#2
那应该是这个啊
22 1 0
22 1 0
#3
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
*/
#4
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)
#5
表 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
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
#6
SELECT a FROM s t where b=(SELECT max(b) FROM s where a=t.a) and c=0
#7
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
#8
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
#1
等下
#2
那应该是这个啊
22 1 0
22 1 0
#3
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
*/
#4
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)
#5
表 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
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
#6
SELECT a FROM s t where b=(SELECT max(b) FROM s where a=t.a) and c=0
#7
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
#8
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