有两个表tabel1,table2tabel1的字段为qssj(起始日期) zzsj(终止日期) bm(商品编码) cksl(出库数量)
2011-10-1 2011-10-16 001
2011-10-2 2011-10-17 002
tabel2的字段为sj(日期) bm(商品编码) cksl(出库数量)
2011-10-2 001 12
2011-10-3 001 11
2011-10-5 002 20
2011-10-6 002 10
2011-10-22 001 18
我希望用sql实现以下效果
table2按日期区间与bm对cksl进行汇总然后刷新table1的cksl
tabel1的字段内容为qssj(起始日期) zzsj(终止日期) bm(商品编码) cksl(出库数量)
2011-10-1 2011-10-16 001 23
2011-10-2 2011-10-17 002 302011-10-22 001 18 这条记录没汇总进来原来就是
2011-10-22不在2011-10-1 与 2011-10-16 之间
2011-10-1 2011-10-16 001
2011-10-2 2011-10-17 002
tabel2的字段为sj(日期) bm(商品编码) cksl(出库数量)
2011-10-2 001 12
2011-10-3 001 11
2011-10-5 002 20
2011-10-6 002 10
2011-10-22 001 18
我希望用sql实现以下效果
table2按日期区间与bm对cksl进行汇总然后刷新table1的cksl
tabel1的字段内容为qssj(起始日期) zzsj(终止日期) bm(商品编码) cksl(出库数量)
2011-10-1 2011-10-16 001 23
2011-10-2 2011-10-17 002 302011-10-22 001 18 这条记录没汇总进来原来就是
2011-10-22不在2011-10-1 与 2011-10-16 之间
--演示环境
USE ZHH
GO
CREATE TABLE Tabel1(
qssj datetime,
zzsj datetime,
bm VARCHAR(20),
cksl int null
)
create table Tabel2(
sj datetime,
bm VARCHAR(20),
cksl int
)
INSERT INTO Tabel1(qssj,zzsj,bm)
SELECT '2011-10-1','2011-10-16','001'
UNION ALL
SELECT '2011-10-2','2011-10-17','002'INSERT INTO Tabel2(sj,bm,cksl)
SELECT '2011-10-2','001',12
UNION ALL
SELECT '2011-10-3','001',11
UNION ALL
SELECT '2011-10-5','002',20
UNION ALL
SELECT '2011-10-6','002',10
UNION ALL
SELECT '2011-10-22','001',18--查询
SELECT * FROM Tabel1
SELECT * FROM Tabel2---更新
;WITH
DD AS(
SELECT distinct B.*
FROM Tabel1 A ,Tabel2 B
WHERE B.sj BETWEEN A.qssj AND A.zzsj
)
UPDATE Tabel1 SET Tabel1.cksl=TB.CKSL
FROM ( SELECT bm,SUM(cksl) CKSL
FROM DD
GROUP BY bm
)TB
WHERE Tabel1.bm=TB.bm--查询SELECT * FROM Tabel1
SELECT * FROM Tabel2
/*结果:
qssj zzsj bm cksl
----------------------- ----------------------- -------------------- -----------
2011-10-01 00:00:00.000 2011-10-16 00:00:00.000 001 23
2011-10-02 00:00:00.000 2011-10-17 00:00:00.000 002 30(2 行受影响)sj bm cksl
----------------------- -------------------- -----------
2011-10-02 00:00:00.000 001 12
2011-10-03 00:00:00.000 001 11
2011-10-05 00:00:00.000 002 20
2011-10-06 00:00:00.000 002 10
2011-10-22 00:00:00.000 001 18(5 行受影响)
*/
--删除演示环境
DROP TABLE Tabel1,Tabel2
---CTE用不了则,方法效率不高,也没认真去想其他,记录数不多将就用吧:
UPDATE Tabel1 SET Tabel1.cksl=TB.CKSL
FROM ( SELECT dd.bm,SUM(cksl) CKSL
FROM
(
SELECT distinct B.*
FROM Tabel1 A ,Tabel2 B
WHERE B.sj BETWEEN A.qssj AND A.zzsj
) dd
GROUP BY dd.bm
)TB
WHERE Tabel1.bm=TB.bm
(
qssj VARCHAR(10) ,
zzsj VARCHAR(10) ,
bm VARCHAR(10) ,
cks INT
) ;
CREATE TABLE table2
(
sj VARCHAR(10) ,
bm VARCHAR(10) ,
cksl INT
) ;INSERT INTO dbo.table1 ( qssj, zzsj, bm )
SELECT '2011-10-1', '2011-10-16', '001' UNION ALL
SELECT'2011-10-2', '2011-10-17', '002';INSERT INTO dbo.table2
SELECT '2011-10-2', '001', 12 UNION ALL
SELECT '2011-10-3', '001', 11 UNION ALL
SELECT '2011-10-5', '002', 20 UNION ALL
SELECT '2011-10-6', '002', 10 UNION ALL
SELECT '2011-10-22', '001', 18;UPDATE t
SET t.cks = a.cksl
FROM table1 t ,
( SELECT a.qssj ,
a.zzsj ,
a.bm ,
SUM(b.cksl) AS cksl
FROM dbo.table1 a ,
dbo.table2 b
WHERE a.bm = b.bm
AND CONVERT(DATETIME, b.sj) BETWEEN CONVERT(DATETIME, a.qssj)
AND CONVERT(DATETIME, a.zzsj)
GROUP BY a.qssj ,
a.zzsj ,
a.bm
) a
WHERE T.qssj = a.qssj
AND T.zzsj = a.zzsj
AND T.bm = a.bm ;
SELECT * FROM dbo.table1;
DROP TABLE dbo.table1,dbo.table2;
/*
qssj zzsj bm cks
---------- ---------- ---------- -----------
2011-10-1 2011-10-16 001 23
2011-10-2 2011-10-17 002 30(2 行受影响)
*/更新前做好备份!
update tabel1 set tabel1.cksl=v.cksl from (select tb2.bm,sum(tb2.cksl) as cksl from tabel1,tabel2 tb2 where tb2.sj between tabel1.qssj and tabel1.zzsjand tabel1.bm=tb2.bm group by tb2.bm) v where tabel1.bm=v.bm--查看数据----
select * from tabel1 -- 运行结果--
--2011-10-01 00:00:00.000 2011-10-16 00:00:00.000 001 23
--2011-10-02 00:00:00.000 2011-10-17 00:00:00.000 002 30