SQL 列变行

之前用到过，但是已经记不起来了，等用到的时候再研究吧。

SELECT
	a.*,
	b.a,
	b.b,
	b.c,
	b.d 
FROM
	`user` a
	LEFT JOIN (
SELECT
	user_id,
	MAX( CASE ziduan WHEN 'a' THEN shuzhi ELSE NULL END ) a,
	MAX( CASE ziduan WHEN 'b' THEN shuzhi ELSE NULL END ) b,
	MAX( CASE ziduan WHEN 'c' THEN shuzhi ELSE NULL END ) c,
	MAX( CASE ziduan WHEN 'd' THEN shuzhi ELSE NULL END ) d 
FROM
	user_ext 
GROUP BY
	user_id 
	) b ON a.id = b.user_id

SELECT
	errorNum.entity_name,
	SUM(errorNum.err_count) AS err_count,
	MAX( CASE errorNum.rule_type WHEN 'Empty' THEN errorNum.err_count ELSE 0 END ) 'empty',
	MAX( CASE errorNum.rule_type WHEN 'Range' THEN errorNum.err_count ELSE 0 END ) 'range',
	MAX( CASE errorNum.rule_type WHEN 'Repeat' THEN errorNum.err_count ELSE 0 END ) 'repeat',
	MAX( CASE errorNum.rule_type WHEN 'Standard' THEN errorNum.err_count ELSE 0 END ) 'standard'
FROM
	(
SELECT
	c.id,
	SUM( c.err_count ) err_count,
	c.rule_type,
	(
SELECT
	e.source 
FROM
	qc_qa_rules r
	LEFT JOIN qc_model_entity e ON r.entity_id = e.id 
WHERE
	r.id = c.rule_id 
	) AS entity_name 
FROM
	`qc_rule_check_logs` c 
WHERE
	project_id = "aa694d152e41401e8ad92e67734d971c"
	AND c.data_period = "20180327"
	AND c.dict_id = "sql"
GROUP BY
	entity_name,
	c.rule_type 
	) errorNum 
GROUP BY
errorNum.entity_name