sql - SQL 中结果行集的总和

Question

我有以下查询:

SELECT DISTINCT CU.permit_id, CU.month, /*CU.year,*/ M.material_id, M.material_name, /*MC.chemical_id, C.chemical_name,
C.precursor_organic_compound, C.non_precursor_organic_compound,*/
/*MC.chemical_percentage,*/
POC_emissions =
        CASE
        WHEN (C.precursor_organic_compound = 'true')
                THEN (CU.chemical_usage_lbs / CU.material_density) * M.VOC
        ELSE 0
        END,
NON_POC_emissions = 
        CASE
        WHEN (C.non_precursor_organic_compound = 'true')
                THEN CU.chemical_usage_lbs * (MC.chemical_percentage / 100)
        ELSE 0
        END
FROM material M
LEFT OUTER JOIN material_chemical MC ON MC.material_id = M.material_id
LEFT OUTER JOIN chemical_usage CU ON CU.material_id = MC.material_id
LEFT OUTER JOIN chemical C ON C.chemical_id = MC.chemical_id
WHERE (CU.month >=1 AND CU.month <= 2)
AND CU.year = 2013
AND M.material_id = 52
--AND CU.permit_id = 2118
--GROUP BY CU.permit_id, M.material_id, M.material_name, CU.month, MC.chemical_id, MC.chemical_id, C.chemical_name, C.precursor_organic_compound, C.non_precursor_organic_compound
--ORDER BY C.chemical_name ASC

哪个返回:

但我需要的是每个月返回一行，将每个月的 POC 值和每个月的 NON_POC 值相加。

所以，我应该以这样的方式结束:

Month    material_id     material_name     POC           NON_POC
1        52              Krylon...         0.107581      0.074108687
2        52              Krylon...         0.143437      0.0988125

我尝试使用 SUM，但它多次汇总相同的结果:

SELECT /*DISTINCT*/ CU.permit_id, CU.month, /*CU.year,*/ M.material_id, M.material_name, /*MC.chemical_id, C.chemical_name,
C.precursor_organic_compound, C.non_precursor_organic_compound,*/
--MC.chemical_percentage,
POC_emissions = SUM( 
        CASE
        WHEN (C.precursor_organic_compound = 'true')
                THEN (CU.chemical_usage_lbs / CU.material_density) * M.VOC
        ELSE 0
        END),
NON_POC_emissions = SUM( 
        CASE
        WHEN (C.non_precursor_organic_compound = 'true')
                THEN CU.chemical_usage_lbs * (MC.chemical_percentage / 100)
        ELSE 0
        END)
FROM material M
LEFT OUTER JOIN material_chemical MC ON MC.material_id = M.material_id
LEFT OUTER JOIN chemical_usage CU ON CU.material_id = MC.material_id
LEFT OUTER JOIN chemical C ON C.chemical_id = MC.chemical_id
WHERE M.material_id = 52
--AND CU.permit_id = 187
AND (CU.month >=1 AND CU.month <= 2)
AND CU.year = 2013
GROUP BY CU.permit_id, M.material_id, M.material_name, CU.month/*, CU.year, MC.chemical_id, C.chemical_name, C.precursor_organic_compound, C.non_precursor_organic_compound*/
--ORDER BY C.chemical_name ASC

Answer 1

第一个查询有一个 DISTINCT 子句。没有 DISTINCT 子句的输出是什么。我怀疑您的行数比屏幕截图中显示的多。

无论如何，您都可以尝试这样的操作以获得所需的结果。

select permit_id, month, material_id, material_name, 
       sum(poc_emissions), sum(non_poc_emissions)
from (

    SELECT DISTINCT CU.permit_id, CU.month, M.material_id, M.material_name, 
    POC_emissions =
        CASE
        WHEN (C.precursor_organic_compound = 'true')
                THEN (CU.chemical_usage_lbs / CU.material_density) * M.VOC
        ELSE 0
        END,
    NON_POC_emissions = 
        CASE
        WHEN (C.non_precursor_organic_compound = 'true')
                THEN CU.chemical_usage_lbs * (MC.chemical_percentage / 100)
        ELSE 0
        END
    FROM material M
    LEFT OUTER JOIN material_chemical MC ON MC.material_id = M.material_id
    LEFT OUTER JOIN chemical_usage CU ON CU.material_id = MC.material_id
    LEFT OUTER JOIN chemical C ON C.chemical_id = MC.chemical_id
    WHERE (CU.month >=1 AND CU.month <= 2)
      AND CU.year = 2013
      AND M.material_id = 52

) main
group by permit_id, month, material_id, material_name

解释

由于您通过 DISTINCT 检索到的结果被认为是真实来源，因此我通过将其作为子查询创建了一个内存表。但是，此子查询必须具有某种名称……任何名称。我给它起了一个名字 main。子查询看起来像这样:

select ... from (sub-query) <give-it-a-table-name>

Simple Example:
select * from (select userid, username from user) user_temp

Advanced Example:
select * from (select userid, username from user) user_temp
inner join (select userid, sum(debits) as totaldebits from debittable) debit
    on debit.userid = user_temp.userid

注意如何使用子查询的 user_temp 别名，就好像子查询是一个真实的表一样。