作者 | 不剪发的Tony老师 责编 | 晋兆雨
SELECT d.dept_name,
t.avg_salary
FROM department d
JOIN (SELECT avg(e.salary) AS avg_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;
SQL 错误 [42601]: ERROR: syntax error at end of input
位置:183
SELECT dept_id, dept_name
FROM department;
for dept_id, dept_name in department
loop
print dept_id, dept_name
end loop
SELECT d.dept_name,
t.avg_salary
FROM department d
CROSS JOIN LATERAL
(SELECT avg(e.salary) AS avg_salary
FROM employee e
WHERE e.dept_id = d.dept_id) t;
dept_name |avg_salary |
-----------+----------------------+
行政管理部 | 26666.666666666667|
人力资源部 |13166.6666666666666667|
财务部 | 9000.0000000000000000|
研发部 | 7577.7777777777777778|
销售部 | 5012.5000000000000000|
保卫部 | |
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL
(SELECT emp_name, salary
FROM employee e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 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|
保卫部 | | |
for d in department
loop
for e in employee order by salary desc
loop
cnt++
if cnt <= 3
then
return e
else
goto next d
end
end loop
end loop
EXPLAIN
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL
(SELECT emp_name, salary
FROM employee e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 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) |
☞贾跃亭回应是否有回国打算:那必须的;顺丰将拿出 2 亿元给快递员加薪;OpenAI 宣布解散机器人团队|极客头条
☞“为了拿下 Offer,我在技术面试时迎合面试官,给了错误答案!”
☞Windows、Linux 纷纷被爆漏洞,黑客可直取 root 权限!
文章评论