高分悬赏！ SQL去除重复项！
如下表：
ID1 ID2
1517
1518
1619
我想通过查询获得如下结果：
ID1 ID2
1517
1619
DISTINCT函数只能包含相同的字段，但我想查询出不相同的ID1和ID2。
[解决办法]
declare @t table(ID1 int,ID2 int)
insert @t
select 15,17 union all
select 15,18 union all
select 16,19
select * 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
*/
[解决办法]
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 id1
drop table test
/----------------------/结果
1517
1619
[解决办法]
----方法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 * 呢?这个有什么区别
[解决办法]
如果表不止2列的話,binglengdexin2() 的方法就行不通了
所以還是hellowork(一两清风) 的方法更通用,呵呵
[解决办法]
多多益善啊，如果对ID2没有要求的话，还是选择使用MIN和MAX函数性能好一些！
[解决办法]
select * from tbl a
where not exists(select 1 from tbl where a.id1=id1 and a.id2> id2 )
