有表如下:symbol year quarter money
a 2006 1 522
a 2006 2 420
a 2006 3 845
a 2006 0 2500
b 2005 1 656
............................
quarter表示季度,0值表示4季度之和,
要根据前3季度和一年之和生成包含第四季度的View如下:symbol year quarter money
a 2006 1 522
a 2006 2 420
a 2006 3 845
a 2006 4 713
a 2006 0 2500
b 2005 1 656
............................这个语句怎么些啊?
a 2006 1 522
a 2006 2 420
a 2006 3 845
a 2006 0 2500
b 2005 1 656
............................
quarter表示季度,0值表示4季度之和,
要根据前3季度和一年之和生成包含第四季度的View如下:symbol year quarter money
a 2006 1 522
a 2006 2 420
a 2006 3 845
a 2006 4 713
a 2006 0 2500
b 2005 1 656
............................这个语句怎么些啊?
symbol VARCHAR(8),
year VARCHAR(8),
quarter INT,
money NUMERIC(12,0)
)INSERT INTO #T
SELECT 'A', '2006', '1', '522'
UNION SELECT 'A', '2006', '2', '420'
UNION SELECT 'A', '2006', '3', '845'
UNION SELECT 'A', '2006', '0', '2500'
UNION SELECT 'A', '2005', '2', '500'
UNION SELECT 'A', '2005', '3', '698'
UNION SELECT 'A', '2005', '0', '2500'
UNION SELECT 'A', '2005', '1', '490'
UNION SELECT 'B', '2005', '1', '726'
UNION SELECT 'B', '2005', '2', '648'
UNION SELECT 'B', '2005', '3', '580'
UNION SELECT 'B', '2005', '0', '2600'-- 用这个语句来写视图就可以满足要求了
SELECT * FROM #T
UNION ALL
SELECT B.symbol, B.year, '4' quarter, A.money-B.sub_total FROM #T A LEFT JOIN (
SELECT symbol, year, SUM(money) sub_total
FROM #T
WHERE quarter!=0
GROUP BY symbol, year
) B ON A.year=B.year AND A.symbol=B.symbol
WHERE A.quarter=0-- 这个语句只是为了更方便的查看结果
SELECT * FROM (
SELECT * FROM #T
UNION ALL
SELECT B.symbol, B.year, '4' quarter, A.money-B.sub_total FROM #T A LEFT JOIN (
SELECT symbol, year, SUM(money) sub_total
FROM #T
WHERE quarter!=0
GROUP BY symbol, year
) B ON A.year=B.year AND A.symbol=B.symbol
WHERE A.quarter=0
) C
ORDER BY symbol, year, quarter
DROP TABLE #T
(select symbol,year, money from 表 where quarter = 0) m,
(select symbol,year, sum(money) as money from 表 where quarter <> 0 group by symbol,year) n
where m.symbol = n.symbol and m.year = n.year
union all
select * from 表