mysql统计及行列转行

 

小知识：

行转列 case when
原理：case when作为新表中的某一列，对原表每一行筛选字段

列转行：join
原理：查询的结果先做笛卡尔积再on再where，扩充列

mysql行转列测试
当前日期
mysql:curdate()
hive:current_date()

mysql with as 5.7版本不行 8.0可以

 

 数据源表模型

 

数据结果表模型

 

student表中数据如下：

 

行列转换在指标统计中很常见，下面给出了示例，复杂的多表查询可以先join关联转换成一张表再统计，同时也给出了count，sum的区别

 

查询语句

#方式一  结果正确
INSERT INTO res
SELECT 
	NOW() AS "统计日期",
	COUNT(CASE xingbie WHEN "男" THEN 1 ELSE NULL END) AS "男性人数",
	COUNT(CASE xingbie WHEN "女" THEN 1 ELSE NULL END) AS "女性人数",
	COUNT(*) AS "总人数"
	#count(CASE xingbie WHEN "女" THEN 1 ELSE null END) AS "总人数",
FROM 
	student;

#方式二  结果不正确
INSERT INTO res
SELECT 
	NOW() AS "统计日期",
	COUNT(CASE xingbie WHEN "男" THEN 1 ELSE 0 END) AS "男性人数",
	COUNT(CASE xingbie WHEN "女" THEN 1 ELSE 0 END) AS "女性人数",
	COUNT(*) AS "总人数"
	#count(CASE xingbie WHEN "女" THEN 1 ELSE null END) AS "总人数",
FROM 
	student;
	
#结果是666  count统计所有行数，如果指定字段，会统计所有非null


#方式三 结果正确
INSERT INTO res
SELECT 
	NOW() AS "统计日期",
	SUM(CASE xingbie WHEN "男" THEN 1 ELSE 0 END) AS "男性人数",
	SUM(CASE xingbie WHEN "女" THEN 1 ELSE 0 END) AS "女性人数",
	COUNT(*) AS "总人数"
	#count(CASE xingbie WHEN "女" THEN 1 ELSE null END) AS "总人数",
FROM 
	student;
	
SELECT 
	NOW() AS "统计日期",
	SUM(CASE xingbie WHEN "男" THEN 1 ELSE 0 END) AS "男性人数",
	SUM(CASE xingbie WHEN "女" THEN 1 ELSE 0 END) AS "女性人数",
	COUNT(*) AS "总人数",
	ROUND(COUNT(*)/2,2) AS "男女平均人数"
	
FROM 
	student;



#统计各个年级的人数及总人数并在一行显示
SELECT
	c1 AS "一年级总数",
	c2 AS "二年级总数",
	c3 AS "三年级总数",
	c1+c2+c3 AS "总人数"
FROM
(
	(
	SELECT
		COUNT(*) AS c1
	FROM student
	WHERE nianji = "一年级"
	)tmp1
JOIN
	(
	SELECT
		COUNT(*) AS c2 
	FROM student
	WHERE nianji = "二年级"
	)tmp2
JOIN
	(
	SELECT
		COUNT(*) AS c3 
	FROM student
	WHERE nianji = "三年级"
	)tmp3
)


#统计各个年级的总人数并在一行显示
SELECT
	c1 AS "一年级总数",
	c2 AS "二年级总数",
	c3 AS "三年级总数"
FROM
(
	(
	SELECT
		COUNT(*) AS c1
	FROM student
	WHERE nianji = "一年级"
	)tmp1
JOIN
	(
	SELECT
		COUNT(*) AS c2 
	FROM student
	WHERE nianji = "二年级"
	)tmp2
JOIN
	(
	SELECT
		COUNT(*) AS c3 
	FROM student
	WHERE nianji = "三年级"
	)tmp3
)









SELECT 
	COUNT(*)/COUNT(DISTINCT(xingbie)) AS "男女平均人数"
FROM student





#统计每个年级男性人数和女性人数
SELECT
	nianji AS "年级",
	SUM(CASE xingbie WHEN "男" THEN 1 ELSE 0 END) AS "该年级男性人数",
	SUM(CASE xingbie WHEN "女" THEN 1 ELSE 0 END) AS "该年级女性人数"
FROM 
	student
GROUP BY nianji