Select C1,Min(C2),C3 from TableName Where C2=0 Group By C1,C3
Select C1,Min(C2) As C2,C3 from TableName Where C2=0 Group By C1,C3
看不太懂,相对于同一个C1,C3的值最小的纪录 那么获得的结果肯定是多记录 即1000 10 1001 10 1003 12? 这样的要求分组用min函数就ok啊 select c1,min(c3) from table where c2 <> 0 group by c1,c3
哦,理解错了Select * from TableName Where C2<>0 And Not Exists(Select * from TableName Where C1=A.C1 And C2=A.C2 And C3<A.C3)
楼主应该是C2不等于0,相同的C1的情况下,并且C3最小的记录吧。--建立测试环境 Create table TEST (C1 Int, C2 Int, C3 Int) --插入数据 Insert TEST Values(1000, 0, 10) Insert TEST Values(1001, 1, 10) Insert TEST Values(1000, 1, 11) Insert TEST Values(1003, 0, 12) Insert TEST Values(1003, 0, 14) --测试 Select * from TEST A Where C2<>0 And Not Exists(Select * from TEST Where C1=A.C1 And C2=A.C2 And C3<A.C3) Order By C1 --删除测试环境 Drop Table TEST --结果 /* C1 C2 C3 1000 1 11 1001 1 10 */
Select * from TEST A Where C2<>0 And Not Exists(Select * from TEST Where C1=A.C1 And C2=A.C2 And C3<A.C3) Order By C1 这个可以行,但是这个TEST 表如果是一个select 语句查出来的,那该怎么办?
将你查询的语句放到临时表中或者将TEST替换为你的语句eg: Select * from (Select ... from ...) A .....
那么获得的结果肯定是多记录
即1000 10
1001 10
1003 12?
这样的要求分组用min函数就ok啊
select c1,min(c3)
from table
where c2 <> 0
group by c1,c3
Where C2<>0 And Not Exists(Select * from TableName Where C1=A.C1 And C2=A.C2 And C3<A.C3)
Create table TEST
(C1 Int,
C2 Int,
C3 Int)
--插入数据
Insert TEST Values(1000, 0, 10)
Insert TEST Values(1001, 1, 10)
Insert TEST Values(1000, 1, 11)
Insert TEST Values(1003, 0, 12)
Insert TEST Values(1003, 0, 14)
--测试
Select * from TEST A
Where C2<>0 And Not Exists(Select * from TEST Where C1=A.C1 And C2=A.C2 And C3<A.C3)
Order By C1
--删除测试环境
Drop Table TEST
--结果
/*
C1 C2 C3
1000 1 11
1001 1 10
*/
Where C2<>0 And Not Exists(Select * from TEST Where C1=A.C1 And C2=A.C2 And C3<A.C3)
Order By C1
这个可以行,但是这个TEST 表如果是一个select 语句查出来的,那该怎么办?
Select * from (Select ... from ...) A
.....