英文:
Need to Find minimum value of a column that is greater than a certain value
问题
我有一个名为Event的表，
需要找到大于5状态时的最大值的Entity的最小值。如果没有大于的值，则返回状态为5时的最大值。
例如，此表的输出应该是，
是否可以使用查询实现这一目标？获得这个输出的最佳方式是什么？
英文:
I have a table named Event,
I need to find the minimum Value of an Entity that is greater than the maximum Value when the Status is 5. If no Value is greater, then return the maximum Value when Status is 5.
For ex, the output of this table should be,
Can we achieve this using query? What is the best possible way to get this output?
答案1
得分: 1
以下是您要翻译的内容：
也许像这样的代码应该适用于大多数关系数据库系统：
选择 *
从
(
选择 *
,MAX(CASE WHEN Status <> 5 THEN Value END) OVER (PARTITION BY Entity) AS not5statusMax
,MAX(CASE WHEN Status = 5 THEN Value END) OVER (PARTITION BY Entity) AS 5statusMax
从 Event
) DS
其中（statusMax IS NULL AND Value = not5statusMax）
或（Value = statusMax）
英文:
Maybe something like this should work on the most of the RDBS:
SELECT *
FROM
(
SELECT *
,MAX(CASE WHEN Status <> 5 THEN Value END) OVER (PARTITION BY Entity) AS not5statusMax
,MAX(CASE WHEN Status = 5 THEN Value END) OVER (PARTITION BY Entity) AS 5statusMax
FROM Event
) DS
WHERE (statusMax IS NULL AND Value = not5statusMax)
OR (Value = statusMax)
答案2
得分: 1
你可以使用最大窗口函数，然后按如下方式聚合：
select Entity, min(Value) Value
from
(
select *,
max(case when Status = 5 then Value end) over (partition by Entity) status_five_value,
max(Value) over (partition by Entity) mx
from tbl
) T
where Value > status_five_value -- 当值大于（Status等于5的值）时
or
(status_five_value = mx and Status = 5) -- 当没有值大于（Status等于5的值）时
group by Entity
order by Entity
这里的逻辑是，对于每个实体，找出大于status_five_value的值中的最小值，如果对于某个实体，此条件未返回任何行，则status_five_value等于整个实体的max(Value)，在这种情况下，仅返回Status = 5的行。
英文:
You could use the max window function then aggregate as the following:
select Entity, min(Value) Value
from
(
select *,
max(case when Status = 5 then Value end) over (partition by Entity) status_five_value,
max(Value) over (partition by Entity) mx
from tbl
) T
where value > status_five_value -- when the values are greater than the (value where status = 5)
or
(status_five_value = mx and Status = 5) -- when there are no values greater than the (value where status = 5)
group by Entity
order by Entity
The logic here is, for each entity find the minimum value from the values that are greater than status_five_value, if no rows are returned by this condition for an entity, then the status_five_value is equal to the max(Value) for the entire entity, for this case, return only the row with Status = 5.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论