需要行号 declare @t table(is_id int identity(1,1),id nvarchar(2),num int,mb nvarchar(2)) insert into @t select '01', 2, 'q' union all select '01', 2, 'y' union all select '02', 1, 'h' union all select '03', 5, 'y' union all select '03', 5, 'j'select case when is_id=(select min(is_id) from @t where id=T.id and num=T.num) then id else '-' end id, case when is_id=(select min(is_id) from @t where id=T.id and num=T.num) then rtrim(num) else '-' end num, mb from @t T
create table tb(id char(2), num int, mb char(1)) insert tb select '01', 2, 'q' union all select '01', 2, 'y' union all select '02', 1, 'h' union all select '03', 5, 'y' union all select '03', 5, 'j'select id=isnull(tmp.id, '-'), num=isnull(tmp.num, '-'), mb=tb.mb from tb left join (select id,num,mb=max(mb) from tb group by id, num) tmp on tb.id=tmp.id and tb.num=tmp.num and tb.mb=tmp.mbdrop table tb
declare @t table(is_id int identity(1,1),id nvarchar(2),num int,mb nvarchar(2))
insert into @t select
'01', 2, 'q' union all select
'01', 2, 'y' union all select
'02', 1, 'h' union all select
'03', 5, 'y' union all select
'03', 5, 'j'select
case when is_id=(select min(is_id) from @t where id=T.id and num=T.num) then id else '-' end id,
case when is_id=(select min(is_id) from @t where id=T.id and num=T.num) then rtrim(num) else '-' end num,
mb
from @t T
insert tb select '01', 2, 'q'
union all select '01', 2, 'y'
union all select '02', 1, 'h'
union all select '03', 5, 'y'
union all select '03', 5, 'j'select
id=isnull(tmp.id, '-'),
num=isnull(tmp.num, '-'),
mb=tb.mb
from tb
left join
(select id,num,mb=max(mb)
from tb
group by id, num)
tmp on tb.id=tmp.id and tb.num=tmp.num and tb.mb=tmp.mbdrop table tb