Pertanyaan sql lebih baik menghitung jumlah item dengan klausa di mana


Saya memiliki pertanyaan ini dan saya tahu ada cara yang lebih baik untuk menulisnya. Berikut adalah kueri yang menghitung pesanan untuk mencari tahu apa yang tersisa di Inventaris.

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 

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

di sini adalah skrip untuk tabel

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'  )  

4
2017-10-09 03:35


asal


Jawaban:


Anda dapat mengganti subkueri menggunakan:

   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

Bergantian, berikut ini setara & lebih mudah dibaca:

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

4
2017-10-09 03:54



Anda dapat mengurangi item dalam T-SQL itu sendiri, sesuatu seperti:

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

Meskipun saya tidak yakin cara terbaik untuk melakukannya dengan pertanyaan sub Anda. Saya juga tidak tahu apakah Anda perlu menjaga nilai Kuantitas, Selesai, Milik, & RerservedExpired dalam program Anda atau jika Anda hanya perlu menghitung Kuantitas Tersedia. Jika Anda membutuhkannya di luar SQL, maka pengurangan di dalamnya tidak akan membantu.


0
2017-10-09 03:45



Anda bisa menggunakannya CROSS APPLY/OUTER APPLY operator:

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;

0
2017-10-09 10:07