时间戳

首页 > 解决方案 > 尝试按小时对查询进行分组

mysql - 尝试按小时对查询进行分组

问题描述

尝试按小时对我的查询进行分组，如果没有找到，则为“0”。

SELECT 
tmhours.hours_value,
COALESCE(cc.countingSheep,0) AS countingSheep
FROM time_hours as tmhours
LEFT JOIN (
SELECT count(*) as countingSheep, company_id, `sales_date`
FROM tbl_cc 
WHERE `sales_date` BETWEEN '2019-05-01 00:00:00' AND '2019-05-01 23:59:59' AND company_id = '12345'  ) as cc on date_format(sales_date, '%H') = tmhours.hours_value
GROUP BY tmhours.hours_value

time_hours 表只包含 01,02,03,04 .... 22, 23

根据上面的查询，我只得到 0 直到 07

所以：

01 0
02 0
03 0
04 0
05 0
06 0
07 - 57 (the first match in the DB is 07:14:35) - the 57 is the total count, it's not grouping results
08 0
09 0
...
...
22 0
23 0

我尝试通过内部选择删除组，尝试移动 date_format = hours_value。

标签： mysql

解决方案

您在外部查询中没有聚合函数，因此如果您需要不同的结果，请使用 DISTINCT（分组依据可以产生意外结果）但在您的情况下似乎没有必要

相反，您根据内部连接中的小时数错过了该组

  SELECT 
    tmhours.hours_value,
    COALESCE(cc.countingSheep,0) AS countingSheep
  FROM time_hours as tmhours
   LEFT JOIN ( SELECT count(*) as countingSheep, company_id, date_format(sales_date, '%H')
      FROM tbl_cc 
      WHERE `sales_date` BETWEEN '2019-05-01 00:00:00' AND '2019-05-01 23:59:59' 
       AND company_id = '12345'
       GROUP BY company_id , date_format(sales_date, '%H')
      ) as cc on date_format(sales_date, '%H') = tmhours.hours_value

推荐阅读