SQL 如何实现上下行去重复，并合并成一列

最新推荐文章于 2025-10-14 21:05:41 发布

原创最新推荐文章于 2025-10-14 21:05:41 发布 · 1.1w 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#上 #g

本文介绍如何使用SQL去除数据表中上下行重复值并合并成一列。通过ROW_NUMBER()函数进行行比较，然后利用STUFF()函数进行合并，最终实现数据去重和合并效果。

数据表“TB_GY”，有两个字段“shortName”,"typeName"

执行前

完成效果

我们要达到的效果不是，合并所有重复，而是合并上下行相同的重复值。

1. 我们先用 ROW_NUMBER() OVER 要把TB_GY表做上下行比较，去重复。

select A.shortName ,A.typeName from (Select shortName,typeName， ROW_NUMBER() over(order by typeName ) as rows from TB_GY） A left join （Select shortName,typeName， ROW_NUMBER() over(order by typeName ) as rows from TB_GY ) B on A.rows=B.rows-1 where A.shortName<>B.shortName or B.shortName is null

where 要加一条 B.shortName is null ，因为最后一行的B.shorName 是 Null值。

效果如下：

如上图，我们完成上下行去重的工作。

2.我们再用 stuff() 来合并行

select D.typeName, stuff((select A.shortName ,A.typeName from (Select shortName,typeName， ROW_NUMBER() over(order by typeName ) as rows from TB_GY） A left join （Select shortName,typeName， ROW_NUMBER() over(order by typeName ) as rows from TB_GY ) B on A.rows=B.rows-1 where A.shortName<>B.shortName or B.shortName is null) C

where C.typename=D.typename FOR XML PATH('')),1, 1, '') as shortnameLJ from

(select A.shortName ,A.typeName from (Select shortName,typeName， ROW_NUMBER() over(order by typeName ) as rows from TB_GY） A left join （Select shortName,typeName， ROW_NUMBER() over(order by typeName ) as rows from TB_GY ) B on A.rows=B.rows-1 where A.shortName<>B.shortName or B.shortName is null) D
group by D.typeName order by D.typeName

这样我们就得到了我们想要的效果。

我们再整理一下思路，先进行上下行对比去重复，再把去重复的结果进行合并。

下面是我在实际工作中，进行多表关联的SQL语句。

select D.typeName, stuff((select' ' +C.shortname from
(select A.shortName ,A.typeName,A.typeid from (Select t1.shortName,t2.typeName,t.typeid, ROW_NUMBER() over(order by t.typeId, ABS(t.t_gx)) as rows from t_gyBody t left join t_work t1 On t.t_workId = t1.id
left join t_gyType t2 on t.typeid=t2.id where t.t_code='0301040001' and t.t_CRAFTNUM='01' ) A left join
(Select t1.shortName,t2.typeName, ROW_NUMBER() over(order by t.typeId, ABS(t.t_gx)) as rows from t_gyBody t left join t_work t1 On t.t_workId = t1.id
left join t_gyType t2 on t.typeid=t2.id where t.t_code='0301040001' and t.t_CRAFTNUM='01' ) B on A.rows=B.rows-1
where A.shortName<>B.shortName or B.shortName is null) C
where C.typename=D.typename FOR XML PATH('')),1, 1, '') as shortnameLJ from
(select A.shortName ,A.typeName,A.typeid from (Select t1.shortName,t2.typeName,t.typeid, ROW_NUMBER() over(order by t.typeId, ABS(t.t_gx)) as rows from t_gyBody t left join t_work t1 On t.t_workId = t1.id
left join t_gyType t2 on t.typeid=t2.id where t.t_code='0301040001' and t.t_CRAFTNUM='01' ) A left join
(Select t1.shortName,t2.typeName, ROW_NUMBER() over(order by t.typeId, ABS(t.t_gx)) as rows from t_gyBody t left join t_work t1 On t.t_workId = t1.id
left join t_gyType t2 on t.typeid=t2.id where t.t_code='0301040001' and t.t_CRAFTNUM='01' ) B on A.rows=B.rows-1
where A.shortName<>B.shortName or B.shortName is null) D
group by D.typeName order by D.typeName