Mysql group_concat的反向应用实现（Mysql列转行）

最新推荐文章于 2025-10-31 11:06:54 发布

转载最新推荐文章于 2025-10-31 11:06:54 发布 · 5.7k 阅读

文章标签：

#mysql #group_concat #反向 #列转行 #逆操作

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种通过SQL脚本查询特定时间段内气温超过设定阈值的方法，并提供了一个实际的例子来展示如何处理包含多个时间点的数据。

本文参考：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 ;