gpt4 book ai didi

sql - 嵌套查询性能替代方案

转载 作者:行者123 更新时间:2023-12-04 18:08:52 25 4
gpt4 key购买 nike

来自 性能透视 , 这是编写有关嵌套查询的以下查询的最佳方法吗:

SELECT a.meg,a.currency
FROM alt6sal a
WHERE a.meg_code IN (1,2)
AND a.sal_year = (SELECT MAX(ia.sal_year) FROM alt6sal ia WHERE a.emp_num = ia.emp_num )
AND a.sal_mon = (SELECT MAX(ia.sal_mon) FROM alt6sal ia WHERE a.emp_num = ia.emp_num AND a.sal_year = ia.sal_year)

最佳答案

你可以试试这个——

SELECT meg, currency
FROM
(
SELECT a.meg,a.currency,
dense_rank() over (PARTITION BY a.emp_num ORDER BY a.sal_year desc) year_rank,
dense_rank() over (PARTITION BY a.emp_num ORDER BY a.sal_mon desc) mon_rank
FROM alt6sal a
WHERE a.meg_code IN (1,2)
)
WHERE year_rank = 1
AND mon_rank = 1;

关于sql - 嵌套查询性能替代方案,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19563241/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com