首页 > 数据库技术 > 详细

InterviewQuestion_SQLServer_Probl_将数据表year|month|amount查询成year|m1|m2|m3|m4的样式

时间:2017-09-11 14:56:01      阅读:1202      评论:0      收藏:0      [点我收藏+]

标签:mount   面试   case   sel   https   发生   结果   mage   www.   

题目:有个年度统计表,结构如下:

技术分享

怎么样把这个表,查询成这样一个结果:

技术分享

这是在面试过程中遇到的一个关于数据库的题,没有一点思路,不知它考查到的知识点是什么,如何下手?

网上搜了一下,有哥们儿说这是典型的“行转列问题”,好像用到“交叉表查询”,下面看看具体的代码!

文件:SQLQuery1.sql

/*
环境:Microsoft SQL Server 2012
工具: Microsoft SQL Server Management Studio
数据库: Interview
数据表: statistic
详情:
year        month        amount
1991        1            1.1
1991        2            1.2
1991        3            1.3
1991        4            1.4
1992        1            2.1
1992        2            2.2
1992        3            2.3
1992        4            2.4
(char(10))    (int)        (float)
查询成如下结果:
year    m1     m2     m3     m4
1991    1.1    1.2    1.3    1.4
1992    2.1    2.2    2.3    2.4
*/

--方法1
SELECT
  year,
  SUM  (  CASE  WHEN  month = 1  THEN  amount ELSE 0 END )  AS  m1,
  SUM  (  CASE  WHEN  month = 2  THEN  amount ELSE 0 END )  AS  m2,
  SUM  (  CASE  WHEN  month = 3  THEN  amount ELSE 0 END )  AS  m3,
  SUM  (  CASE  WHEN  month = 4  THEN  amount ELSE 0 END )  AS  m4
FROM
  statistic
GROUP  BY
  year

-->查询结果:
    year    m1     m2     m3     m4
    1991    1.1    1.2    1.3    1.4
    1992    2.1    2.2    2.3    2.4

--方法2
SELECT 
  year,
  MAX(CASE month WHEN 1 THEN amount ELSE 0 END) m1,
  MAX(CASE month WHEN 2 THEN amount ELSE 0 END) m2, 
  MAX(CASE month WHEN 3 THEN amount ELSE 0 END) m3,
  MAX(CASE month WHEN 4 THEN amount ELSE 0 END) m4
FROM statistic
GROUP BY year

-->查询结果:
    year    m1     m2     m3     m4
    1991    1.1    1.2    1.3    1.4
    1992    2.1    2.2    2.3    2.4

--方法3
SELECT year, 
(SELECT amount FROM statistic m WHERE month=1 AND m.year=statistic.year) AS m1,
(SELECT amount FROM statistic m WHERE month=2 AND m.year=statistic.year) AS m2,
(SELECT amount FROM statistic m WHERE month=3 AND m.year=statistic.year) AS m3,
(SELECT amount FROM statistic m WHERE month=4 AND m.year=statistic.year) AS m4
FROM statistic GROUP BY year

-->查询结果:
    year    m1     m2     m3     m4
    1991    1.1    1.2    1.3    1.4
    1992    2.1    2.2    2.3    2.4

运行结果:

技术分享

代码分解:

--方法1变形:将代码中的0改为1,计算SUM,看看会发生什么
SELECT
  year,
  SUM  (  CASE  WHEN  month = 1  THEN  amount ELSE 1 END )  AS  m1,
  SUM  (  CASE  WHEN  month = 2  THEN  amount ELSE 1 END )  AS  m2,
  SUM  (  CASE  WHEN  month = 3  THEN  amount ELSE 1 END )  AS  m3,
  SUM  (  CASE  WHEN  month = 4  THEN  amount ELSE 1 END )  AS  m4
FROM
  statistic
GROUP  BY
  year

-->查询结果:
    year    m1     m2     m3     m4
    1991    4.1    4.2    4.3    4.4
    1992    5.1    5.2    5.3    5.4

--方法2变形1:将MAX关键字换为MIN
SELECT 
  year,
  MIN(CASE month WHEN 1 THEN amount ELSE 0 END) m1,
  MIN(CASE month WHEN 2 THEN amount ELSE 0 END) m2, 
  MIN(CASE month WHEN 3 THEN amount ELSE 0 END) m3,
  MIN(CASE month WHEN 4 THEN amount ELSE 0 END) m4
FROM statistic
GROUP BY year

-->查询结果:
    year    m1   m2   m3   m4
    1991    0    0    0    0
    1992    0    0    0    0

--方法2变形2:将MAX关键字换为MIN,并将0换为1
SELECT 
  year,
  MIN(CASE month WHEN 1 THEN amount ELSE 1 END) m1,
  MIN(CASE month WHEN 2 THEN amount ELSE 1 END) m2, 
  MIN(CASE month WHEN 3 THEN amount ELSE 1 END) m3,
  MIN(CASE month WHEN 4 THEN amount ELSE 1 END) m4
FROM statistic
GROUP BY year

-->查询结果:
    year    m1   m2   m3   m4
    1991    1    1    1    1
    1992    1    1    1    1

--方法2变形3:将MAX关键字换为SUM,并将0换为1
SELECT 
  year,
  SUM(CASE month WHEN 1 THEN amount ELSE 1 END) m1,
  SUM(CASE month WHEN 2 THEN amount ELSE 1 END) m2, 
  SUM(CASE month WHEN 3 THEN amount ELSE 1 END) m3,
  SUM(CASE month WHEN 4 THEN amount ELSE 1 END) m4
FROM statistic
GROUP BY year

-->查询结果:
    year    m1     m2     m3     m4
    1991    4.1    4.2    4.3    4.4
    1992    5.1    5.2    5.3    5.4

参考文章:

https://zhidao.baidu.com/question/550776347.html

https://zhidao.baidu.com/question/486996750.html

https://www.zybang.com/question/0ac74f3ae058071e80274e23d5e1042a.html

 

InterviewQuestion_SQLServer_Probl_将数据表year|month|amount查询成year|m1|m2|m3|m4的样式

标签:mount   面试   case   sel   https   发生   结果   mage   www.   

原文:http://www.cnblogs.com/jswl/p/7504795.html

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号