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

案例背景：按周统计周中每天销售额。

要求结果如下图所示：

---

【1】子查询

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;

抛开表分区不谈，这种方式效率很低。结果如下图所示，耗时167.180S：

---

【2】IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

实例如下：

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

效率显然比第一种方式(子查询)要高，结果如下(耗时0.119S)：

---

【3】CASE…WEHN…THEN…ELSE…END

实例如下 :

SELECT
	week_year,
	SUM(
		(
			CASE gs.day_week
			WHEN 1 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Sunday,
	SUM(
		(
			CASE gs.day_week
			WHEN 2 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Monday,
	SUM(
		(
			CASE gs.day_week
			WHEN 3 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Tuesday,
	SUM(
		(
			CASE gs.day_week
			WHEN 4 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Wednesday,
	SUM(
		(
			CASE gs.day_week
			WHEN 5 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Thursday,
	SUM(
		(
			CASE gs.day_week
			WHEN 6 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Friday,
	SUM(
		(
			CASE gs.day_week
			WHEN 7 THEN
				gs.net_price
			ELSE
				0
			END
		)
	) Saturday,
	SUM(gs.net_price)
FROM
	goods_sale gs
WHERE
	year_num = 2016
GROUP BY
	week_year

效率比子查询显然快。结果如下，耗时0.101S：

综上，MySQL行转列时，要综合考虑运用函数提高效率！