sql - 从数据库中获取每天登录的用户名

Question

我有这个数据库结构

username   logged_in            logged_out
------------------------------------------
user1      2011-04-03 19:32:01  2011-04-05 03:41:34
user2      2011-04-01 10:33:42  2011-05-01 23:15:23

我需要的是在特定日期登录的所有用户的列表，例如

day           logged users
2011-04-01    user2
2011-04-02    user2
2011-04-03    user2
2011-04-03    user1
2011-04-04    user2
2011-04-04    user1
2011-04-05    user2
2011-04-05    user1
...
2011-05-01    user2

我目前正在尝试使用单个 SQL 查询来完成此操作，但我真的不知道如何获取表中记录的所有天数的时间跨度以及如何将它们连接到登录的用户。

我最大的问题是如何创建数据库中所有天数的“虚拟”表...

Answer 1

DECLARE @from_date DATETIME, @to_date DATETIME

-- populate @from_date and @to_date based on reporting needs
-- possibly using MIN() and MAX() on your logged_in and logged_out fields

DECLARE
  @limit INT
SELECT
  @limit = DATEDIFF(DAY, @from_date, @to_date)
;
WITH
  calendar AS
(
  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @from_date), 0) AS date, 1 AS inc_a, 2 AS inc_b
UNION ALL
  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @from_date) + inc_a, 0), inc_a + inc_a + 1, inc_a + inc_a + 2 FROM calendar WHERE inc_a <= @limit
UNION ALL
  SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @from_date) + inc_b, 0), inc_b + inc_b + 1, inc_b + inc_b + 2 FROM calendar WHERE inc_b <= @limit
)

SELECT
  calendar.date,
  your_table.username
FROM
  your_table
INNER JOIN
  calendar
    ON  calendar.date >= DATEADD(DAY, DATEDIFF(DAY, 0, your_table.logged_id), 0)
    AND calendar.date <  your_table.logged_out

编辑

CTE 的二进制增长而不是线性增长。 2^100 日期应该给出一个合理的范围。