作者 |?不剪發的Tony老師???責編?| 晉兆雨? ? ?
出品 | CSDN博客
一般來說,SQL 子查詢只能引用外查詢中的字段,而不能使用同一層級中其他表中的字段。例如:
-- 錯誤示例
SELECT d.dept_name,t.avg_salary
FROM department d
JOIN (SELECT avg(e.salary) AS avg_salaryFROM employee eWHERE e.dept_id = d.dept_id) t;
SQL 錯誤 [42601]: ERROR: syntax error at end of input位置:183
jpa模糊查詢,由于 JOIN 子句中的查詢語句 t 引用了左側 department 表中的字段,因此產生了語法錯誤。
為了解決以上問題,我們可以使用 PostgreSQL 提供的橫向子查詢(LATERAL subquery)。不過在介紹 LATERAL 關鍵字之前,我們先來回顧一下 SELECT 和 FROM 子句的含義。例如:
SELECT dept_id, dept_name
FROM department;
簡單來說,我們可以將以上查詢看作一個循環處理語句。使用偽代碼實現的以上 SQL 語句如下:
for dept_id, dept_name in department
loopprint dept_id, dept_name
end loop
對于 department 中的每一條記錄,都執行 SELECT 語句指定的操作,以上示例簡單的輸出了每行記錄。
SELECT 就像一個循環語句,而 LATERAL 就像是一個嵌套循環語句,對于左側表中的每行記錄執行一次子查詢操作。例如,通過增加 LATERAL 關鍵字,我們可以修改第一個示例:
SELECT d.dept_name,t.avg_salary
FROM department d
CROSS JOIN LATERAL(SELECT avg(e.salary) AS avg_salaryFROM employee eWHERE e.dept_id = d.dept_id) t;dept_name |avg_salary |
-----------+----------------------+
行政管理部 | 26666.666666666667|
人力資源部 |13166.6666666666666667|
財務部 | 9000.0000000000000000|
研發部 | 7577.7777777777777778|
銷售部 | 5012.5000000000000000|
保衛部 | |
CROSS JOIN LATERAL 右側的查詢可以引用左側表中的字段,以上語句為 JOIN 左側的每個部門返回了月薪總和。
oracle兩個查詢結果橫向拼接、LATERAL 可以幫助我們實現一些有用的分析功能,例如以下查詢返回了每個部門月薪最高的 3 名員工:
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL(SELECT emp_name, salaryFROM employee eWHERE e.dept_id = d.dept_idORDER BY salary DESCLIMIT 3) t
ON TRUE;dept_name |emp_name|salary |
-----------+--------+--------+
行政管理部 |劉備 |30000.00|
行政管理部 |關羽 |26000.00|
行政管理部 |張飛 |24000.00|
人力資源部 |諸葛亮 |24000.00|
人力資源部 |黃忠 | 8000.00|
人力資源部 |魏延 | 7500.00|
財務部 |孫尚香 |12000.00|
財務部 |孫丫鬟 | 6000.00|
研發部 |趙云 |15000.00|
研發部 |周倉 | 8000.00|
研發部 |關興 | 7000.00|
銷售部 |法正 |10000.00|
銷售部 |簡雍 | 4800.00|
銷售部 |孫乾 | 4700.00|
保衛部 | | |
對于 department 中的每個部門,子查詢 t 最多返回 3 個員工信息。我們使用了 LEFT JOIN LATERAL,從而保證了“保衛部”也會返回一條數據。
同樣使用偽代碼表示以上查詢語句:
for d in department
loopfor e in employee order by salary descloopcnt++if cnt <= 3thenreturn eelsegoto next dendend loop
end loop
通過 EXPLIAN 命令查看以上語句的執行計劃:
EXPLAIN
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL(SELECT emp_name, salaryFROM employee eWHERE e.dept_id = d.dept_idORDER BY salary DESCLIMIT 3) t
ON TRUE;QUERY PLAN |
-------------------------------------------------------------------------------------------------+
Nested Loop Left Join (cost=8.17..4439.35 rows=540 width=250) |-> Seq Scan on department d (cost=0.00..15.40 rows=540 width=122) |-> Limit (cost=8.17..8.17 rows=1 width=132) |-> Sort (cost=8.17..8.17 rows=1 width=132) |Sort Key: e.salary DESC |-> Index Scan using idx_emp_dept on employee e (cost=0.14..8.16 rows=1 width=132)|Index Cond: (dept_id = d.dept_id) |
Nested Loop Left Join 說明 PostgreSQL 使用的就是嵌套循環算法。
版權聲明:本文為CSDN博主「不剪發的Tony老師」的原創文章。
sql將查詢結果橫轉縱。原文鏈接:https://blog.csdn.net/horses/article/details/118769805
?賈躍亭回應是否有回國打算:那必須的;順豐將拿出 2 億元給快遞員加薪;OpenAI 宣布解散機器人團隊|極客頭條?“為了拿下 Offer,我在技術面試時迎合面試官,給了錯誤答案!”?Windows、Linux 紛紛被爆漏洞,黑客可直取 root 權限!
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态