请问这个SQL如何写,谢谢!初始:
T1 T2 T3
--------------
A 12 43
A MM MM
B XC ED
C DS XX
C ES EE结果:T1 T2 T3
--------------
A 12 43
MM MM
B XC ED
C DS XX
ES EE
T1 T2 T3
--------------
A 12 43
A MM MM
B XC ED
C DS XX
C ES EE结果:T1 T2 T3
--------------
A 12 43
MM MM
B XC ED
C DS XX
ES EE
insert into tb select 'A' , '12' , '43'
insert into tb select 'A' , 'MM' , 'MM'
insert into tb select 'B' , 'XC' , 'ED'
insert into tb select 'C' , 'DS' , 'XX'
insert into tb select 'C' , 'ES' , 'EE'
goselect t1=case when t2=(select min(t2) from tb where t1=a.t1) then t1 else '' end,
t2,t3
from tb ago
drop table tb/*
t1 t2 t3
---------- ---------- ----------
A 12 43
MM MM
B XC ED
C DS XX
ES EE(所影响的行数为 5 行)*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (T1 varchar(1),T2 varchar(2),T3 varchar(2))
insert into #T
select 'A','12','43' union all
select 'A','MM','MM' union all
select 'B','XC','ED' union all
select 'C','DS','XX' union all
select 'C','ES','EE';with T as
(
select id=row_number()over(partition by T1 order by T1),* from #T
)
select T1=case id when (select min(id) from T where T1=t.T1) then T1 else '' end, T2,T3 from T as t/*
T1 T2 T3
---- ---- ----
A 12 43
MM MM
B XC ED
C DS XX
ES EE
*/