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

高效匯總 MySQL 中多列資料

使用關聯式資料庫通常需要聚合分佈在不同表格中多個欄位的資料。 簡單地連接多個查詢可能會導致結果不準確。 此範例示範了一個常見問題及其使用 MySQL 的有效解決方案。

考慮這個初始 MySQL 查詢，旨在匯總 t_hospital 表中的患者資料：

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;

問題：只有arr（算上DAY_IN）會產生正確的結果。 由於單獨查詢的連接不正確，ONG1、RTED、POLI 和 para（計算不同 PAT_STATUS 值的出現次數）不準確。

解決方案：在單一查詢中使用 MySQL 的聚合函數（SUM 和 IF）提供了更有效率、更準確的解決方案：

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;

此修訂後的查詢正確聚合每個 DAY_IN 的數據，在單一高效查詢中為每個 PAT_STATUS 類別提供準確的計數。 這種方法避免了笛卡爾積並導致原始查詢不準確。

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