主页 / dba / 问题 / 196905
Accepted
Agnel Amodia
Asked: 2018-02-03 06:09:17 +0800 CST

如何显示按日期分组的日期范围内的所有日期？

  • 772

假设我有一个名为“products”的表：

Productive product-name   product-quantity  created-date
111            ABC          10               1/10/2018
222            XYZ          20               1/10/2018
333            PQR          30               1/11/2018
444            MNC          40               1/15/2018
555            DEF          50               1/11/2018

如果我运行此查询：

Select convert(date,createddate,101), count(product-quantity) from products
Where created-date > 1/9/2018 and created-date < 1/16/1018
group by convert(date,createddate,101)

这会给我输出，例如

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/15/2018            40

我想实现：

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/12/2018            00
1/13/2018            00
1/14/2018            00
1/15/2018            40

我该怎么做？

sql-server group-by

1 个回答

  1. Best Answer

    日历表的基本实现。

    CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, DAY(@date), DATEPART(weekday, @date), DATENAME(weekday, @date));
    SET @date = DATEADD(day, 1, @date);
END
GO

    31 行受影响

    CREATE TABLE products(Productive int, Name char(3), Qty int, Created date);
INSERT INTO products VALUES
(111, 'ABC', 10, '1/10/2018'),
(222, 'XYZ', 20, '1/10/2018'),
(333, 'PQR', 30, '1/11/2018'),
(444, 'MNC', 40, '1/15/2018'),
(555, 'DEF', 50, '1/11/2018');
GO

    5 行受影响

    请注意，我使用 LEFT JOIN 从日历表中获取所有记录，并且只获取与产品表匹配的记录。

    SELECT     c.cDate AS Created, COALESCE(Qty, 0) AS Qty
FROM       Calendar c
LEFT JOIN  (SELECT   CONVERT(date, Created, 101) AS [Created],
                     COUNT(Qty) AS [Qty]
            FROM     products
            GROUP BY CONVERT(date, Created, 101)) p
ON         p.Created = c.cDate
WHERE      c.cDate >= (SELECT MIN(Created) FROM products)
AND        c.cDate <= (SELECT MAX(Created) FROM products);
GO

    创建 | 数量
:----------------- | --:
2018 年 10 月 1 日 00:00:00 | 2
2018 年 11 月 1 日 00:00:00 | 2
2018 年 12 月 1 日 00:00:00 | 0
13/01/2018 00:00:00 | 0
14/01/2018 00:00:00 | 0
15/01/2018 00:00:00 | 1

    dbfiddle在这里

    • 0

相关问题