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

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个日期应在合理范围内。