declare @t table(ID1 int,ID2 int) insert @t select 15, 17 union all select 15, 18 union all select 16, 19select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2)/*结果: ID1 ID2 ----------- ----------- 15 17 16 19 */
declare @t table(ID1 int,ID2 int) insert @t select 15, 17 union all select 15, 18 union all select 16, 19----方法1: select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2) ----方法2: select * from @t as a where ID2 = (select min(ID2) from @t where ID1 = a.ID1) ----方法3: select a.* from @t as a INNER JOIN (select ID1, min(ID2) as ID2 from @t group by ID1) as b on a.ID1 = b.ID1 and a.ID2 = b.ID2 /*结果: ID1 ID2 ----------- ----------- 15 17 16 19 */
新建的SQL群 19078538 欢迎大家加入,共同研究,一起进步!
create table test(id1 int,id2 int) insert test select 15,17 union all select 15,18 union all select 16,19
select id1,min(id2) id2 from test group by id1drop table test/----------------------/结果 15 17 16 19
----方法1: select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2) 这句里面的子查询select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2中 为什么写成select 1 而不写成select * 呢?这个有什么区别
insert @t
select 15, 17 union all
select 15, 18 union all
select 16, 19select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2)/*结果:
ID1 ID2
----------- -----------
15 17
16 19
*/
insert @t
select 15, 17 union all
select 15, 18 union all
select 16, 19----方法1:
select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2)
----方法2:
select * from @t as a where ID2 = (select min(ID2) from @t where ID1 = a.ID1)
----方法3:
select a.* from @t as a
INNER JOIN (select ID1, min(ID2) as ID2 from @t group by ID1) as b
on a.ID1 = b.ID1 and a.ID2 = b.ID2
/*结果:
ID1 ID2
----------- -----------
15 17
16 19
*/
insert test select 15,17
union all select 15,18
union all select 16,19
select id1,min(id2) id2 from test group by id1drop table test/----------------------/结果
15 17
16 19
高手真多啊
select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2)
这句里面的子查询select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2中
为什么写成select 1 而不写成select * 呢?这个有什么区别
select * from tbl a
where not exists(select 1 from tbl where a.id1=id1 and a.id2>id2 )
select * from @t as a where not exists(select 1 from @t where ID1 = a.ID1 and ID2 < a.ID2)
这句话意思看不懂,不知能否给解释一下,谢谢。
binglengdexin2
牛!!:)