有两个表
表A RECNO CID YDATE MONEYA YDESC
1 1020 201101 122.09
2 1020 201103 111.08
3 1020 201105 64.00
4 1022 201105 33.00
5 1022 201107 22.00
6 1022 201109 11.00
表 B
RECNO CID YDATE MONEYB
1 1020 201101 66.00
2 1020 201102 55.00
3 1020 201103 33.00
4 1020 201104 345.00
5 1020 201105 54.23
6 1022 201105 11.06
7 1022 201106 12.01
8 1022 201107 13.21
9 1022 201108 12.56
10 1022 201109 16想得到的结果:
RECNO CID YDATE MONEYA MONEYB YDESC
1 1020 201101 122.09 66 201101
2 1020 201103 111.08 88.00 201102,201103
3 1020 201105 64.00 399.23 201104,201005
4 1022 201105 33.00 11.06 201105
5 1022 201107 22.00 25.22 201106,201107
6 1022 201109 11.00 28.56 201108,201109
MONEYB的规则是有同月份的直接连接,并把MONEYB 的月份计入 YDESC
如果没有放到下个月,(有可能连续在表A中都没该月份的记录),把几个月的MONEYB 累计,并把几个月的日期写入YDESC
表A RECNO CID YDATE MONEYA YDESC
1 1020 201101 122.09
2 1020 201103 111.08
3 1020 201105 64.00
4 1022 201105 33.00
5 1022 201107 22.00
6 1022 201109 11.00
表 B
RECNO CID YDATE MONEYB
1 1020 201101 66.00
2 1020 201102 55.00
3 1020 201103 33.00
4 1020 201104 345.00
5 1020 201105 54.23
6 1022 201105 11.06
7 1022 201106 12.01
8 1022 201107 13.21
9 1022 201108 12.56
10 1022 201109 16想得到的结果:
RECNO CID YDATE MONEYA MONEYB YDESC
1 1020 201101 122.09 66 201101
2 1020 201103 111.08 88.00 201102,201103
3 1020 201105 64.00 399.23 201104,201005
4 1022 201105 33.00 11.06 201105
5 1022 201107 22.00 25.22 201106,201107
6 1022 201109 11.00 28.56 201108,201109
MONEYB的规则是有同月份的直接连接,并把MONEYB 的月份计入 YDESC
如果没有放到下个月,(有可能连续在表A中都没该月份的记录),把几个月的MONEYB 累计,并把几个月的日期写入YDESC
--*******************************************************************************************
表结构,数据如下:
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. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb /*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc (2 行受影响)
*/ --SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id /*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb
if not object_id('Tempdb..#A') is null
drop table #A
Go
Create table #A([RECNO] int,[CID] int,[YDATE] nvarchar(6),[MONEYA] decimal(18,2),YDESC nvarchar(200))
Insert #A([RECNO],[CID],[YDATE],[MONEYA])
select 1,1020,'201101',122.09 union all
select 2,1020,'201103',111.08 union all
select 3,1020,'201105',64.00 union all
select 4,1022,'201105',33.00 union all
select 5,1022,'201107',22.00 union all
select 6,1022,'201109',11.00
Go
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#B') is null
drop table #B
Go
Create table #B([RECNO] int,[CID] int,[YDATE] nvarchar(6),[MONEYB] decimal(18,2))
Insert #B
select 1,1020,'201101',66.00 union all
select 2,1020,'201102',55.00 union all
select 3,1020,'201103',33.00 union all
select 4,1020,'201104',345.00 union all
select 5,1020,'201105',54.23 union all
select 6,1022,'201105',11.06 union all
select 7,1022,'201106',12.01 union all
select 8,1022,'201107',13.21 union all
select 9,1022,'201108',12.56 union all
select 10,1022,'201109',16
Go
;with b
as
(
Select
a.[RECNO],
a.[CID],
a.[YDATE],
a.[MONEYA],
b.[MONEYB],
b.[YDATE] as [YDATEB]
from #A as a
left join #B as b on a.[CID]=b.[CID] and b.YDATE<=a.YDATE and b.YDATE>(select isnull(max(YDATE),'') from #A where CID=A.CID and YDATE<a.YDATE)
)
select
a.[RECNO],
a.[CID],
a.[YDATE],
a.[MONEYA],
[MONEYB]=SUM(a.[MONEYB]),
[YDESC]=stuff((select ','+[YDATEB] from b where [RECNO]=a.[RECNO] for xml path('')),1,1,'')
from b as a
group by a.[RECNO],a.[CID],a.[YDATE],a.[MONEYA]
/*
RECNO CID YDATE MONEYA MONEYB YDESC
1 1020 201101 122.09 66.00 201101
2 1020 201103 111.08 88.00 201102,201103
3 1020 201105 64.00 399.23 201104,201105
4 1022 201105 33.00 11.06 201105
5 1022 201107 22.00 25.22 201106,201107
6 1022 201109 11.00 28.56 201108,201109
*/
其实,很大的问题在,这个还不是全部合并。
他的一个规则很痛苦,例如日期是A表有201101,201103 两个月的数据,B表中有201101,201102,201103 三个月的数据。
A表 201101 的数据合并 B 表的 201101 数据。
A 表 201103 的数据 合并 B 表的 201102,201103 的数据。
难点在这个规则,想不到好的方法。
用游标,先取A表中CID 的数据,从最小的月份是201101,去B表找是否有小于等于201101的数据,有则合并
,合并后把B表中符合的数据删掉。游标取A表的下一个月份201103,去B表找是否有小于等于201103的数据,有则合并。
这种方法在数据量大一点,效率相当的低。
清论坛的各位老大看看有没有什么好的方法
b.YDATE>(select isnull(max(YDATE),'') from #A where CID=A.CID and YDATE<a.YDATE)
老大能讲讲写这条语句想法,看到了才知道,要自己写,还真不行。