Mysql之如何實現(xiàn)行列轉(zhuǎn)換

更新時間：2023年06月15日 10:30:52 作者：藍天⊙白云

這篇文章主要介紹了Mysql之如何實現(xiàn)行列轉(zhuǎn)換問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

1. 行轉(zhuǎn)列

方案一

select  name,
sum(case when course='java' then grade end) as java,
sum(case when course='C++' then grade end) as C++,
sum(case when course='C#' then grade end) as C#
from test group by name

方案二

select distinct c.`name` AS name,
(select grade from test where name = c.`name` and course = 'java' )as java,
(select grade from test where name = c.`name` and course = 'C++' )as C++,
(select grade from test where name = c.`name` and course = 'C#' )as C#
from test c

結(jié)合項目用到的sql：

select  MAIN_STATION_CODE_ as stationCode,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=curdate() then STATION_ else "" end as firstDay,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=DATE_SUB(curdate(),INTERVAL -1 DAY) then STATION_ else "" end as secondDay,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=DATE_SUB(curdate(),INTERVAL -2 DAY) then STATION_ else "" end as thirdDay,
case when SUBSTR(PLAN_START_DATE_, 1, 10)=DATE_SUB(curdate(),INTERVAL -3 DAY) then STATION_ else "" end as fourthDay
from ps_overhaul_plan_row group by MAIN_STATION_CODE_

方案二拓展

上面我們是采用逐個判斷并拼接的方式來進行，那如果極端情況下，假如有成百上千個值需要判斷怎么辦？

這種情況下，能夠立即想到的是采用函數(shù)來拼接出對應的語句塊，再合并在一起執(zhí)行。

假如我們需要拼接下面一句：

(select GRADE_ FROM grade where name_ = c.name_ and course_ = ‘Math') as Math，

使用concat函數(shù)（例子）：

select c.name_,concat('(SELECT grade_ from grade where name_=c.name_ ', 'and course_ =''', c.course_, '''', ') as ',c.course_, ')') from grade c

然后再次使用group_concat函數(shù)將多行轉(zhuǎn)為一行：

SELECT GROUP_CONCAT(distinct concat('(SELECT grade_ from grade where name_=c.name_ ', 'and course_ =''', c.course_, '''', ') as ',c.course_, ')')) from grade c

最后再使用存儲過程完成動態(tài)sql執(zhí)行。

方案三：帶匯總

select ifnull(uid,'Total') uid, uname,
sum(if(`course`='java',grade,0)) 'java',
sum(if(`course`='C++',grade,0)) 'C++',
sum(if(`course`='C#',grade,0)) 'C#',
sum(score) 'total'
from course
group by uid
with ROLLUP

方案四：使用group_concat函數(shù)

SELECT
    id,
    name,
    group_concat(CASE WHEN subject = 'Math' THEN score END SEPARATOR '') '數(shù)學',
    group_concat(CASE WHEN subject = 'English' THEN score END SEPARATOR '') '英語'
FROM test1
GROUP BY name;

2. 列轉(zhuǎn)行

原表：

實現(xiàn)SQL：

SELECT d.name_,'Math' AS subject,d.Math_ AS score FROM 
grade_column d
UNION ALL
SELECT d.name_, 'English' as SUBJECT,d.English_ as score from
grade_column d

列轉(zhuǎn)行效果如下：