时间戳

首页 > 解决方案 > 通过检查每个行属性查询将发票添加到表中

sql - 通过检查每个行属性查询将发票添加到表中

问题描述

Invoice
========
InvoiceID
LineID
ItemID
AmountInc
MenuPrice

#TempTable
========
InvoiceID
AmountInc (will have Sum(AmountInc))

我想根据上面的列构建一个从 Invoice 表中派生数据的表。

发票需要至少有一行 MenuPrice = Yes 的项目才能添加到临时表中，例如：

> InvoiceTable
> InvoiceID | LineID | ItemID | AmountInc | MenuPrice
  0001      | 1      | A001   | 10        | Yes
  0001      | 2      | A002   | 20        | No
  0002      | 1      | A001   | 10        | No
  0002      | 2      | A003   | 15        | No
  0002      | 3      | A001   | 10        | No
  0003      | 1      | A003   | 15        | Yes
  0003      | 2      | A004   | 25        | Yes

结果将是：

#TempTable
> InvoiceID | AmountInc
  0001      | 30        
  0003      | 15

如果可能的话，或者这个：

#TempTable
> InvoiceID | AmountInc | MenuPrice
  0001      | 30        | Mixed
  0002      | 35        | No
  0003      | 40        | Yes

写这个的最好方法是什么？

标签： sqlsql-servertsql

解决方案

试试这样：

DECLARE @tbl TABLE(InvoiceID INT, LineID INT,ItemID VARCHAR(10),AmountInc INT,MenuPrice VARCHAR(10));
INSERT INTO @tbl VALUES
 (0001,1,'A001',10,'Yes')
,(0001,2,'A002',20,'No')
,(0002,1,'A001',10,'No')
,(0002,2,'A003',15,'No')
,(0002,3,'A001',10,'No')
,(0003,1,'A003',15,'Yes')
,(0003,2,'A004',25,'Yes');

--查询将首先使用一个 cte 来查找InvoiceID至少一个的所有值Yes，然后这个有效 ID 列表将用于INNER JOIN该列表。

WITH AllYes AS
(
    SELECT InvoiceID
    FROM @tbl
    WHERE MenuPrice='Yes'
    GROUP BY InvoiceID
)
SELECT t.InvoiceID,SUM(t.AmountInc) AS SumAount
FROM @tbl t
INNER JOIN AllYes y ON t.InvoiceID=y.InvoiceID
GROUP BY t.InvoiceID;

结果

InvoiceID   SumAount
1           30
3           40

此查询将带回您的替代预期结果：

SELECT t.InvoiceID
      ,SUM(t.AmountInc) AS SumAmount
      ,A.YesExists
      ,A.NoExists
      ,CASE WHEN YesExists+NoExists=2 THEN 'Mixed' ELSE
            CASE WHEN NoExists=1 THEN 'No' ELSE 'Yes' END 
       END AS InvStatus  
FROM @tbl t
CROSS APPLY(SELECT CASE WHEN EXISTS(SELECT 1 FROM @tbl t1 WHERE t1.InvoiceID=t.InvoiceID AND MenuPrice='Yes') THEN 1 ELSE 0 END AS YesExists
                  ,CASE WHEN EXISTS(SELECT 1 FROM @tbl t1 WHERE t1.InvoiceID=t.InvoiceID AND MenuPrice='No') THEN 1 ELSE 0 END AS NoExists ) A
GROUP BY t.InvoiceID,A.YesExists,A.NoExists;

推荐阅读