大概是关于季度报表的问题
有表A,简单查询结果如下
year Quarter Amount
2000 1 20.1
2000 2 20.2
2000 3 20.3
2000 4 20.4
2001 1 30.1
2001 2 30.2
2001 3 30.3
2001 4 30.4要求得出每年的季度数据如下
year Quarter1 Quarter2 Quarter3 Quarter4
2000 20.1 20.2 20.3 20.4
2001 30.1 30.2 30.3 30.4求实现的SQL或过程
小弟用了一个笨办法
SELECT B.year,SUM(B.quarter1) AS quarter1, SUM(B.quarter2) AS quarter2, SUM(B.quarter3) AS quarter3, SUM(B.quarter4) AS quarter4
FROM (SELECT year,amount AS quarter1,0 AS quarter2,0 AS quarter3,0 AS quarter4 FROM A WHERE quarter=’1’
UNION
SELECT year,0 AS quarter1,amount AS quarter2,0 AS quarter3,0 AS quarter4 FROM A WHERE quarter=’2’
SELECT year,0 AS quarter1,0 AS quarter2,amount AS quarter3,0 AS quarter4 FROM A WHERE quarter=’3’
SELECT year,0 AS quarter1,0 AS quarter3,0 AS quarter3,amount AS quarter4 FROM A WHERE quarter=’4’
) B
GROUP BY year
————————————————————————
第一问上的方法通不通?
第二问是否更巧妙的方法来实现?
谢谢!!!
有表A,简单查询结果如下
year Quarter Amount
2000 1 20.1
2000 2 20.2
2000 3 20.3
2000 4 20.4
2001 1 30.1
2001 2 30.2
2001 3 30.3
2001 4 30.4要求得出每年的季度数据如下
year Quarter1 Quarter2 Quarter3 Quarter4
2000 20.1 20.2 20.3 20.4
2001 30.1 30.2 30.3 30.4求实现的SQL或过程
小弟用了一个笨办法
SELECT B.year,SUM(B.quarter1) AS quarter1, SUM(B.quarter2) AS quarter2, SUM(B.quarter3) AS quarter3, SUM(B.quarter4) AS quarter4
FROM (SELECT year,amount AS quarter1,0 AS quarter2,0 AS quarter3,0 AS quarter4 FROM A WHERE quarter=’1’
UNION
SELECT year,0 AS quarter1,amount AS quarter2,0 AS quarter3,0 AS quarter4 FROM A WHERE quarter=’2’
SELECT year,0 AS quarter1,0 AS quarter2,amount AS quarter3,0 AS quarter4 FROM A WHERE quarter=’3’
SELECT year,0 AS quarter1,0 AS quarter3,0 AS quarter3,amount AS quarter4 FROM A WHERE quarter=’4’
) B
GROUP BY year
————————————————————————
第一问上的方法通不通?
第二问是否更巧妙的方法来实现?
谢谢!!!
2、select year,sum(Quarter1) Quarter1,sum(Quarter2) Quarter2,sum(Quarter3) Quarter3,sum(Quarter4) Quarter4 from (select year,decode(Quarter,1,Amount,0) Quarter1,decode(Quarter,2,Amount,0) Quarter2,decode(Quarter,3,Amount,0) Quarter3,decode(Quarter,4,Amount,0) Quarter4 from A) group by year
DECODE函数这么好,我却用union写一大堆,让人见笑了。
惭愧啊!