java笔试题系列一：MySQL行转列 （列值转成列名）

需求：

描述：用户表，统一不同地区的客户总数，以及客户星级分布情况。

1.客户信息表如下：

写一段sql，统计数据要求如下

1.准备mysql数据

CREATE TABLE `t_user_info` (
  `F_ID` int(10) NOT NULL,
  `F_NAME` varchar(100) DEFAULT NULL COMMENT '客户名称',
  `F_STAR` varchar(10) DEFAULT NULL COMMENT '星级',
  `F_CITY` varchar(100) DEFAULT NULL COMMENT '城市',
  PRIMARY KEY (`F_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_user_info`(`F_ID`, `F_NAME`, `F_STAR`, `F_CITY`) VALUES (1, 'A', '1星', '上海');
INSERT INTO `t_user_info`(`F_ID`, `F_NAME`, `F_STAR`, `F_CITY`) VALUES (2, 'B', '2星', '上海');
INSERT INTO `t_user_info`(`F_ID`, `F_NAME`, `F_STAR`, `F_CITY`) VALUES (3, 'C', '1星', '北京');
INSERT INTO `t_user_info`(`F_ID`, `F_NAME`, `F_STAR`, `F_CITY`) VALUES (4, 'D', '2星', '广州');

分析题目

统计出每个地点的客户数量，以及每个星级的数量。客户星级为客户中的星级列的数据，要求统计结果为，将星级列转成的行。

固定星级的解决方法

mysql 函数学习

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

函数描述：
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如 果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

SELECT
	F_CITY '地区',
	count( 1 ) '客户数量',
	sum( CASE F_STAR WHEN '1星' THEN 1 ELSE 0 END) '1星数量',
    sum( CASE F_STAR WHEN '2星' THEN 1 ELSE 0 END) '2星数量' 
		FROM
			t_user_info 
	GROUP BY
	F_CITY;

自定义星级的解决办法

使用sql查出自定义星级的字段，用户拼接查询后显示结果字段

SELECT DISTINCT
	GROUP_CONCAT(
			DISTINCT CONCAT( 
			 "sum( case F_STAR when '",
			  F_STAR, 
			  "' then 1 else 0 end) '", 
			  F_STAR, 
			  "数量'"
			 )
		 )
FROM
	t_user_info 

执行结果如下图：
将查询sql拼接

SET @sql = NULL;
SELECT DISTINCT
	GROUP_CONCAT(
	  DISTINCT CONCAT( 
	 "sum( case F_STAR when '", F_STAR, 
	  "' then 1 else 0 end) '", F_STAR, "数量'"
	 )) INTO @sql
FROM
	t_user_info ;
 
SET @sql = CONCAT('select F_CITY "地区", count(1) "客户数量", ',
					         @sql, 
                  ' FROM t_user_info  GROUP BY F_CITY');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

执行sql结果：

添加自定星级sql

INSERT `t_user_info`(`F_ID`, `F_NAME`, `F_STAR`, `F_CITY`) VALUES (5, 'E', '3星', '三亚');

执行查询自定义星级的sql，获取结果为下图：

tony老师认证：此题目已攻克。
---- 理发界的扫地僧，编程界的泥石流