mysql左转联接，后面跟着组by with中间的范围

Question

我对该怎么做感到困惑。下面是我当前的左联接命令，它运行得很好：

SELECT t1.avg_temperature as T_aver,
       t2.new_confirmed as count 
FROM 3_day_avg as t1 
LEFT JOIN table2 as t2 on t1.date = t2.date

这张桌子做得很好：

T_aver    |count|
-----------------
-0.2      | 2   |
3         | 2   |
5         | 1   |
-2.3      | 4   |
22        | 0   |

但是现在我想更进一步，按照T_aver的范围(如0-5、6-10、11-15等)和SUM()对计数列进行分组。例如，如果我要将范围放在上面的-10到0和0到30的左侧联接表示例中，最后一个表将如下所示：

Trange    |count|
-----------------
-10 - 0   | 6   |
0 - 30    | 3   |

以上的转变是我感到困惑的地方，我害怕让我的生活变得更简单，我只需要创建一个大桌子，而不是…

提前感谢

Answer 1

你离我很近！只需从现有查询开始，然后包装它。

对于您的回收箱，如果不希望绑定到固定的间隔，请使用另一个查找表：

垃圾箱：

mintemp | maxtemp
 -10    | 0
   0    | 30

我将使用num而不是count，因为我从不使用保留词作为列：

SELECT 
  CONCAT(mintemp, ' - ', maxtemp) AS Trange,
  SUM(baseview.num) AS num
FROM bins
INNER JOIN (
  SELECT
    t1.avg_temperature as T_aver,
    t2.new_confirmed as num
  FROM 3_day_avg as t1
  LEFT JOIN table2 as t2 on t1.date = t2.date
  ) AS baseview
ON baseview.T_aver>bins.mintemp AND baseview.T_aver<=bins.maxtemp
GROUP BY bins.mintemp;

Answer 2

SELECT ranges.caption Trange,
       SUM(t2.new_confirmed) as `count` 
FROM 3_day_avg as t1 
LEFT JOIN table2 as t2 on t1.date = t2.date
JOIN ( SELECT -10 t_from, 0 t_to, '-10 - 0' caption 
       UNION ALL
       SELECT 0, 30, '0 - 30' ) ranges ON t1.avg_temperature >= ranges.t_from 
                                      AND t1.avg_temperature < ranges.t_to 
GROUP BY ranges.caption;

最好是创建静态ranges表，而不是动态生成。这允许创建和存储许多预定义的范围集。

Answer 3

您可以按包含回收箱的CASE表达式进行分组：

SELECT
  CASE 
    WHEN t1.avg_temperature >= -10 and t1.avg_temperature <= 0 THEN '-10 - 0'
    WHEN t1.avg_temperature > 0 and t1.avg_temperature <=30 THEN '0 - 30'
  END AS Trange,
  SUM(t2.new_confirmed) AS count 
FROM 3_day_avg AS t1 LEFT JOIN table2 AS t2 
ON t1.date = t2.date
GROUP BY Trange

您可以在CASE表达式中添加更多的回收箱，更改范围和不等式符号以满足您的要求。