sql - 一列中两种不同条件的总和

Question

我有这个 T-SQL 查询

SELECT 
    ProductId, SUM(Quantity) AS Quantity 
FROM 
    ProductStockIn 
WHERE  
    OrgName = @OrgName
    AND Statecode = 'ADD' OR Statecode = 'ORDER' 
    AND CreatedOn BETWEEN DATEADD(MS, 3, @LastGenerationDate) AND @GenerationDate 
GROUP BY 
    ProductId) stockin  ON p.ProductId = stockin.ProductId

我有这种桌子

|quantity | StateCode | CreatedOn |
+---------+-----------+-----------+
|400      | ORDER     | <date>    |
|400      | ADD       | <date>    |

我想对所有 StateCode 等于 'Order' 和 'Add' 的数量求和，但在我的查询中它只选择 StateCode = ADD .. 当我尝试 Condition StateCode = 'ADD' AND St​​ateCode = 'ORDER' 时，没有选择任何内容.

标签： sqlsql-serverssms

Answer 1

你可以试试下面的 -

SELECT ProductId, SUM(Quantity) AS Quantity 
FROM ProductStockIn 
WHERE  OrgName = @OrgName AND Statecode in( 'ADD','ORDER')
AND CreatedOn BETWEEN DATEADD(MS,3,@LastGenerationDate)
AND @GenerationDate 
GROUP BY ProductId
having count(distinct Statecode)=2