id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
18509 7704 33211BE29C5E4C075573E06D7FF5776C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
12506 7704 33211BE29C5E4C075573E06D7FF5776C结果:
id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
id不能有重复, 取最大scale的stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
18509 0 C71D4E4205F24CFCB8EDC7D4460B61F1
18509 7704 33211BE29C5E4C075573E06D7FF5776C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 0 C71D4E4205F24CFCB8EDC7D4460B61F1
12506 7704 33211BE29C5E4C075573E06D7FF5776C结果:
id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
id不能有重复, 取最大scale的stockid
select * from table1 as t where not exists(select 1 from table1 where ID=t.ID and scale>t.scale)
from table1 a
right join (select id,max(scale) as scale from table1 group by id) b on a.id=b.id and a.scale=b.scale
group by a.id,a.scale
---------------------------------
id scale stockid
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1
我把stockid改成最小的结果就错了id scale stockid
-------------------------------------------
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
18509 0 Z71D4E4205F24CFCB8EDC7D4460B61F1
18509 7704 33211BE29C5E4C075573E06D7FF5776C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 0 Z71D4E4205F24CFCB8EDC7D4460B61F1
12506 7704 33211BE29C5E4C075573E06D7FF5776C
Oracle可以用也可用row_number()over(partition by ID order by Scale desc)
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
2 18509 0 Z71D4E4205F24CFCB8EDC7D4460B61F1 AAAOVmAAIAAAW8FAAB
3 18509 7704 33211BE29C5E4C075573E06D7FF5776C AAAOVmAAIAAAW8FAAC
4 12506 7704 z3211BE29C5E4C075573E06D7FF5776C AAAOVmAAIAAAW8FAAD
5 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1 AAAOVmAAIAAAW8FAAE
6 12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C AAAOVmAAIAAAW8FAAF
我电脑上的数据
查询结果:
1 18509 7704 33211BE29C5E4C075573E06D7FF5776C
2 18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
3 12506 19999 C71D4E4205F24CFCB8EDC7D4460B61F1
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([id] int,[scale] int,[stockid] nvarchar(32))
Insert #
select 18509,7704,N'F9320BE29C5E4C07A6573E0D6D7FF56C' union all
select 18509,0,N'C71D4E4205F24CFCB8EDC7D4460B61F1' union all
select 18509,7704,N'33211BE29C5E4C075573E06D7FF5776C' union all
select 12506,7704,N'F9320BE29C5E4C07A6573E0D6D7FF56C' union all
select 12506,0,N'C71D4E4205F24CFCB8EDC7D4460B61F1' union all
select 12506,7704,N'33211BE29C5E4C075573E06D7FF5776C'
Go
SELECT *
from # as t
where
not exists(select 1 from # where ID=t.ID and scale>t.scale OR(scale=t.scale AND stockid>t.stockid ) )
/*
id scale stockid
18509 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
12506 7704 F9320BE29C5E4C07A6573E0D6D7FF56C
*/