mysql行转列

最新推荐文章于 2025-03-02 21:45:49 发布

kitajima--

最新推荐文章于 2025-03-02 21:45:49 发布

阅读量472

点赞数

分类专栏： mysql 文章标签： mysql行转列

本文链接：https://blog.csdn.net/masuwen/article/details/79197930

版权

mysql 专栏收录该内容

16 篇文章

订阅专栏

典型的行转列

-- mysql 行函数

SELECT   
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '1' THEN 1 ELSE 0 END) AS 一月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '2' THEN 1 ELSE 0 END) AS 二月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '3' THEN 1 ELSE 0 END) AS 三月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '4' THEN 1 ELSE 0 END) AS 四月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '5' THEN 1 ELSE 0 END) AS 五月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '6' THEN 1 ELSE 0 END) AS 六月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '7' THEN 1 ELSE 0 END) AS 七月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '8' THEN 1 ELSE 0 END) AS 八月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '9' THEN 1 ELSE 0 END) AS 九月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '10' THEN 1 ELSE 0 END) AS 十月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '11' THEN 1 ELSE 0 END) AS 十一月份,  
SUM(CASE MONTH(jxfp_cxptcj.kprq) WHEN '12' THEN 1 ELSE 0 END) AS 十二月份  
FROM jxfp_cxptcj   
WHERE YEAR(jxfp_cxptcj.kprq)='2017';

-- mysql 列函数

SELECT
  t.myYear AS 年份,
  t.monthNo AS 月份,
  COUNT(1) AS 发票数量
FROM
  (SELECT
    MONTH(jxfp_cxptcj.kprq) AS monthNo,
    YEAR(jxfp_cxptcj.kprq) AS myYear,
    jxfp_cxptcj.id AS id
  FROM
    jxfp_cxptcj) AS t
WHERE t.myYear = '2017'
GROUP BY t.monthNo;