如何有效率地聚合 MySQL 中多個表格的多個資料列的資料？

MySQL多表多列資料高效率聚合方法

在豐富結果集的過程中，常常需要整合來自多個表格的數據，每個表格包含感興趣的特定欄位。然而，使用多個SELECT語句來實現這一點可能會很繁瑣且效率低下。

假設我們需要從兩個表中檢索總計列，如下例所示：

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

這種方法通常會遇到問題，即只有第一列顯示正確，而其他列的結果則錯誤。

解決方案是使用單一SELECT語句和條件聚合：

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

在這個修改後的語句中，條件聚合使用IF()函數來評估每一行中特定的PAT_STATUS模式，對匹配的模式遞增計數。然後，SUM()函數聚合這些計數，產生所需的結果集。

以上是如何有效率地聚合 MySQL 中多個表格的多個資料列的資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！