現mysql中有一張表php_user表,表結構為: 表中數據有: 現在想查詢出來不同學生的語數外成績在一行顯示,那麼需要用到行轉列的用法, 一、行轉列 1、使用case...when....then 進行行轉列MAX(case when 條件 then 列內容 else 不匹配時顯示內容 end) ...
現mysql中有一張表php_user表,表結構為:
表中數據有:
現在想查詢出來不同學生的語數外成績在一行顯示,那麼需要用到行轉列的用法,
一、行轉列
1、使用case...when....then 進行行轉列
MAX(case when 條件 then 列內容 else 不匹配時顯示內容 end) 列名。具體sql如下:
2、使用IF() 進行行轉列:
3、利用SUM(IF()) 生成列,直接生成結果不再利用子查詢
二、列轉行
建表語句:
CREATE TABLE tb_score1( id INT(11) NOT NULL auto_increment, userid VARCHAR(20) NOT NULL COMMENT '用戶id', cn_score DOUBLE COMMENT '語文成績', math_score DOUBLE COMMENT '數學成績', en_score DOUBLE COMMENT '英語成績', po_score DOUBLE COMMENT '政治成績', PRIMARY KEY(id) )ENGINE = INNODB DEFAULT CHARSET = utf8;
插入數據:
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0); INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0); INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);
查詢數據表中的內容(即轉換前的結果)
SELECT * FROM tb_score1
轉換後:
本質是將userid的每個科目分數分散成一條記錄顯示出來。
直接上SQL:
SELECT userid,'語文' AS course,cn_score AS score FROM tb_score1 UNION ALL SELECT userid,'數學' AS course,math_score AS score FROM tb_score1 UNION ALL SELECT userid,'英語' AS course,en_score AS score FROM tb_score1 UNION ALL SELECT userid,'政治' AS course,po_score AS score FROM tb_score1 ORDER BY userid
這裡將每個userid對應的多個科目的成績查出來,通過UNION ALL將結果集加起來,達到上圖的效果。
附:UNION與UNION ALL的區別(摘):
1.對重覆結果的處理:UNION會去掉重覆記錄,UNION ALL不會;
2.對排序的處理:UNION會排序,UNION ALL只是簡單地將兩個結果集合併;
3.效率方面的區別:因為UNION 會做去重和排序處理,因此效率比UNION ALL慢很多;