SQL Server 2005 行号、合并、分组

最新推荐文章于 2022-11-22 18:17:27 发布

Yole

最新推荐文章于 2022-11-22 18:17:27 发布

阅读量1k

点赞数

分类专栏：技术专栏文章标签： sql server 2005 合并 row_numberover FOR XML PATH

本文链接：https://blog.youkuaiyun.com/yole_grise/article/details/17577661

版权

技术专栏专栏收录该内容

46 篇文章

订阅专栏

本文演示了如何在SQL Server 2005中使用`row_number()`和`FOR XML PATH`来处理数据。首先创建了一个测试表[a]，接着通过`row_number() over(PARTITION BY)`生成行号，然后通过XML PATH方法将相同djbh的spid和shl字段进行合并，最后展示了如何查询重复数量大于2的单据号。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([djbh] varchar(10),[spid] varchar(10),[shl] int)
insert [a]
select 'bh001' ,'sp001' ,10  union all
select 'bh001' ,'sp002' ,11  union all
select 'bh001' ,'sp003' ,15  union all
select 'bh002' ,'sp001' ,10  union all
select 'bh002' ,'sp002' ,11  union all
select 'bh002' ,'sp003' ,15  union all
select 'bh003' ,'sp001' ,97  union all
select 'bh003' ,'sp003' ,98  union all
select 'bh003' ,'sp004' ,99 
select * from a
/*djbh       spid       shl
---------- ---------- -----------
bh001      sp001      10
bh001      sp002      11
bh001      sp003      15
bh002      sp001      10
bh002      sp002      11
bh002      sp003      15
bh003      sp001      97
bh003      sp003      98
bh003      sp004      99

(9 行受影响)
*/
--A：查询spid、shl及单据明细条数有相同的djbh

 --处理测试数据
select *,row_number() over(PARTITION BY djbh order by djbh,spid) as rowid into #a from a
select * from #a
/*
djbh       spid       shl         rowid
---------- ---------- ----------- --------------------
bh001      sp001      10          1
bh001      sp002      11          2
bh001      sp003      15          3
bh002      sp001      10          1
bh002      sp002      11          2
bh002      sp003      15          3
bh003      sp001      97          1
bh003      sp003      98          2
bh003      sp004      99          3

(9 行受影响)
*/
--按单据编号进行分组合并
SELECT [djbh],  
(SELECT [spid]+',' FROM #a  WHERE djbh=a.djbh
  FOR XML PATH('') 
  ) AS [spid] ,
(SELECT cast([shl] as varchar(10))+',' FROM #a  WHERE djbh=a.djbh
  FOR XML PATH('') 
  ) AS [shl] 
into #b
FROM #a a  
GROUP BY [djbh]
select * from #b
/*
djbh       spid                shl
---------- ------------------------
bh001      sp001,sp002,sp003, 10,11,15,
bh002      sp001,sp002,sp003, 10,11,15,
bh003      sp001,sp003,sp004, 97,98,99,

(3 行受影响)
*/

--查询重复数量大于2的单据号
select djbh from 
#b a,
(select spid,shl from #b group by spid,shl having count(djbh)>=2) b
where a.spid=b.spid and a.shl=b.shl
/*
djbh
----------
bh001
bh002

(2 行受影响)
*/