时间戳

首页 > 解决方案 > MsSQL LEFT JOIN 与双 GROUP BY

sql - MsSQL LEFT JOIN 与双 GROUP BY

问题描述

我目前正在尝试将 MsSQL 用于产品运输数据库。我花了很长时间尝试编写一个 SQL 查询，以获取从今天开始的 4 天内每个日期到达某个地区每个交货地点的产品数量。在一个区域中意味着有另一个位置是该位置的父位置。

相关表格是产品和位置，结构如下

Products    
ProductID  DeliveryDate  DestinationID
1          2018-10-05    1
2          2018-10-05    2
3          2018-10-05    3
4          2018-10-06    1
5          2018-10-06    5

Locations
LocationID  OwnerID     
1           4
2           4
3           4
4           Null
5           6
6           Null

所需的输出如下

DeliveryDate  Destination ProductCount
2018-10-04    1           Null
2018-10-04    2           Null
2018-10-04    3           Null
2018-10-05    1           1
2018-10-05    2           1
2018-10-05    3           1 
2018-10-06    1           1
2018-10-06    2           Null
2018-10-06    3           Null
2018-10-07    1           Null
2018-10-07    2           Null
2018-10-07    3           Null

到目前为止我尝试过的是这个

DECLARE @startdate DATETIME
    ,@enddate DATETIME

SET @startdate = convert(varchar, '2018-10-04 00:00:00', 102)
SET @enddate = convert(varchar, '2018-10-07 00:00:00', 102)

;WITH DateArray
AS (
    SELECT @startdate DateVal

    UNION ALL

    SELECT DateVal + 1
    FROM DateArray
    WHERE DateVal + 1 <= @enddate
    )
SELECT * FROM
(SELECT da.DateVal AS DeliveryDate
FROM DateArray da) a
LEFT JOIN 
(SELECT ISNULL(COUNT(p.ProductID),0) AS ProductCount,
        DeliveryDate,
        ISNULL(p.DestinationID,'') AS Destination
FROM Product p
AND p.DestinationID IN(SELECT LocationID FROM Locations WHERE OwnerID = 4)
GROUP BY p.DestinationID, p.DeliveryDate) AS b
ON b.DeliveryDate = a.DeliveryDate

结果确保所有日期都存在，即使 ProductCount 为 null，但是，如果 count 为 null，则不会显示每个 Destination。如下图所示：

DeliveryDate  Destination  ProductCount
2018-10-04    Null        Null
2018-10-05    1           1
2018-10-05    2           1
2018-10-05    3           1 
2018-10-06    1           1
2018-10-07    Null        Null

我花了两天时间顽固地试图通过许多在线 SQL 资源和搜索 StackOverFlow 来解决这个问题，但没有运气。

标签： sqlsql-serverjoin

解决方案

我建议创建一个数字表或日历表，但递归查询对于小型集合就足够了。

DECLARE
  @startdate DATETIME = '2018-10-04 00:00:00',
  @enddate   DATETIME = '2018-10-07 00:00:00';

WITH
  DateArray AS
(
  SELECT @startdate DateVal
  UNION ALL
  SELECT DateVal + 1 FROM DateArray WHERE DateVal + 1 <= @enddate
)
SELECT
  DateArray.DateVal          AS DeliveryDate,
  Locations.LocationID       AS Destination,
  COUNT(Products.ProductID)  AS ProductCount
FROM
  DateArray
CROSS JOIN
(
  SELECT * FROM Locations WHERE OwnerID = 4
)
  Locations
LEFT JOIN
  Products
    ON  Products.DeliveryDate  = DateArray.DateVal
    AND Products.DestinationID = Locations.LocationID
GROUP BY
  DateArray.DateVal,
  Locations.LocationID

或者...

SELECT
  DateArray.DateVal          AS DeliveryDate,
  Locations.LocationID       AS Destination,
  Products.ProductCount      AS ProductCount
FROM
  DateArray
CROSS JOIN
(
  SELECT * FROM Locations WHERE OwnerID = 4
)
  Locations
LEFT JOIN
(
  SELECT DeliveryDate, DestinationID, COUNT(*) AS ProductCount
    FROM Products
GROUP BY DeliveryDate, DestinationID
)
  Products
    ON  Products.DeliveryDate  = DateArray.DateVal
    AND Products.DestinationID = Locations.LocationID

推荐阅读