sql - 如果该月没有条目，如何选择该月的最后一天 SQL

Question

我有一个包含以下条目的表，

<表类="s-表"><头>身份证<日>日期 频率<正文>1'2014-05-18'51'2014-06-19'41'2014-07-20'252'2014-05-20'73'2014-05-18'43'2014-06-20'14'2014-05-18'6

在这种情况下，我正在尝试提取两个日期之间的值。2014-05-18 至 2014-07-20。选择输出应该有每个月的条目，如果任何一个月没有值，它应该默认为该月的零，并且该日期应该是该特定月份的最后一天。

预期的输出是

<表类="s-表"><头>身份证<日>日期 频率<正文>1'2014-05-18'51'2014-06-19'41'2014-07-20'252'2014-05-20'72'2014-06-30'02'2014-07-31'03'2014-05-18'43'2014-06-20'13'2014-07-31'04'2014-05-18'64'2014-06-30'04'2014-07-31'0

我试过使用这个但是它给出了那个月所有日子的列表，但我只对这个月的最后一天感兴趣。

    ;WITH n AS
    (
      SELECT n = 0
      UNION ALL
      SELECT n + 1 FROM n
      WHERE n < DATEDIFF(DAY, '20140518', '20140720')
    ),
    range AS
    (
      SELECT [date] = DATEADD(DAY, n,'20140518') FROM n
    ),
    IDs AS
    (
      SELECT ID 
        FROM dbo.MyTable AS t
        INNER JOIN range
        ON t.[date] = range.[date]
        GROUP BY ID
    )
    SELECT 
      IDs.ID,
      range.[date],
      Frequency = COALESCE(t.Frequency, 0)
    FROM range CROSS JOIN IDs
    LEFT OUTER JOIN dbo.MyTable AS t
    ON t.[date] = range.date 
    AND IDs.ID = t.ID;

Answer 1

这是“填空”查询类型的一个有趣变体。不同之处在于，如果原始行匹配，您希望保留它们，但为不匹配的月份创建一个新行。

您可以通过以下方式解决此问题:

• 每月生成一个包含一行的范围。
• 交叉连接为每个 ID 和每个月生成一行。
• 连接回原始数据以获取任何已存在的行。

这看起来像:

WITH months AS (
      SELECT DATEFROMPARTS(YEAR('20140518'), MONTH('20140518'), 1) as mon
      UNION ALL
      SELECT DATEADD(MONTH, 1, mon)
      FROM months
      WHERE mon < DATEFROMPARTS(YEAR('20140720'), MONTH('20140720'), 1)
    )
SELECT i.id,
       COALESCE(t.date, EOMONTH(m.mon)),
       COALESCE(t.frequency, 0)
FROM (SELECT DISTINCT ID FROM dbo.MyTable) i CROSS JOIN
     months m LEFT JOIN
     dbo.MyTable t
     ON t.id = i.id AND
        t.date >= m.mon AND
        t.date < DATEADD(month, 1, m.mon);

请注意，您可以直接在递归 CTE 中生成所需的日期。无需先生成数字。