如何有效聚合約一個 MySQL 表中不同列的多個計數？

MySQL：有效聚合單一表格中多個欄位的計數

針對 MySQL 中同一個資料表但不同欄位的多個查詢可能會導致資料檢索效率低下和結果不準確。 讓我們用一個例子來說明這一點並展示正確的方法。

假設您需要根據t_hospital表中的各種條件計算計數：

低效率方法（多次查詢）：

以下程式碼嘗試使用多個查詢來實現此目的，但它會為第一個查詢之外的列產生錯誤的結果：

<code class="language-sql">SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;

SELECT* from
   ( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )e,
     (SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE PAT_STATUS  like '%ong%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN ) a,
        (SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE PAT_STATUS  like '%rtde%'and  DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )b,
           (SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE PAT_STATUS  like '%pol%'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN   )c,
              (SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE PAT_STATUS  like '%para%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )d;</code>

高效方法（帶條件聚合的單一查詢）：

最佳解決方案涉及使用條件聚合的單一查詢：

<code class="language-sql">SELECT DAY_IN, COUNT(*) AS arr,
        SUM(IF(PAT_STATUS  like '%ong%', 1, 0)) AS ONG1,
        SUM(IF(PAT_STATUS  like '%rtde%', 1, 0)) AS RTED,
        SUM(IF(PAT_STATUS  like '%pol%', 1, 0)) AS POL1,
        SUM(IF(PAT_STATUS  like '%para%', 1, 0)) AS para
FROM t_hospital 
WHERE DAY_IN between @start_check and @finish_check 
    and RES_DATE between @start_res and @finish_res  
    and ID_daily_hos =@daily_hos
GROUP BY DAY_IN;</code>

此方法利用 IF() 函數根據 PAT_STATUS 值有條件地計算出現次數，並且 SUM() 聚合每個 DAY_IN 的這些計數。該方法顯著提高了效率並提供了準確的結果。 它避免了透過連接多個子查詢隱式創建的笛卡爾積。

以上是如何有效聚合約一個 MySQL 表中不同列的多個計數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！