求sql语句写法
现在有a表，数据为
qty year month
15 2013 1
b表，数据为
qty year month
-3 2012 12
9 2012 11
1 2012 10
现在想得到这样的数据
18 2012 12
9 2012 11
8 2012 10
8 2012 9
。。。
8 2012 1
即用 15+3,15+3-9。。这样，请教，谢谢
[解决办法]
USE ProductWarehouseSecurity
GO
-->生成表a
if object_id('a') is not null
drop table a
Go
Create table a([qty] smallint,[year] smallint,[month] smallint)
Insert into a
Select 15,2013,1
-->生成表b
if object_id('b') is not null
drop table b
Go
Create table b([qty] smallint,[year] smallint,[month] smallint)
Insert into b
Select -3,2012,12
Union all Select 9,2012,11
Union all Select 1,2012,10
/*
现在有a表，数据为
qty year month
15 2013 1
b表，数据为
qty year month
-3 2012 12
9 2012 11
1 2012 10
现在想得到这样的数据
18 2012 12
9 2012 11
8 2012 10
8 2012 9
。。。
8 2012 1
*/
Go
----------------- 1.
;WITH t AS(
SELECT
b.year
,b.month
,a.qty-b.qty AS qty
FROM
(SELECT qty FROM a WHERE month=1 AND year=2013) AS a
,b
WHERE NOT EXISTS(SELECT 1 FROM b AS x
WHERE x.month>b.month
AND x.year=2012
)
UNION ALL
SELECT
b.year
,b.month
,t.qty-b.qty
FROM
b
,t
WHERE b.year=t.year
AND b.month=t.month-1
)
,t2 AS (
SELECT
ISNULL(t.qty,q.qty) AS qty
,b.year
,a.number AS month
FROM master.dbo.spt_values AS a
INNER JOIN (SELECT year,MAX(month) AS month FROM t GROUP BY year) AS b ON a.number<=b.month
LEFT JOIN t ON b.year=t.year AND a.number=t.month
INNER JOIN (SELECT year,qty FROM t WHERE NOT EXISTS(SELECT 1 FROM t AS x WHERE x.year=t.year AND x.month<t.month)) AS q ON b.year=q.year
WHERE a.number>0
AND a.type='P'
)SELECT * FROM t2
ORDER BY year DESC,month DESC
/*
qty year month
------ ------ -----------
18 2012 12
9 2012 11
8 2012 10
8 2012 9
8 2012 8
8 2012 7
8 2012 6
8 2012 5
8 2012 4
8 2012 3
8 2012 2
8 2012 1
*/
----------------- 2.
IF object_id('tempdb..#')IS NOT NULL
DROP TABLE #
;WITH t AS(
SELECT
b.year
,b.month
,a.qty-b.qty AS qty
FROM
(SELECT qty FROM a WHERE month=1 AND year=2013) AS a
,b
WHERE NOT EXISTS(SELECT 1 FROM b AS x
WHERE x.month>b.month
AND x.year=2012
)
UNION ALL
SELECT
b.year
,b.month
,t.qty-b.qty
FROM
b
,t
WHERE b.year=t.year
AND b.month=t.month-1
)
SELECT * INTO # FROM t
SELECT
ISNULL(t.qty,q.qty) AS qty
,b.year
,a.number AS month
FROM master.dbo.spt_values AS a
INNER JOIN (SELECT year,MAX(month) AS month FROM # GROUP BY year) AS b ON a.number<=b.month
LEFT JOIN # AS t ON b.year=t.year AND a.number=t.month
INNER JOIN (SELECT year,qty FROM # AS o WHERE NOT EXISTS(SELECT 1 FROM # AS x WHERE x.year=o.year AND x.month<o.month)) AS q ON b.year=q.year
WHERE a.number>0
AND a.type='P'
ORDER BY year DESC,month DESC
/*
qty year month
------ ------ -----------
18 2012 12
9 2012 11
8 2012 10
8 2012 9
8 2012 8
8 2012 7
8 2012 6
8 2012 5
8 2012 4
8 2012 3
8 2012 2
8 2012 1
*/
