如何在SQL中有效率地查詢不同表格的多列？

最佳化多表多列SQL查詢

從多個表中檢索資料通常會帶來挑戰，特別是在需要聚合各個列中的值時。最近的一個支援案例強調了使用者在準確計算兩個表中多個列的值時遇到的困難。他們最初嘗試使用巢狀子查詢，但產生了錯誤的結果。

有缺陷的查詢如下所示：

<code class="language-sql">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>

此修改後的查詢可有效檢索必要的數據，消除與原始過於複雜的結構相關的錯誤。 關鍵是使用條件 SUM() 語句將計算合併為一個結構良好的查詢。

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