計算子記錄具有特定值的每一天的記錄

我有兩個表（請參閱最后創建表和資料的 SQL 腳本）：

帶列的訂單表

• ID
• 日期

帶列的 OrderItems 表

• 訂單號
• 專案型別 ID

我需要選擇每天至少有一個 ItemTypeID=6000 的專案的訂單數和訂單中根本沒有 ItemTypeID=6000 的訂單數。到目前為止，我得到了這個，但我不知道如何繼續：

SELECT
    DATEADD(dd, 0, DATEDIFF(dd, 0, OrderDate)) AS OrderDate,
    COUNT(DISTINCT(Orders.ID)) AS TotalOrders
FROM
    Orders
JOIN
    OrderItems
ON
    Orders.ID = OrderItems.OrderID 
WHERE
    OrderItems.ItemTypeID = 6000
GROUP BY
    DATEADD(dd, 0, DATEDIFF(dd, 0, OrderDate))

創建測驗資料的腳本：

CREATE TABLE OrderItems(
    [ID] [int] NOT NULL,
    [OrderID] [int] NOT NULL,
    [ItemTypeID] [int] NOT NULL
)
CREATE TABLE Orders(
    [ID] [int] NOT NULL,
    [OrderDate] [date] NOT NULL
)
GO
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (1, 1, 1000)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (2, 1, 6000)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (3, 2, 1000)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (4, 3, 1000)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (5, 3, 1000)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (6, 4, 1000)
INSERT [OrderItems] ([ID], [OrderID], [ItemTypeID]) VALUES (7, 4, 6000)
INSERT [Orders] ([ID], [OrderDate]) VALUES (1, CAST(N'2021-12-01' AS Date))
INSERT [Orders] ([ID], [OrderDate]) VALUES (2, CAST(N'2021-12-01' AS Date))
INSERT [Orders] ([ID], [OrderDate]) VALUES (3, CAST(N'2021-12-02' AS Date))
INSERT [Orders] ([ID], [OrderDate]) VALUES (4, CAST(N'2021-12-03' AS Date))
GO

預期結果應如下所示：

OrderDate   OrdersWithItem  OrdersWithoutItem
2021-12-01  1               1
2021-12-02  0               1
2021-12-03  1               0

uj5u.com熱心網友回復：

（類似于 xQbert 的回答）我通常使用 CROSS APPLY 來計算一個中間值，然后我可以用它來提供以后的邏輯 - 在這種情況下，一個標志指示訂單是否具有所需的專案。

嘗試：

SELECT
    O.OrderDate,
    OrdersWithItem = COUNT(CASE WHEN X.HasItem = 1 THEN 1 END),
    OrdersWithoutItem = COUNT(CASE WHEN X.HasItem = 0 THEN 1 END)
FROM Orders O
CROSS APPLY (
    SELECT HasItem = CASE WHEN EXISTS(
        SELECT * FROM OrderItems OI WHERE OI.OrderID = O.ID AND OI.ItemTypeID = 6000
        ) THEN 1 ELSE 0 END
) X
GROUP BY O.OrderDate
ORDER BY O.OrderDate

注意上面“COUNT(CASE...)”樣式中“THEN 1”中的“1”是任意的。它只需要與隱含的“ELSE NULL”情況區分開來。

uj5u.com熱心網友回復：

測驗：DBFiddle.uk 示例

我的理論是我們可以使用外部應用來獲取具有所需專案的訂單專案的前 1 行。計算那些，然后簡單地從總計數中減去該計數以獲得那些沒有的計數。當訂單沒有相關專案時，我們使用合并來處理來自外部應用程式的 NULL 結果。

您確實需要添加到您的測驗資料中，因為您沒有達到足夠的測驗用例來了解解決方案是否能滿足您的所有需求。

SELECT O.OrderDate
     , count(Z.hasItem)  OrdersWithItem
     , count(*)-count(Z.HasItem) as OrdersWithoutItems
FROM Orders O
OUTER APPLY (SELECT TOP 1 1 as hasItem
             FROM OrderItems OI
             WHERE OI.ItemTypeID=6000 
               AND O.ID = OI.OrderID
             ORDER BY OI.ID ) z
GROUP BY O.OrderDate

uj5u.com熱心網友回復：

一個稍微不同的答案：

select 
    O.OrderDate,
    count(case when Typ6000.OrderID is not null then O.ID end) as OrdersWithItem,
    count(case when Typ6000.OrderID is null then O.ID end) as OrdersWithoutItem
    
from #Orders O
     
     left join
     (
      select distinct OrderId
      from #OrderItems OI
      where OI.ItemTypeID=6000
     )  Typ6000
     on Typ6000.OrderId=O.ID

group by O.OrderDate

uj5u.com熱心網友回復：

您可以在聚合函式中使用邏輯，例如 COUNT

SELECT
  OrderDate
, COUNT(DISTINCT CASE WHEN ItemTypeID = 6000 THEN Orders.ID END) AS OrdersWithItem 
, COUNT(DISTINCT Orders.ID) - COUNT(DISTINCT CASE WHEN ItemTypeID = 6000 THEN Orders.ID END) AS OrdersWithoutItem
FROM Orders
JOIN OrderItems
  ON Orders.ID = OrderItems.OrderID 
GROUP BY OrderDate
ORDER BY OrderDate

關于db<>fiddle 的演示在這里

標籤：sql sql-server 查询语句

上一篇：具有兩個輸入的函式回傳空值

下一篇：為什么我不能使用參考SQL中其他列的checkConstraint將列添加到現有表中