22,301
社区成员




Set nocount on
go
Create Table #t
(col1 varchar(15),
col2 varchar(15),
col3 varchar(15),
col4 varchar(15),
col6 varchar(15)
)
go
insert into #t
Select 'aa','bb','cc','dd','ee'
union all select 'aa','bb','cc','ff','gg'
union all select 'aa','bb','cc','gg','rr'
union all select '11','22','33','44','55'
union all select '11','22','33','12s4','asdf55'
union all select '11','232','33','44','55'
go
Select * from #t
go
Select *,idx=0 into #t2 from #t
go
select col1,col2,col3, idx = identity(int, 1, 1) into #grp from #t
group by col1,col2,col3
go
declare @i int, @gid int
select @i = 1,@gid = 0
update T2 set @i = Case when P.idx = @gid then @i + 1 else 1 end, T2.idx = @i, @gid = P.idx
from #t2 T2
inner join #grp P on T2.col1=P.col1 and T2.col2=P.col2 and T2.col3 = P.col3
Select col1 = case T2.idx when 1 then T2.col1 else ''end ,col2 = case T2.idx when 1 then T2.col2 else ''end,
col3 = case T2.idx when 1 then T2.col3 else ''end, col4, col6
from #t2 T2
inner join #grp P on T2.col1=P.col1 and T2.col2=P.col2 and T2.col3 = P.col3
drop table #grp
drop table #t2
drop table #t
col1 col2 col3 col4 col6
--------------- --------------- --------------- --------------- ---------------
aa bb cc dd ee
aa bb cc ff gg
aa bb cc gg rr
11 22 33 44 55
11 22 33 12s4 asdf55
11 232 33 44 55
col1 col2 col3 col4 col6
--------------- --------------- --------------- --------------- ---------------
aa bb cc dd ee
ff gg
gg rr
11 22 33 44 55
12s4 asdf55
11 232 33 44 55
select t.*
from (select a.*,
row_number() over(partition by HeadersNo,MachineNo,Description order by getdate()) rn
from tb a) t
where t.rn=1
headerNo machineNO descrption artNo qty repartno repqty
---------- ---------- -------------------- -------------------- ----------- -------------------- -----------
HD01 0101520 电池出问题 102020 2 102020 2
101010 2 202020 2
126888 2 102020 2
HD02 01012221 D电机故障 102020 2 102020 2
HD03 12312312 突然停机 102020 2 102020 2
102020 2 102020 2
HD04 12312344 皮带松了 102020 2 102020 2
(7 row(s) affected)
CREATE TABLE #tp
(
headerNo VARCHAR(10),
machineNO VARCHAR(10),
descrption nVARCHAR(20),
artNo VARCHAR(20),
qty INT ,
repartno varchar(20) ,
repqty INT
)
insert INTO #tp SELECT 'HD01','0101520',N'电池出问题','102020',2,'102020',2
insert INTO #tp SELECT 'HD01','0101520',N'电池出问题','101010',2,'202020',2
insert INTO #tp SELECT 'HD01','0101520',N'电池出问题','126888',2,'102020',2
insert INTO #tp SELECT 'HD02','01012221',N'D电机故障','102020',2,'102020',2
insert INTO #tp SELECT 'HD03','12312312',N'突然停机','102020',2,'102020',2
insert INTO #tp SELECT 'HD03','12312312',N'突然停机','102020',2,'102020',2
insert INTO #tp SELECT 'HD04','12312344',N'皮带松了','102020',2,'102020',2
SELECT CASE when row=1 THEN headerNo ELSE '' END headerNo,
CASE when row=1 THEN machineNO ELSE '' END machineNO,
CASE when row=1 THEN descrption ELSE '' END descrption
,artNo,qty,repartno,repqty
FROM
(
SELECT *,row_number()OVER(PARTITION BY headerNo,machineNO,descrption ORDER BY GETDATE())row
FROM #tp
)M
headerNo machineNO descrption artNo qty repartno repqty
---------- ---------- -------------------- -------------------- ----------- -------------------- -----------
HD01 0101520 电池出问题 102020 2 102020 2
101010 2 202020 2
126888 2 102020 2
HD02 01012221 D电机故障 102020 2 102020 2
HD03 12312312 突然停机 102020 2 102020 2
102020 2 102020 2
HD04 12312344 皮带松了 102020 2 102020 2
(7 row(s) affected)