是否可以用CTE替换用作标量值的子查询？
是否可以用CTE替换用作标量值的子查询？
提问于 2019-05-23 01:41:51
我想用短别名替换返回标量值或不存在的长子查询，因为我将它放在UPDATE语句中3次。子查询采用UncoveredLoss中的最后一个值(如果有)，并根据最后一个UncoveredLoss值在更新后的行中计算新的UncoveredLoss值。
它是一个不相关的查询，但在SELECT子句中使用，而不是在FROM子句中使用。也许我应该以某种方式修改触发器中的UPDATE语句。
工作代码：
CREATE TRIGGER Result
UPDATE OF Win ON Log
BEGIN
UPDATE Log
SET Profit = CASE
WHEN NEW.Win = 0
THEN - Stake
WHEN NEW.Win = 1
THEN Rate * Stake / 100
WHEN NEW.Win = 2
THEN 0
END
WHERE ID = OLD.ID;
UPDATE Log
SET SumProfit = (
SELECT Sum(Profit)
FROM (
SELECT StrategyAccountID
,Profit
FROM Log
WHERE DATE <= NEW.DATE
)
GROUP BY StrategyAccountID
HAVING StrategyAccountID = NEW.StrategyAccountID
)
WHERE ID = NEW.ID;
UPDATE Log
SET UncoveredLoss = CASE
WHEN EXISTS (
SELECT UncoveredLoss
FROM Log
WHERE DATE < NEW.DATE
AND StrategyAccountID = NEW.StrategyAccountID
ORDER BY DATE DESC LIMIT 1
)
AND (
SELECT UncoveredLoss
FROM Log
WHERE DATE < NEW.DATE
AND StrategyAccountID = NEW.StrategyAccountID
ORDER BY DATE DESC LIMIT 1
) + NEW.Profit < 0
THEN (
SELECT UncoveredLoss
FROM Log
WHERE DATE < NEW.DATE
AND StrategyAccountID = NEW.StrategyAccountID
ORDER BY DATE DESC LIMIT 1
) + NEW.Profit
WHEN NOT EXISTS (
SELECT UncoveredLoss
FROM Log
WHERE DATE < NEW.DATE
AND StrategyAccountID = NEW.StrategyAccountID
ORDER BY DATE DESC LIMIT 1
)
AND NEW.Profit < 0
THEN NEW.Profit
ELSE 0
END
WHERE ID = NEW.ID;
END;使用CTE替换简单的子查询不起作用：
CREATE TRIGGER Result
UPDATE OF Win ON Log
BEGIN
UPDATE Log
SET Profit = CASE
WHEN NEW.Win = 0
THEN - Stake
WHEN NEW.Win = 1
THEN Rate * Stake / 100
WHEN NEW.Win = 2
THEN 0
END
WHERE ID = OLD.ID;
UPDATE Log
SET SumProfit = (
SELECT Sum(Profit)
FROM (
SELECT StrategyAccountID
,Profit
FROM Log
WHERE DATE <= NEW.DATE
)
GROUP BY StrategyAccountID
HAVING StrategyAccountID = NEW.StrategyAccountID
)
WHERE ID = NEW.ID;
WITH Loss
AS (
SELECT UncoveredLoss
FROM Log
WHERE DATE < NEW.DATE
AND StrategyAccountID = NEW.StrategyAccountID
ORDER BY DATE DESC LIMIT 1
)
UPDATE Log
SET UncoveredLoss = CASE
WHEN EXISTS (Loss)
AND (Loss) + NEW.Profit < 0
THEN (Loss) + NEW.Profit
WHEN NOT EXISTS (Loss)
AND NEW.Profit < 0
THEN NEW.Profit
ELSE 0
END
WHERE ID = NEW.ID;
END;错误:接近"UPDATE"：语法错误
当我不替换子查询时，它工作得很好，但是当我尝试使用CTE时，它失败了。我在Emacs的sql.el模式下工作。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-05-23 06:18:24
是啊，那是可以清理的。考虑一下这样的情况：
CREATE TRIGGER Result UPDATE OF Win ON Log BEGIN
UPDATE Log
SET SumProfit = (SELECT sum(Profit)
FROM Log
WHERE Date <= NEW.Date AND StrategyAccountID = NEW.StrategyAccountID)
, UncoveredLoss = ifnull((SELECT min(UncoveredLoss + NEW.Profit, 0)
FROM Log
WHERE Date < NEW.Date AND StrategyAccountID = NEW.StrategyAccountID
ORDER BY Date DESC
LIMIT 1), 0)
WHERE ID = NEW.ID;
END;我敢肯定和你的计算结果是一样的。(实际的示例表定义和要处理的数据会更好)
还要注意，在最近的Sqlite3版本(3.25和更高版本)中，可以轻松地按需计算SumProfit列，而不是占用表中的空间：
SELECT *
, sum(profit) OVER (PARTITION BY StrategyAccountID ORDER BY Date) AS SumProfit
FROM Log;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56262261
复制相关文章
相似问题