mysql 行转列

最新推荐文章于 2025-11-19 11:27:55 发布

转载最新推荐文章于 2025-11-19 11:27:55 发布 · 116 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jentary/p/11534006.html

文章标签：

#数据库

SELECT NAME ,SUM( CASE SUBSTRING(mon, 6)  WHEN '01'  THEN sa ELSE 0 END ) AS '01月',

SUM( CASE SUBSTRING(mon, 6)  WHEN '02'  THEN sa ELSE 0 END ) AS '02月',
SUM( CASE SUBSTRING(mon, 6)  WHEN '03'  THEN sa ELSE 0 END ) AS '03月',
mon FROM sale

GROUP BY mon

SELECT 
  ROUND((b.num / a.num) * 100, 2) || '%' AS tpnum,
  nvl (b.num, 0) AS tnum,
  nvl (a.num, 0) AS num,
  CAST(c.time2 AS VARCHAR (10)) AS mon 
FROM
  (SELECT 
    COUNT(t.did) num,
    to_char (t.ts, 'yyyy-MM') AS d 
  FROM
    RCV_TB t 
  WHERE to_char (t.ts, 'yyyy') = '2019' 
    AND t.UNITID = '1001' 
  GROUP BY to_char (t.ts, 'yyyy-MM')) a 
  LEFT JOIN 
    (SELECT 
      * 
    FROM
      (SELECT 
        COUNT(t.did) num,
        to_char (t.ts, 'yyyy-MM') AS d 
      FROM
        RCV_TB t 
      WHERE t.isnet = '1' 
      GROUP BY to_char (t.ts, 'yyyy-MM'))) b 
    ON a.d = b.d 
  RIGHT JOIN 
    (SELECT 
      '01' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '02' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '03' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '04' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '05' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '06' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '07' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '08' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '09' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '10' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '11' AS time2 
    FROM
      DUAL 
    UNION
    ALL 
    SELECT 
      '12' AS time2 
    FROM
      DUAL) c 
    ON SUBSTR(a.d, 6, 2) = c.time2 
ORDER BY c.time2