gpt4 book ai didi

SQL 根据值选择唯一值

转载 作者:行者123 更新时间:2023-12-04 05:15:38 25 4
gpt4 key购买 nike

表:

Name | Value | Prize
Race1 32 5
Race1 22 5
Race1 30 5
Race2 11 5
Race2 12 5
Race2 31 5

我想查询什么,当我定义一个 Value 20+, UNIQUE 比赛 (Race1, Race2) 将根据我的结果进行选择,基于 Value更高。奖金应该相加。

所以当我做元查询时: select sum(Prize) biggestValueUniqueRaces where value > -20我得到:

查询返回:
Prize
10

它考虑了这两行:
Name | Value | Prize
Race1 32 5
Race2 31 5

最佳答案

编辑重新评论

SELECT Sum(Races.Prize) As Total
FROM Races INNER JOIN (SELECT r.[Name], Max(r.[Value]) AS MaxOfValue
FROM Races r WHERE [Value]>20
GROUP BY r.[Name]) AS q
ON (Races.Value = q.MaxOfValue) AND (Races.Name = q.Name);

或者
SELECT SUM(races.prize) AS SP
FROM races
WHERE races.id IN (
SELECT id
FROM races r
WHERE r.name = races.name
AND VALUE = (
SELECT Max(VALUE)
FROM races s
WHERE s.[name] = r.name
AND VALUE > 20 ))

关于SQL 根据值选择唯一值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14306401/

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