更好的SQL使用where子句计算项的数量

Question

我有这个查询，我知道有一个更好的方法来写它。下面是对订单进行计数的查询，以找出库存中还剩下什么。

DECLARE @reserveDate as Datetime = '10/5/2011 10:20'
SELECT p.Name 
     , p.Quantity
     , (SELECT COUNT(*) 
          FROM [Order] o 
         WHERE o.ProductId = p.Id 
           AND o.Completed = 1) as Completed 
     , (SELECT COUNT(*) 
          FROM [Order] o 
         WHERE o.ProductId = p.Id 
           AND o.Completed <> 1 
           AND o.ModifiedDate >= @reserveDate) as Reserved 
     , (SELECT COUNT(*) 
          FROM [Order] o 
         WHERE o.ProductId = p.Id 
           AND o.Completed <> 1 
           AND o.ModifiedDate < @reserveDate) as ReserveExpired  
   --, (Quantity - Completed - Reserved) as Available
  FROM Product p 

====================================

这里是一个脚本的表

IF EXISTS ( SELECT  * FROM  sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order]') AND type IN ( N'U' ) )    DROP TABLE [Order]
IF EXISTS ( SELECT  * FROM  sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type IN ( N'U' ) )  DROP TABLE [Product]
-- Product --
PRINT N' [Product]  ' 
CREATE TABLE Product
    (
      [Id] INT NOT NULL IDENTITY PRIMARY KEY,
      [Name] NVARCHAR(50) ,
      [Quantity] INT ,
    ); 
SET IDENTITY_INSERT Product ON 
INSERT  INTO Product ( [Id] , [Name] ,  [Quantity]) VALUES  ( '1', 'Tea Package',  7000 )
INSERT  INTO Product ( [Id] , [Name] ,  [Quantity]) VALUES  ( '2', 'Sugar Package',  8000)
SET IDENTITY_INSERT Product OFF 

-- Order --
PRINT N' [Order]'
CREATE TABLE [Order]
    (
      [Id] INT NOT NULL IDENTITY PRIMARY KEY ,
      [ProductId] INT   ,
      [Completed] Bit,
      [ModifiedDate] DATETIME 
    ); 
ALTER TABLE [Order] ADD CONSTRAINT FK_Product_Order FOREIGN KEY (ProductId) REFERENCES [Product] (Id) 
GO  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/5/2011 11:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/6/2011 11:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 1, 0,   '10/6/2011 11:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 1,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 0,   '10/5/2011 10:10'  )  
INSERT  INTO [Order] ([ProductId],  [Completed],   [ModifiedDate]  ) VALUES  ( 2, 0,   '10/6/2011 10:10'  )  

Answer 1

您可以使用:

   SELECT p.Name 
        , p.Quantity
        , SUM(CASE WHEN o.completed = 1 THEN 1 ELSE 0 END) AS Completed 
        , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) AS Reserved 
        , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) AS ReserveExpired  
        , p.Quantity - 
          SUM(CASE WHEN o.completed = 1 THEN 1 ELSE 0 END) -
          SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) AS available
     FROM Product p 
LEFT JOIN ORDER o ON o.productid = p.id
 GROUP BY p.Name, p.Quantity

或者，下面是等效的&更容易阅读:

SELECT x.name, 
       x.quantity,
       x.completed,
       x.reserved,
       x.reserveexpired,
       x.quantity - x.completed - x.reserved AS available
  FROM (SELECT p.Name 
             , p.Quantity
             , SUM(CASE WHEN o.completed = 1 THEN 1 ELSE 0 END) AS Completed 
             , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) AS Reserved 
             , SUM(CASE WHEN o.completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) AS ReserveExpired  
          FROM Product p 
     LEFT JOIN ORDER o ON o.productid = p.id
      GROUP BY p.Name, p.Quantity) x

Answer 2

您可以在T-SQL本身中减去项，例如:

SELECT A - B AS C FROM TABLE WHERE ID=1

虽然我不确定最好的方法来做你的子查询。我也不知道你是否需要保留实际数量、完成数量、预留数量等等。在您的编程中，或者如果您只需要这些值来计算可用数量。如果在SQL之外需要它们，那么内部的减法将没有任何帮助。

Answer 3

可以使用CROSS APPLY/OUTER APPLY操作符:

CREATE INDEX aaa
ON [Order](ProductId)
INCLUDE (Completed,ModifiedDate);
PRINT '***** Sol1 *****'
SELECT 
     p.Name 
    ,p.Quantity
    ,ISNULL(ca.Completed,0) Completed
    ,ISNULL(ca.Reserved,0) Reserved
    ,ISNULL(ca.ReserveExpired,0) ReserveExpired
    ,p.Quantity - ISNULL(ca.Completed,0) - ISNULL(ca.Reserved,0) Available
FROM Product p 
OUTER APPLY
(
    SELECT 
         SUM(CASE WHEN o.Completed = 1 THEN 1 ELSE 0 END) Completed
        ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) Reserved
        ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) ReserveExpired
    FROM [Order] o --WITH(FORCESEEK) or WITH(INDEX=aaa)
    WHERE o.ProductId = p.Id 
) ca;
PRINT '***** Sol2 *****'
SELECT 
     p.Name 
    ,p.Quantity
    ,ISNULL(q.Completed,0) Completed
    ,ISNULL(q.Reserved,0) Reserved
    ,ISNULL(q.ReserveExpired,0) ReserveExpired
    ,p.Quantity - ISNULL(q.Completed,0) - ISNULL(q.Reserved,0) Available
FROM    Product p
LEFT MERGE JOIN --or LEFT JOIN
(
    SELECT   o.ProductId
            ,SUM(CASE WHEN o.Completed = 1 THEN 1 ELSE 0 END) Completed
            ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate >= @reserveDate THEN 1 ELSE 0 END) Reserved
            ,SUM(CASE WHEN o.Completed <> 1 AND o.ModifiedDate < @reserveDate THEN 1 ELSE 0 END) ReserveExpired     
    FROM    [Order] o 
    GROUP BY o.ProductId
) q ON p.Id = q.ProductId;