使用不同的標准在同一列上選擇兩個聚合函式 - 性能

親愛的堆棧交換使用者，這裡的第一個問題。

有問題的數據庫是使用者租用服務的服務數據庫。現在我想向被雇用的人展示幾張桌子上完成的、未完成的、賺取的總金額、待處理的總金額的概覽

我有點擔心這是否是編寫此查詢的最佳方式。看起來我應該能夠縮短它，因為我在同一張桌子上選擇，不應該以某種方式重用它嗎？

           SELECT finished, notfinished, totalamount, areas, pendingamount
           FROM 
           (        
           SELECT COUNT(finished) as finished, COALESCE(SUM(cost),0) as totalamount FROM HiresService as hs
           JOIN ServiceProvider USING(serviceproviderid)
           JOIN BusinessUser USING(businessuserid)
           WHERE BusinessUser.businessuserid = ? AND hs.finished = true
           ) t1
           JOIN 
           (
           SELECT COUNT(finished) as notfinished, COALESCE(SUM(cost),0) as pendingamount  
           FROM HiresService as hs2
           JOIN ServiceProvider USING(serviceproviderid)
           JOIN BusinessUser USING(businessuserid)
           WHERE BusinessUser.businessuserid = ? AND hs2.finished = false
           ) t2
           JOIN
           (
           SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
           FROM ServicesArea 
           JOIN Area USING(areaid) 
           WHERE serviceproviderid IN(
                SELECT serviceproviderid 
                FROM ServiceProvider 
                JOIN BusinessUser USING(businessuserid) 
                WHERE businessuserid = ?)
           ) t3

表結構如下所示：

編輯：（接受的答案）

SELECT finished, notfinished, totalamount, areas, pendingamount  
FROM ( SELECT  
sum(case when finished = 1 THEN 1 ELSE 0 END) as finished, 
sum(case when finished = 1 THEN cost ELSE 0 END) as totalamount, 
sum(case when finished = 0 THEN 1 ELSE 0 END) as notfinished,
sum(case when finished = 0 THEN cost ELSE 0 END) as pendingamount    
FROM HiresService
JOIN ServiceProvider USING(serviceproviderid)
JOIN BusinessUser USING(businessuserid)
WHERE BusinessUser.businessuserid = ?
) t1
JOIN            
(
SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
FROM ServicesArea 
JOIN Area USING(areaid) 
WHERE serviceproviderid IN(
SELECT serviceproviderid 
FROM ServiceProvider 
JOIN BusinessUser USING(businessuserid) 
WHERE businessuserid = ?)
) t2

您可以使用一個技巧來過濾聚合：

select
--...
, sum(case when CONDITION THEN 1 ELSE 0 END) AS FilteredCount
, sum(case when CONDITION THEN SomeCol ELSE 0 END) AS FilteredSumOfSomeCol

如果您可以將聚合適合此方案，則可以避免像目前程式碼中那樣重複查詢部分。看起來你可以使用這個技巧。

引用自：https://dba.stackexchange.com/questions/82823