SQL 服务器 - 复杂 SQL 查询，由 SUM 函数确定的比率的 pos/neg/0 计数

Question

我们计算的是2012年第一季度至第四季度和2013年第一季度至第四季度收入差异的百分比变化。然后我们需要把正的加起来，负的加起来，和不变的加起来。所有数据都在一个表中。这需要完全在SQL。

我已经成功地获得了2012年的结果，然后是2013年的结果，但似乎无法让它们返回同一记录。

SELECT ID_FIELD, sum( Receipts_Total ) 
FROM QR_Dom 
WHERE quarter LIKE '%2012%' 
GROUP BY ID_FIELD;

和

SELECT ID_FIELD, sum( Receipts_Total ) 
FROM QR_Dom 
WHERE quarter LIKE '%2013%' 
GROUP BY ID_FIELD;

最后我需要执行这个计算，然后计算正/负/零，然后除以计算的总数:

(2013 Receipts_Total - 2012 Receipts_Total) / 2012 Receipts_Total

我当前的(硬编码id)查询如下:

SELECT ID_FIELD, (((SELECT SUM(Receipts_Total) 
                    FROM QR_Dom 
                    WHERE quarter LIKE '%2013%' 
                          AND ID_FIELD = 10001 
                    GROUP BY ID_FIELD)
                 - (SELECT SUM(Receipts_Total) 
                    FROM QR_Dom 
                    WHERE quarter LIKE '%2012%' 
                          AND ID_FIELD = 10001 
                    GROUP BY ID_FIELD))
                 / (SELECT SUM(Receipts_Total) 
                    FROM QR_Dom 
                    WHERE quarter LIKE '%2012%' 
                          AND ID_FIELD = 10001 
                    GROUP BY ID_FIELD) ) AS RATIO 
FROM QR_Dom 
WHERE ID_FIELD = 10001 
GROUP BY ID_FIELD

Answer 1

您可以使用case语句来查找确切的年份:

SELECT (CASE WHEN quarter LIKE '%2012%' THEN 2012
             WHEN quarter LIKE '%2013%' then 2013
        END) as yr, ID_FIELD, sum( Receipts_Total )
FROM QR_Dom
WHERE quarter LIKE '%2012%' or quarter LIKE '%2013%'
GROUP BY (CASE WHEN quarter LIKE '%2012%' THEN 2012
               WHEN quarter LIKE '%2013%' then 2013
          END), ID_FIELD;

这应该在MySQL和SQL Server中工作。

编辑:

如果你需要区别，你可以用条件聚合和算术来实现:

SELECT ID_FIELD,
       (SUM(CASE WHEN quarter LIKE '%2013%' THEN Receipts_Total ELSE - Receipts_TOTAL
            END) / 
        SUM(CASE WHEN quarter LIKE '%2012%' THEN Receipts_Total
            END)
       )
FROM QR_Dom
WHERE quarter LIKE '%2012%' or quarter LIKE '%2013%'
GROUP BY ID_FIELD;