Sql工作小记(2)

行列转换

---

1.行转列

在日常开发场景中，经常有需要将数据库中的行内容转化成列的情况，例如数据求和(分列统计每个学生的分数总和等)，此时，有两种方式实现业务：

• cross join 方法

  SELECT * FROM 
  (SELECT SUM(score) AS q1Score FROM es_answer WHERE question_id = 1) a CROSS JOIN
  (SELECT SUM(score) AS q2Score FROM es_answer WHERE question_id = 1) b
  

• case when语句

  SELECT SUM(CASE WHEN question_id = 1 THEN score END) AS q1Score ,
  SUM(CASE WHEN question_id = 2 THEN score END) AS q2Score FROM es_answer
  

2.列转行

当遇到属性拆分(多个属性存储在一列，用符号分隔开，或者多个属性单独成列)时，通常需要列转行的操作，以下记录具体实现方法：

备注：此处常用到序列表作为辅助工具，序列表指的是仅有自增的唯一标识(id)的数据库表，创建语句如下:
CREATE TABLE tb_squence(id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id));
INSERT INTO tb_squence VALUES(),(),(),(),(),(),(),();//插入几行数据根据需要而定

场景：多个属性存储在一列，用指定符号(,或、等，具体情况依数据而定)分隔开

a.步骤拆分:

1. 在属性字符串尾部加入指定符号(,或、等)，确保每个属性后以指定符号结尾
2. 计算出各行元素的属性长度
3. 每行元素属性长度是多少，就复制多少次该行元素
4. 根据属性进行字符串截取

b.实例代码:

    SELECT uname,REPLACE(SUBSTRING(SUBSTRING_INDEX(mobile,',',a.id), CHAR_LENGTH(SUBSTRING_INDEX(mobile,',',a.id-1))+1),',','') AS mobile 
    FROM tb_sequence AS a
    CROSS JOIN 
    (SELECT username, CONCAT(mobile,',') AS mobile, (LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1) AS size 
    FROM user1) AS b 
    ON a.id <= b.size;