求和SQL优化

SQL复杂查询案例

最新推荐文章于 2024-07-22 18:36:40 发布

最新推荐文章于 2024-07-22 18:36:40 发布 · 305 阅读

文章标签：

#SQL

本文介绍了一个复杂的SQL查询案例，该查询使用了多个子查询、CASE WHEN表达式以及窗口函数进行数据处理。主要目的是根据不同的条件计算目标金额，并通过窗口函数进行累计求和，确保结果的准确性。

SELECT id
,currencyCode
,(CASE WHEN total<=3 THEN amount ELSE (3 - last_total)/conversion_rate END) AS target_amount
,(CASE WHEN total<=3 THEN target_amount ELSE 3 - last_total END) AS target_amount
FROM (
SELECT t.*
,t.amount*r.conversion_rate as target_amount
,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) as total
,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) - t.amount*r.conversion_rate as last_total
,r.conversion_rate
FROM t_money t, t_rates r
WHERE t.currencyCode = r.from_currency
AND r.to_currency = 'usd'
)
WHERE total<=3
OR total>3
AND last_total<3
ORDER BY total;

--------------
SELECT id,
currencyCode,
(CASE
WHEN total <= 3 THEN
amount
ELSE
(3 - last_total)/nvl((select nvl(conversion_rate, 1)
from t_rates
where from_currency = currencycode
and upper(to_currency) = 'USD'),
1)
END)amount,
(CASE
WHEN total <= 3 THEN
target_amount
ELSE
3 - last_total
END) AS target_amount
FROM (SELECT t.*,
t.amount * r.conversion_rate as target_amount,
SUM(t.amount * r.conversion_rate) OVER(ORDER BY t.amount * r.conversion_rate, id) as total,
SUM(t.amount * r.conversion_rate) OVER(ORDER BY t.amount * r.conversion_rate, id) - t.amount * r.conversion_rate as last_total
FROM t_money t, t_rates r
WHERE t.currencyCode = r.from_currency
AND r.to_currency = 'usd')
WHERE total <= 3
OR total > 3
AND last_total < 3
ORDER BY total;