22,302
社区成员




create table tb(ID int, XH int, NUM float)
insert into tb
select 1, 3, 100 union all
select 1, 3, 100 union all
select 1, 3, 100 union all
select 1, 4, 200 union all
select 1, 4, 200 union all
select 2, 3, 50 union all
select 2, 3, 50 union all
select 3, 5, 300 union all
select 3, 5, 300 union all
select 3, 5, 300 union all
select 3, 5, 300 union all
select 4, 1, 2.5
select * , px=identity(int,1,1) into tmp from tb
--只判断id
select ID ,XH ,
NUM = (case when px = (select min(px) from tmp where id = t.id) then ltrim(num) else '' end)
from tmp t
/*
ID XH NUM
----------- ----------- ----------------------
1 3 100
1 3
1 3
1 4
1 4
2 3 50
2 3
3 5 300
3 5
3 5
3 5
4 1 2.5
(所影响的行数为 12 行)
*/
--同时判断id , xh
select ID ,XH ,
NUM = (case when px = (select min(px) from tmp where id = t.id and XH = t.XH) then ltrim(num) else '' end)
from tmp t
/*
ID XH NUM
----------- ----------- ----------------------
1 3 100
1 3
1 3
1 4
1 4
2 3 50
2 3
3 5 300
3 5
3 5
3 5
4 1 2.5
(所影响的行数为 12 行)
*/
drop table tb , tmp
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID int, XH int, NUM float)
insert into #
select 1, 3, 100 union all
select 1, 3, 100 union all
select 1, 3, 100 union all
select 1, 4, 200 union all
select 1, 4, 200 union all
select 2, 3, 50 union all
select 2, 3, 50 union all
select 3, 5, 300 union all
select 3, 5, 300 union all
select 3, 5, 300 union all
select 3, 5, 300 union all
select 4, 1, 2.5
;with cte as
(
select row=row_number()over(partition by ID,XH order by ID), * from #
)
select ID, XH, NUM=case row when 1 then ltrim(NUM) else '' end from cte
/*
ID XH NUM
----------- ----------- -----------------------
1 3 100
1 3
1 3
1 4 200
1 4
2 3 50
2 3
3 5 300
3 5
3 5
3 5
4 1 2.5
*/