如何将一个语句用于许多不同的事物

Question

我想对统计数据执行以下操作，通常情况下，这将是一次提交多个语句。有没有办法把这些变成一个陈述？

此表将按分钟提供API调用的统计数据。

-------------------------
Statistic
-------------------------
appName varchar(256) PK
timeMinuteMS bigint PK
totalElapsedMs bigint
totalCount bigInt
minElapsedMs bigInt
maxElapsedMs bigInt

我要运行的语句是

UPDATE Statistic SET minElapsedMS = ? WHERE(appName = ? AND timeMinuteMs = ? AND minElapsedMs > ?);
UPDATE Statistic SET maxElapsedMS = ? WHERE(appName = ? AND timeMinuteMs = ? AND maxElapsedMs < ?);
UPDATE Statistic SET totalElapsedMs = (totalElapsedMs + ?), totalCount = (totalCount + 1) WHERE(appName = ? AND timeMinuteMs = ?);

有没有办法把所有3个组合成1？

稍后我将运行性能测试，只需要先使用1语句即可。

Answer 1

您可以在一个update中执行此操作，但它要复杂得多：

UPDATE Statistic
SET minElapsedMS = (CASE WHEN timeMinuteMs = ? AND minElapsedMs > ? THEN ? ELSE minElapsedMS END),
maxElapsedMS = (CASE WHEN timeMinuteMs = ? AND maxElapsedMs < ? THEN ? ELSE maxElapsedMS END),
totalElapsedMs = totalElapsedMs + (CASE WHEN timeMinuteMs = ? THEN 1 ELSE 0 END),
totalCount = totalCount + (CASE WHEN timeMinuteMs = ? THEN 1 ELSE 0 END)
WHERE appName = ? AND
( (timeMinuteMs = ? AND minElapsedMs > ?) OR
(timeMinuteMs = ? AND maxElapsedMs < ?) OR
timeMinuteMs = ?
);

注意，这有更多的参数，所以在传递值时需要小心。

Answer 2

一个选项是保留这三个选项并将它们包装为事务。