sql - 查询中的多个 COUNT 返回不正确的结果

Question

我正在使用 SQLite，一般来说我对 SQL 还是很陌生。我遇到了一个查询问题，即我的新 COUNT 语句在不应该返回相同的数字时返回。当我分别运行这两个子查询时，我得到了正确的结果，所以我知道我只是将它们混合在一起。任何关于如何减少丑陋的建议也非常感谢!

我正在使用以下非常难看的选择语句:

SELECT BATCH_ID, PROGRAM_ID,
    COUNT((SELECT ID FROM PARTS
             WHERE DROPSHIP_ONLY = 'No'
             AND OBSOLETE = 'No'
             AND DISCONTINUED = 'No'))
             AS 'Active Parts',
    COUNT((SELECT ID FROM PARTS
             WHERE DROPSHIP_ONLY = 'No'
             AND OBSOLETE = 'No'
             AND DISCONTINUED = 'No'
             AND ON_HAND > 0
             AND ON_HAND > SAMPLE_LEVEL))
             AS 'Instock Parts'
             FROM PARTS
             GROUP BY BATCH_ID, PROGRAM_ID;

它返回这个:

BATCH_ID  PROGRAM_ID Active Parts Instock Parts
201311    Vendor1    1390         1390
201311    Vendor2    1433         1433
201311    Vendor3    213          213
201311    Vendor4    3595         3595
201311    Vendor5    98           98
201311    Vendor6    3            3

事件部件的预期结果是:

201311   Vendor1    1144
201311   Vendor2    1380
201311   Vendor3    100
201311   Vendor4    2276
201311   Vendor5    98
201311   Vendor6    3

Instock Parts 的预期结果是:

201311   Vendor1    15
201311   Vendor2    1272
201311   Vendor3    45
201311   Vendor4    1767
201311   Vendor5    97
201311   Vendor6    3

感谢您的任何帮助或建议!

Answer 1

尝试使用 CASE()

SELECT  BATCH_ID,
        PROGRAM_ID,
        SUM(CASE WHEN DROPSHIP_ONLY = 'No' 
                        AND OBSOLETE = 'No' 
                        AND DISCONTINUED = 'No' 
                THEN 1 END) AS 'Active Parts',
        SUM(CASE WHEN DROPSHIP_ONLY = 'No' 
                        AND OBSOLETE = 'No' 
                        AND DISCONTINUED = 'No'
                        AND ON_HAND > 0 
                        AND ON_HAND > SAMPLE_LEVEL 
                THEN 1 END) AS 'Instock Parts'
FROM    PARTS
WHERE   DROPSHIP_ONLY = 'No' 
        AND OBSOLETE = 'No'
        AND 
        (
            (DISCONTINUED = 'No') 
            OR
            (DISCONTINUED = 'No' 
            AND ON_HAND > 0 
            AND ON_HAND > SAMPLE_LEVEL)
        )
GROUP   BY BATCH_ID, PROGRAM_ID