有下表及记录
BNo ENo UseQty SubTotal
A0001 A0100 98 1500
A0101 A0300 200 6000
A0501 A0700 200 500
BB0201 BB0400 200 450
BB0401 BB0500 100 350
BB0601 BB0700 100 550
BB0701 BB0800 100 400
如何得到
A0001 A0300 398 7500
A0501 A0700 200 500
BB0201 BB0500 300 850
BB0601 BB0800 200 950
也就是连续编号合并。
BNo ENo UseQty SubTotal
A0001 A0100 98 1500
A0101 A0300 200 6000
A0501 A0700 200 500
BB0201 BB0400 200 450
BB0401 BB0500 100 350
BB0601 BB0700 100 550
BB0701 BB0800 100 400
如何得到
A0001 A0300 398 7500
A0501 A0700 200 500
BB0201 BB0500 300 850
BB0601 BB0800 200 950
也就是连续编号合并。
无论是在sql 2000,还是在 sql 2005 中,都没有提供字符串的聚合函数,
所以,当我们在处理下列要求时,会比较麻烦:
有表tb, 如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即, group by id, 求 value 的和(字符串相加)1. 旧的解决方法-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = @r + ',' + value
FROM tb
WHERE id=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id-- 2. 新的解决方法
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT
[values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO
/*==============================================*/
--3.3.2 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
GO/*==============================================*/
--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3--合并处理
SELECT col1,col2=CAST(col2 as varchar(100))
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1 col2
---------- -------------
a 1
a 1,2
b 1
b 1,2
b 1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1 col2
---------- -----------
a 1,2
b 1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO
/*==============================================*/--3.3.4.1 每组 <=2 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=1 THEN ''
ELSE ','+CAST(MAX(col2) as varchar)
END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ----------
a 1,2
b 1,2
c 3
--*/--3.3.4.2 每组 <=3 条记录的合并
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3--合并处理
SELECT col1,
col2=CAST(MIN(col2) as varchar)
+CASE
WHEN COUNT(*)=3 THEN ','
+CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
ELSE ''
END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1 col2
---------- ------------
a 1,2
b 1,2,3
c 3
--*/
GO
if not object_id('A') is null
drop table A
Go
Create table A([id] int,[cname] nvarchar(2))
Insert A
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'蔡六'
Go
--> -->
if not object_id('B') is null
drop table B
Go
Create table B([id] int,[cname] nvarchar(5))
Insert B
select 1,N'1,2,3' union all
select 2,N'3,4'
Go
create function F_str(@cname nvarchar(100))
returns nvarchar(100)
as
begin
select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return @cname
end
go
select [id],dbo.F_str([cname])[cname] from Bid cname
----------- ----------------------------------------------------------------------------------------------------
1 张三,李四,王五
2 王五,蔡六(2 個資料列受到影響)
A0001~A0100,A0101~A0300要转换为A0001~A0300
下一条记录应为A0301~....即为连续的。
同理BB0201~BB0400,BB0401~BB0500要转换为BB0201~BB0500
BB0601~BB0700,BB0701~BB0800转换为BB0601~BB0800
BNo varchar(10),
ENo varchar(10),
UseQty int,
SubTotal int
);
INSERT @tb
SELECT 'A0001', 'A0100', 98 , 1500 UNION ALL
SELECT 'A0101', 'A0300', 200 , 6000 UNION ALL
SELECT 'A0501', 'A0700', 200 , 500 UNION ALL
SELECT 'BB0201', 'BB0400', 200, 450 UNION ALL
SELECT 'BB0401', 'BB0500', 100, 350 UNION ALL
SELECT 'BB0601', 'BB0700', 100, 550 UNION ALL
SELECT 'BB0701', 'BB0800', 100, 400;
SELECT
A.Bno, A.Eno,
UseQty = SUM(B.UseQty),
SubTotal = SUM(B.SubTotal)
FROM(
SELECT
A.Bno,
Eno = (
SELECT TOP 1
Eno
FROM @tb AA
WHERE LEFT(AA.BNo, LEN(AA.BNo) - 4) = LEFT(A.ENo, LEN(A.ENo) - 4)
AND AA.Bno >= A.Bno
AND NOT EXISTS(
SELECT * FROM @tb BB
WHERE LEFT(BB.BNo, LEN(BB.BNo) - 4) = LEFT(AA.ENo, LEN(AA.ENo) - 4)
AND RIGHT(BB.Bno, 4) - 1 = RIGHT(AA.ENo, 4))
)
FROM @tb A
WHERE NOT EXISTS(
SELECT * FROM @tb B
WHERE LEFT(B.BNo, LEN(B.BNo) - 4) = LEFT(A.ENo, LEN(A.ENo) - 4)
AND RIGHT(B.ENo, 4) + 1 = RIGHT(A.Bno, 4))
)A,
@tb B
WHERE B.Bno BETWEEN A.Bno AND A.Eno
AND B.Eno BETWEEN A.Bno AND A.Eno
GROUP BY A.Bno, A.Eno/*-- 结果
Bno Eno UseQty SubTotal
---------- ---------- ----------- -----------
A0001 A0300 298 7500
A0501 A0700 200 500
BB0201 BB0500 300 800
BB0601 BB0800 200 950(4 行受影响)
--*/
INSERT @TB
SELECT 'A0001', 'A0100', 98, 1500 UNION ALL
SELECT 'A0101', 'A0300', 200, 6000 UNION ALL
SELECT 'A0501', 'A0700', 200, 500 UNION ALL
SELECT 'BB0201', 'BB0400', 200, 450 UNION ALL
SELECT 'BB0401', 'BB0500', 100, 350 UNION ALL
SELECT 'BB0601', 'BB0700', 100, 550 UNION ALL
SELECT 'BB0701', 'BB0800', 100, 400SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB
SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE LEFT(BNo,1)<LEFT(A.BNo,1)) INTO #T FROM # AS ASELECT BNo,ENo=ISNULL((SELECT TOP 1 ENo FROM #T WHERE LEFT(BNo,1)= LEFT(A.BNo,1) AND SEQ=A.SEQ+1),(SELECT TOP 1 ENo FROM #T WHERE LEFT(BNo,1)= LEFT(A.BNo,1) AND SEQ=A.SEQ)),
UseQty=(SELECT SUM(UseQty) FROM #T WHERE LEFT(BNo,1)= LEFT(A.BNo,1) AND (SEQ=A.SEQ OR SEQ=A.SEQ+1)),
SubTotal=(SELECT SUM(SubTotal) FROM #T WHERE LEFT(BNo,1)= LEFT(A.BNo,1) AND (SEQ=A.SEQ OR SEQ=A.SEQ+1))
FROM #T AS A
WHERE SEQ%2=1DROP TABLE #
DROP TABLE #T
/*
BNo ENo UseQty SubTotal
---------- ---------- ----------- -----------
A0001 A0300 298 7500
A0501 A0700 200 500
BB0201 BB0500 300 800
BB0601 BB0800 200 950
*/
INSERT @t SELECT 'A0001', 'A0100', 98 , 1500
UNION ALL SELECT 'A0101' , 'A0300', 200 , 6000
UNION ALL SELECT 'A0501' , 'A0700', 200 , 500
UNION ALL SELECT 'BB0201', 'BB0400', 200 ,450
UNION ALL SELECT 'BB0401' , 'BB0500', 100 , 350
UNION ALL SELECT 'BB0601' ,'BB0700', 100 , 550
UNION ALL SELECT 'BB0701' ,'BB0800', 100 , 400
UNION ALL SELECT 'BB0801', 'BB0809', 105 , 200 --加入一条
;WITH fc AS
(
SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @t
)
,fc1 AS
(
SELECT a.*,b.bid gid FROM fc a
CROSS APPLY
(SELECT bid,eid FROM fc x WHERE x.bid>a.eid AND x.bType = a.bType
AND NOT EXISTS
(
SELECT 1 FROM fc y WHERE y.bid>a.eid AND y.bid<=x.bid AND y.bType = a.bType
AND y.bid NOT IN
(
SELECT z.eid + 1 FROM fc z WHERE z.bTYpe=a.BType
)
)
) b
)SELECT ISNULL(STUFF(a.bno,LEN(a.bno)-LEN(bno1)+1,len(bno1),bno1),a.bno) Bno,SUM(UseQty),SUM(SubTotal) FROM @t a
LEFT JOIN
(SELECT bNo,MAX(gid) bNo1 FROM fc1 GROUP BY bno) b
ON a.bno=b.bno
GROUP BY ISNULL(STUFF(a.bno,LEN(a.bno)-LEN(bno1)+1,len(bno1),bno1),a.bno)/*
A0101 298 7500
A0501 200 500
BB0401 300 800
BB0801 305 1150
*/
/*
A0101 A0300 298 7500
A0501 A0700 200 500
BB0401 BB0700 300 800
BB0601 BB0809 305 1150
*/
而不是fcuandy 兄的:
/*
A0101 298 7500
A0501 200 500
BB0401 300 800
BB0801 305 1150
*/
结果还是有点差别.
INSERT @t SELECT 'A0001', 'A0100', 98 , 1500
UNION ALL SELECT 'A0101' , 'A0300', 200 , 6000
UNION ALL SELECT 'A0501' , 'A0700', 200 , 500
UNION ALL SELECT 'BB0201', 'BB0400', 200 ,450
UNION ALL SELECT 'BB0401' , 'BB0500', 100 , 350
UNION ALL SELECT 'BB0601' ,'BB0700', 100 , 550
UNION ALL SELECT 'BB0701' ,'BB0800', 100 , 400
UNION ALL SELECT 'BB0801', 'BB0809', 105 , 200 --加入一条我希望的结果为:
/*
A0101 A0300 298 7500
A0501 A0700 200 500
BB0201 BB0500 300 800
BB0601 BB0809 305 1150
*/
而不是fcuandy 兄的:
/*
A0101 298 7500
A0501 200 500
BB0401 300 800
BB0801 305 1150
*/
结果还是有点差别.
我写的是sql2005写法。 虽然用了cte,但是只是为了简化查询用的,稍加修改可支持2000,不过语句就很烦琐了。
INSERT @t SELECT 'A0001', 'A0100', 98 , 1500
UNION ALL SELECT 'A0101' , 'A0300', 200 , 6000
UNION ALL SELECT 'A0501' , 'A0700', 200 , 500
UNION ALL SELECT 'BB0201', 'BB0400', 200 ,450
UNION ALL SELECT 'BB0401' , 'BB0500', 100 , 350
UNION ALL SELECT 'BB0601' ,'BB0700', 100 , 550
UNION ALL SELECT 'BB0701' ,'BB0800', 100 , 400
UNION ALL SELECT 'BB0801', 'BB0809', 105 , 200 --加入一条
;WITH fc AS
(
SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @t
)
,fc1 AS
(
SELECT a.*,b.bid gid FROM fc a
CROSS APPLY
(SELECT bid,eid FROM fc x WHERE x.bid>a.eid AND x.bType = a.bType
AND NOT EXISTS
(
SELECT 1 FROM fc y WHERE y.bid>a.eid AND y.bid<=x.bid AND y.bType = a.bType
AND y.bid NOT IN
(
SELECT z.eid + 1 FROM fc z WHERE z.bTYpe=a.BType
)
)
) b
)SELECT MIN(a.Bno) bno,MAX(a.Eno)eno,SUM(UseQty) SumUseQty,SUM(SubTotal) SumSubTotal FROM @t a
LEFT JOIN
(SELECT bNo,MAX(gid) bNo1 FROM fc1 GROUP BY bno) b
ON a.bno=b.bno
GROUP BY ISNULL(STUFF(a.bno,LEN(a.bno)-LEN(bno1)+1,len(bno1),bno1),a.bno)/*
SELECT *,ISNULL(STUFF(a.bno,LEN(a.bno)-LEN(bno1)+1,len(bno1),bno1),a.bno) FROM @t a
LEFT JOIN
(SELECT bNo,MAX(gid) bNo1 FROM fc1 GROUP BY bno) b
ON a.bno=b.bno
--GROUP BY ISNULL(STUFF(a.bno,LEN(a.bno)-LEN(bno1)+1,len(bno1),bno1),a.bno)
*/
/*
A0001 A0300 298 7500
A0501 A0700 200 500
BB0201 BB0500 300 800
BB0601 BB0809 305 1150
*/
,可以不用那么子查询,不过连表条件里要加上那么多函数处理关联关系。
DECLARE @t TABLE(BNo VARCHAR(10), ENo VARCHAR(10),UseQty INT,SubTotal INT)
INSERT @t SELECT 'A0001', 'A0100', 98 , 1500
UNION ALL SELECT 'A0101' , 'A0300', 200 , 6000
UNION ALL SELECT 'A0501' , 'A0700', 200 , 500
UNION ALL SELECT 'BB0201', 'BB0400', 200 ,450
UNION ALL SELECT 'BB0401' , 'BB0500', 100 , 350
UNION ALL SELECT 'BB0601' ,'BB0700', 100 , 550
UNION ALL SELECT 'BB0701' ,'BB0800', 100 , 400
UNION ALL SELECT 'BB0801', 'BB0809', 105 , 200 --加入一条
SELECT MIN(a.bno),max(a.bno),Sum(UseQty),SUM(SubTotal) FROM
(SELECT a.bno,MAX(ISNULL(b.Bno,a.Bno)) gid FROM
(
SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @t
)
a
LEFT JOIN (
SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @t
)
b
ON a.bType= b.bType AND b.bid>a.eid
AND NOT EXISTS
(
SELECT 1 FROM
(
SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @t
) y WHERE y.bid>a.eid AND y.bid<=b.bid AND y.bType = a.bType
AND y.bid NOT IN
(
SELECT z.eid + 1 FROM
(
SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @t
) z WHERE z.bType = a.bType-- AND z.bid>a.eid AND z.bid<b.bid
)
)
GROUP BY a.bno
) a
INNER JOIN @t b
ON a.bno = b.bno
GROUP BY gid
/*
A0001 A0101 298 7500
A0501 A0501 200 500
BB0201 BB0401 300 800
BB0601 BB0801 305 1150
*/
DECLARE @t TABLE(BNo VARCHAR(10), ENo VARCHAR(10),UseQty INT,SubTotal INT)
INSERT @t SELECT 'A0001', 'A0100', 98 , 1500
UNION ALL SELECT 'A0101' , 'A0300', 200 , 6000
UNION ALL SELECT 'A0501' , 'A0700', 200 , 500
UNION ALL SELECT 'BB0201', 'BB0400', 200 ,450
UNION ALL SELECT 'BB0401' , 'BB0500', 100 , 350
UNION ALL SELECT 'BB0601' ,'BB0700', 100 , 550
UNION ALL SELECT 'BB0701' ,'BB0800', 100 , 400
UNION ALL SELECT 'BB0801', 'BB0809', 105 , 200 --加入一条DECLARE @fc TABLE(BNo VARCHAR(10), ENo VARCHAR(10),UseQty INT,SubTotal INT,bid INT,eid INT,bType VARCHAR(10))
INSERT @fc SELECT *,CAST(STUFF(bno,1,PATINDEX('%[0-9]%',bno)-1,'') AS INT) bid,CAST(STUFF(eno,1,PATINDEX('%[0-9]%',eno)-1,'') AS INT) eid
,STUFF(bno,PATINDEX('%[0-9]%',bno),100,'') btype
FROM @tSELECT MIN(a.bno),max(a.bno),Sum(UseQty),SUM(SubTotal) FROM
(SELECT a.bno,MAX(ISNULL(b.Bno,a.Bno)) gid FROM
@fc a
LEFT JOIN
@fc b
ON a.bType= b.bType AND b.bid>a.eid
AND NOT EXISTS
(
SELECT 1 FROM @fc y
WHERE y.bid>a.eid AND y.bid<=b.bid AND y.bType = a.bType
AND y.bid NOT IN
(
SELECT z.eid + 1 FROM @fc z
WHERE z.bType = a.bType
)
)
GROUP BY a.bno
) a
INNER JOIN @t b
ON a.bno = b.bno
GROUP BY gid
/*
A0001 A0101 298 7500
A0501 A0501 200 500
BB0201 BB0401 300 800
BB0601 BB0801 305 1150
*/
INSERT @t (BNo,ENo,UseQty,SubTotal)
SELECT 'A0001', 'A0100', 98 , 1500
UNION ALL SELECT 'A0101' , 'A0300', 200 , 6000
UNION ALL SELECT 'A0501' , 'A0700', 200 , 500
UNION ALL SELECT 'BB0201', 'BB0400', 200 ,450
UNION ALL SELECT 'BB0401' , 'BB0500', 100 , 350
UNION ALL SELECT 'BB0601' ,'BB0700', 100 , 550
UNION ALL SELECT 'BB0701' ,'BB0800', 100 , 400
UNION ALL SELECT 'BB0801', 'BB0809', 105 , 200 --加入一条DECLARE @grp INT
SET @grp=0UPDATE @t
SET grp=@grp,
@grp=CASE WHEN CAST(RIGHT(BNo,3) as INT)
=1+ ISNULL((SELECT TOP 1 CAST(RIGHT(ENo,3) as INT) FROM @t WHERE ENo<a.BNo ORDER BY ENo DESC),-1)
THEN @grp
ELSE @grp+1
END
FROM @t aSELECT MIN(BNo) as BNo,
MAX(ENo) as ENo,
SUM(UseQty) as UseQty,
SUM(SubTotal) as SubTotal
FROM @t
GROUP BY grp
INSERT tb
SELECT 'A0001', 'A0100', 98 , 1500 UNION ALL
SELECT 'A0101', 'A0300', 200 , 6000 UNION ALL
SELECT 'A0501', 'A0700', 200 , 500 UNION ALL
SELECT 'BB0201', 'BB0400', 200, 450 UNION ALL
SELECT 'BB0401', 'BB0500', 100, 350 UNION ALL
SELECT 'BB0601', 'BB0700', 100, 550 UNION ALL
SELECT 'BB0701', 'BB0800', 100, 400;
go
alter table tb add flg varchar(10)
go
update tb set flg=(
select top 1 eno from tb a where eno>b.bno and
not exists (select 1 from tb where left(bno,len(bno)-4)=left(a.eno,len(a.eno)-4) and convert(int,right(bno,4))=convert(int,right(a.eno,4))+1)
)from tb b
select min(bno) as bno,flg as eno,sum(useqty) as userqty,sum(subtotal) as subtotal from tb group by flg
go
drop table tb
/*
bno eno userqty subtotal
---------- ---------- ----------- -----------
A0001 A0300 298 7500
A0501 A0700 200 500
BB0201 BB0500 300 800
BB0601 BB0800 200 950(4 行受影响)*/
if object_id('tc') is not null
drop table tc
go
create table tc(BNo varchar(10),ENo varchar(10),UseQty int,SubTotal int);
INSERT tc
SELECT 'A0001', 'A0100', 98 , 1500 UNION ALL
SELECT 'A0101', 'A0300', 200 , 6000 UNION ALL
SELECT 'A0501', 'A0700', 200 , 500 UNION ALL
SELECT 'BB0201', 'BB0400', 200, 450 UNION ALL
SELECT 'BB0401', 'BB0500', 100, 350 UNION ALL
SELECT 'BB0601', 'BB0700', 100, 550 UNION ALL
SELECT 'BB0701', 'BB0800', 100, 400;
godeclare @a varchar(100),@b varchar(100),@c varchar(100)
set @a='A0001'
set @b='A0001'
set @c='A0100'
update tc set @a=BNo,
@b=case when cast(right(BNo,4) as int)=cast(right(@c,4) as int)+1 then @b else @a end,
@c=ENo,
BNo=@bselect BNo,max(ENo) as ENo,sum(UseQty) as UseQty,sum(SubTotal) as SubTotal
from tc
group by BNo/*
BNo ENo UseQty SubTotal
---------- ---------- ----------- -----------
A0001 A0300 298 7500
A0501 A0700 200 500
BB0201 BB0500 300 800
BB0601 BB0800 200 950(4 行受影响)
*/
因為字段中的數字長度不固定,這些語句可能會有問題。
BNo ENo UseQty SubTotal
A0001 A0100 98 1500
A0101 A0300 200 6000
A0501 A0700 200 500
BB0201 BB0400 200 450
BB0401 BB0500 100 350
BB0601 BB0700 100 550
BB0701 BB0800 100 400
如何得到
A0001 A0300 398 7500
A0501 A0700 200 500
BB0201 BB0500 300 850
BB0601 BB0800 200 950
也就是连续编号合并。?
怎么合并的沒有看出來?
go
create table [tb]([bno] varchar(10),[eno] varchar(10),[useqty] int,[subtotal] int)
insert [tb]
select 'a0001','a0100',98,1500 union all
select 'a0101','a0300',200,6000 union all
select 'a0501','a0700',200,500 union all
select 'bb0201','bb0400',200,450 union all
select 'bb0401','bb0500',100,350 union all
select 'bb0601','bb0700',100,550 union all
select 'bb0701','bb0800',100,400select * from [tb]
--规整信息,插入临时表
select *
,aid=left(bno,patindex('%[0-9]%',bno)-1)
,bid=cast(stuff(bno,1,patindex('%[0-9]%',bno)-1,'') as int)
,eid=cast(stuff(eno,1,patindex('%[0-9]%',eno)-1,'') as int)
,id=identity(int,1,1)
,grp=0
into #
from tb
order by aid,bid
--select * from #
/*
bno eno useqty subtotal aid bid eid id grp
---------- ---------- ----------- ----------- ---------- ----------- ----------- ----------- -----------
a0001 a0100 98 1500 a 1 100 1 0
a0101 a0300 200 6000 a 101 300 2 0
a0501 a0700 200 500 a 501 700 3 0
bb0201 bb0400 200 450 bb 201 400 4 0
bb0401 bb0500 100 350 bb 401 500 5 0
bb0601 bb0700 100 550 bb 601 700 6 0
bb0701 bb0800 100 400 bb 701 800 7 0
*/
--更新临时表以取得分组号
declare @grp int
set @grp=0
update t
set grp=@grp,@grp=case when bid=(select eid+1 from # where id=t.id-1) then @grp else @grp+1 end
from # t
--分组聚合
select bno=min(bno),eno=max(eno),useqty=sum(useqty),subtotal=sum(subtotal)
from #
group by grp
/*
bno eno useqty subtotal
---------- ---------- ----------- -----------
a0001 a0300 298 7500
a0501 a0700 200 500
bb0201 bb0500 300 800
bb0601 bb0800 200 950(4 行受影响)
*/
drop table #