水平到垂直表

我有這個查詢

SELECT quiz_id, COUNT(case when options LIKE '%question_id_8":"1%' then 1 else null end) as 'answered 1',
COUNT(case when options LIKE '%question_id_8":"2%' then 1 else null end) as 'answered 2',
COUNT(case when options LIKE '%question_id_8":"3%' then 1 else null end) as 'answered 3',
COUNT(case when options LIKE '%question_id_8":"4%' then 1 else null end) as 'answered 4',
COUNT(case when options LIKE '%question_id_8":"5%' then 1 else null end) as 'answered 5',
COUNT(case when options LIKE '%question_id_8":"6%' then 1 else null end) as 'answered 6',
COUNT(case when options LIKE '%question_id_8":"7%' then 1 else null end) as 'answered 7',
COUNT(case when options LIKE '%question_id_8":"8%' then 1 else null end) as 'answered 8'
FROM wp_aysquiz_reports
WHERE quiz_id = 2;

我有這個結果

我怎樣才能做到這一點：

Answered 1    7
Answered 2    3
Answered 3    1
Answered 4    1
Answered 5    1
Answered 6    1
Answered 7    1
Answered 8    0

您在原始查詢中所做的操作被視為旋轉，您可以在之後應用 unpivot 或不進行旋轉。

關於您的數據的特殊之處似乎options可能有多個匹配like條件，因此您可以union all每個版本並彙總：

select quiz_id, question, count(*) 
from (
         SELECT quiz_id, case when options LIKE '%question_id_8":"1%' then 'answered 1' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"2%' then 'answered 2' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"3%' then 'answered 3' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"4%' then 'answered 4' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"5%' then 'answered 5' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"6%' then 'answered 6' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"7%' then 'answered 7' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
union all SELECT quiz_id, case when options LIKE '%question_id_8":"8%' then 'answered 8' else null end as question FROM wp_aysquiz_reports where quiz_id = 2
)
where question is not null
group by quiz_id,question

注意我已經包含了一個group by條件，這是因為你想要一個 row perquiz_id, question而你不只是希望 MySQL 給你一個任意值（參見MySQL 的 GROUP BY 處理）。

case when您可以只使用過濾器等，而不是在每個選擇中使用表達式where options LIKE '%question_id_8":"1%'。這可能會更快，您最後也不需要where question is not null過濾器。我更喜歡重寫，因為從您的原始陳述中更容易理解。

引用自：https://dba.stackexchange.com/questions/294883