sql - 按日期与空组分组

Question

我有一个 loginAudit 表，我正在尝试获取每天所有登录的计数。我想要做的是让没有登录的日子返回他们的日子，登录计数为 0。目前，没有登录的日子没有返回任何行。

这可能是不可能的，必须在返回查询结果后填写应用程序中的空组吗？

  SELECT DATEADD(day, DATEDIFF(day,0,LoginAudit.LoginDateTime), 0) as LoginDate,  
         COUNT(DISTINCT LoginAudit.LoginAuditID) AS Logins  
    FROM LoginAudit 
GROUP BY DATEADD(day, DATEDIFF(day,0,LoginAudit.LoginDateTime), 0)
ORDER BY 1  

Answer 1

本质上，您要问的是将您的表格连接到日期“表格”。日期表将没有间隙，您将根据日期值进行分组。那么如何创建日期表呢？

在SQL for Smarties ，建议您保留一个整数表，以备不时之需，以备不时之需。然后，您可以通过将表格加入其中来选择所需的任何序列。

因此，如果您有一个整数表，其中的值根据需要从 NOW() 返回尽可能多的天数，您可以执行以下操作:

SELECT DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY) AS thedate, 
       COUNT(DISTINCT LoginAudit.LoginAuditID) AS logins
FROM i LEFT JOIN dual ON (DATE_SUB(NOW(), INTERVAL i.intvalue DAY)= day)
GROUP BY DATE_SUB(CURDATE(), INTERVAL i.intvalue DAY)
ORDER BY i DESC;

预计到达时间，用于 mysql:

//创建整数表

create table i(i integer not null primary key);
insert into i values (0),(1),(2) ... (9);

如果我需要 0-99 连续数字:

SELECT 10*t.i + u.i AS number
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY number;

如果我需要连续的日期:

SELECT date_sub(curdate(), interval (10*t.i + u.i) DAY) as thedate 
  FROM i AS u
CROSS JOIN 
  i AS t
ORDER BY thedate;