--> 测试数据:[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
SQL Server 2005 行号、合并、分组
最新推荐文章于 2022-11-22 18:17:27 发布
本文演示了如何在SQL Server 2005中使用`row_number()`和`FOR XML PATH`来处理数据。首先创建了一个测试表[a],接着通过`row_number() over(PARTITION BY)`生成行号,然后通过XML PATH方法将相同djbh的spid和shl字段进行合并,最后展示了如何查询重复数量大于2的单据号。
摘要由CSDN通过智能技术生成