sql-server - 对派生表进行多次计数

我正在尝试使用派生表计算入围名单和感兴趣的总数，但它不起作用。

select  (SELECT COUNT(ID) FROM Dtable WHERE Is_shortlisted=1) AS TOTALSHORLISTED,
        (SELECT COUNT(ID) FROM Dtable WHERE Is_Interested=1) AS TOTALINERESTED
from (
        SELECT  BM.ID,
                BM.Is_Interested,
                BM.Is_shortlisted,
                BM.Business_Masla_Status_ID
        FROM  Business_Maslahal BM
        INNER join Vw_MaslaInfo MI
            on BM.[MaslaHal_ID]=MI.ID and BM.ID=2 AND MI.Masla_status_ID IN(1,2)
) Dtable

最佳答案

实际上，您甚至不需要派生表:

SELECT  SUM(CASE WHEN Is_shortlisted=1 THEN 1 ELSE 0 END) AS TOTALSHORLISTED,
        SUM(CASE WHEN Is_Interested=1 THEN 1 ELSE 0 END) AS TOTALINERESTED
FROM  Business_Maslahal BM
INNER join Vw_MaslaInfo MI
ON BM.[MaslaHal_ID]=MI.ID
WHERE and BM.ID=2
AND MI.Masla_status_ID IN(1,2)

我还将一些条件从 ON 子句移到了 WHERE 子句。它应该对结果没有影响，但它使查询更具可读性。

关于sql-server - 对派生表进行多次计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37811269/

bm

sql-server - 对派生表进行多次计数