MySQL使用控制语句实现行转列的几个实践

SELECT
  week_year,
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 2
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Monday',
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 3
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Tuesday',
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 4
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Wednesday',
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 5
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Thursday',
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 6
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Friday',
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 7
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Saturday',
  (
    SELECT
      SUM(net_price)
    FROM
      goods_sale
    WHERE
      day_week = 1
    AND week_year = gs.week_year
    AND year_num = 2016
  ) AS 'Sunday'
FROM
  goods_sale gs
WHERE
  1 = 1
AND year_num = 2016
GROUP BY
  gs.week_year;

SELECT
  gs.week_year,
  sum(if(gs.day_week=1,gs.net_price,0)) AS 'Sunday',
  sum(if(gs.day_week=2,gs.net_price,0)) AS 'Monday',
  sum(if(gs.day_week=3,gs.net_price,0)) AS 'Tuesday',
  sum(if(gs.day_week=4,gs.net_price,0)) AS 'Wednesday',
  sum(if(gs.day_week=5,gs.net_price,0)) AS 'Thursday',
  sum(if(gs.day_week=6,gs.net_price,0)) AS 'Friday',
  sum(if(gs.day_week=7,gs.net_price,0)) AS 'Saturday',
  sum(gs.net_price)
FROM
  goods_sale gs
WHERE
  1 = 1
AND gs.year_num = 2016
GROUP BY
  gs.week_year