如何根据另一个字段大小来去重？

[kyleledger]

例如
id td ed
1 100 5
1 200 6
2 100 4
2 200 2
2 50 5
3 40 2
3 30 1

id去重，但是根据对应td的大小，取出id对应td最小的一行字段
结果
id td ed
1 100 5
2 50 5
3 30 1

用sql语句怎么实现呢？谢谢！

[Ginnnnnnnn]

select * from table a where not exists(select * from table where id = a.id and tid < a.tid)

[道素]

WITH a(id,td,ed) AS (	
select 1,100,5 UNION
select 1,200,6 UNION
select 2,100,4 UNION
select 2,200,2 UNION
select 2,50,5 UNION
select 3 ,40,2 UNION
select 3,30,1
)
SELECT * FROM (SELECT * ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY td) AS seq FROM a) AS t WHERE seq=1

[读万卷书不如行万里路呀]

引用 1 楼 KanzakiOrange 的回复:

select * from table a where not exists(select * from table where id = a.id and tid < a.tid)

是可以的，好厉害，但是我没怎么看懂。

[读万卷书不如行万里路呀]

引用 2 楼 ch21st 的回复:

WITH a(id,td,ed) AS (	
select 1,100,5 UNION
select 1,200,6 UNION
select 2,100,4 UNION
select 2,200,2 UNION
select 2,50,5 UNION
select 3 ,40,2 UNION
select 3,30,1
)
SELECT * FROM (SELECT * ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY td) AS seq FROM a) AS t WHERE seq=1

[kyleledger]

引用 1 楼 KanzakiOrange 的回复:

select * from table a where not exists(select * from table where id = a.id and tid < a.tid)

真的可以呀，好谢谢！如果后面还有很多字段，是不是也可以这样？我先去尝试下

[kyleledger]

引用 4 楼 nanfeiyannan 的回复:

[quote=引用 2 楼 ch21st 的回复:]

WITH a(id,td,ed) AS (	
select 1,100,5 UNION
select 1,200,6 UNION
select 2,100,4 UNION
select 2,200,2 UNION
select 2,50,5 UNION
select 3 ,40,2 UNION
select 3,30,1
)
SELECT * FROM (SELECT * ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY td) AS seq FROM a) AS t WHERE seq=1

[/quote]谢谢回答，不过这样感觉好麻烦，而且还得我手动输入字段内的内容，我这个是举个例子，实际内容字段会很多，不过还是要谢谢啊，你这方法我也要学习学习

[kyleledger]

引用 3 楼 nanfeiyannan 的回复:

[quote=引用 1 楼 KanzakiOrange 的回复:] select * from table a where not exists(select * from table where id = a.id and tid < a.tid)

是可以的，好厉害，但是我没怎么看懂。[/quote]我也没看懂，研究学习下。

[道素]

引用 6 楼 kyleledger 的回复:

[quote=引用 4 楼 nanfeiyannan 的回复:] [quote=引用 2 楼 ch21st 的回复:]

WITH a(id,td,ed) AS (	
select 1,100,5 UNION
select 1,200,6 UNION
select 2,100,4 UNION
select 2,200,2 UNION
select 2,50,5 UNION
select 3 ,40,2 UNION
select 3,30,1
)
SELECT * FROM (SELECT * ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY td) AS seq FROM a) AS t WHERE seq=1

[/quote]谢谢回答，不过这样感觉好麻烦，而且还得我手动输入字段内的内容，我这个是举个例子，实际内容字段会很多，不过还是要谢谢啊，你这方法我也要学习学习[/quote] with那段你不需要输入，那是建个临时表用于测试，你有实际的表，直接下面的a换成你实际的表就行了 SELECT * FROM (SELECT * ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY td) AS seq FROM a) AS t WHERE seq=1