在一次选择中返回两个 Count(*) 结果

Question

我有一个存储过程，我希望它返回以下内容......

TotalItems  |  FailedItems
@totalItems | @failedItems

where --> @totalItems = `SELECT COUNT(*)
    From dbo.OdsBuild AS P 
    where P.StartTime Between @StartDate and @EndDate 
    AND P.Official = 1`

where --> @failedItems = `SELECT COUNT(*)
    From dbo.Items AS P
    where p.StartTime Between @StartDate and @EndDate 
    AND P.Official = 1 AND ( P.Result = 7 OR P.Result = 8 OR P.Result = 14)`

Answer 1

您正在寻找的是 GROUP BY 和 HAVING

Answer 2

子查询 SELECT COUNT

SELECT
    (SELECT COUNT(*)
        From dbo.OdsBuild AS P 
        where P.StartTime Between @StartDate and @EndDate 
        AND P.Official = 1) totalItems ,
    (SELECT COUNT(*)
        From dbo.Items AS P
        where p.StartTime Between @StartDate and @EndDate 
        AND P.Official = 1 AND ( P.Result = 7 OR P.Result = 8 OR P.Result = 14)) failedItems

如果您已经将它们设置为变量，当然您不必重复 SELECT COUNT。

SELECT @totalItems AS totalItems, @failedItems AS failedItems

SELECT 语句可以独立存在，无需 FROM 子句。

Answer 3

您不能简单地在过程结束时选择这些变量吗？

SELECT @totalitems AS TotalItems, @faileditems AS FailedItems