Mysql group_concat的反向應用實作（Mysql列轉行）

本文參考：http://blog.chinaunix.net/uid-411974-id-3990697.html，示例具業務場景略作改動。

-- 氣溫采集表，每天每個整點的氣溫度數
CREATE TABLE temp (
  id INT,
  time1 INT,
  time2 INT,
  time3 INT,
  time4 INT,
  receive_date DATE
) ;

-- 初始化資料
INSERT INTO `temp` (`id`, `time1`, `time2`, `time3`, `time4`, `receive_date`) VALUES('1','10','15','25','16','2016-05-11');
INSERT INTO `temp` (`id`, `time1`, `time2`, `time3`, `time4`, `receive_date`) VALUES('2','9','14','26','15','2016-05-10');
INSERT INTO `temp` (`id`, `time1`, `time2`, `time3`, `time4`, `receive_date`) VALUES('3','8','13','27','14','2016-05-09');
INSERT INTO `temp` (`id`, `time1`, `time2`, `time3`, `time4`, `receive_date`) VALUES('4','7','12','28','13','2016-05-08');
INSERT INTO `temp` (`id`, `time1`, `time2`, `time3`, `time4`, `receive_date`) VALUES('5','6','11','29','12','2016-05-07');
           

問題：查詢5月份溫度高于15度的次數，sql如何寫？

如果清單較多，如100列，sql如何寫？

解決：使用cross join，具體用法可參考文章頂部的部落格原文。

腳本如下：

SELECT 
  receive_date,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(sub_id, ',', seq),
    ',',
    - 1
  ) sub_id,
  seq 
FROM
  (SELECT 
    0 seq 
  UNION
  SELECT 
    1 seq 
  UNION
  SELECT 
    2 seq 
  UNION
  SELECT 
    3 seq 
  UNION
  SELECT 
    4 seq) sequence 
  CROSS JOIN 
    (SELECT 
      CONCAT(
        p.time1,
        ',',
        p.time2,
        ',',
        p.time3,
        ',',
        p.time4
      ) sub_id,
      p.receive_date 
    FROM
      temp p) temp2 
WHERE seq BETWEEN 1 
  AND 4 
ORDER BY receive_date,
  seq ASC ;