SQL为一个日期选择多个最大值
提问于 2019-07-01 20:37:38
我试图通过使用max筛选列来从表中获取最新的记录，但是看起来我有多条记录对应于相同的最大日期。我在查询的末尾添加了order BY，看起来我实际上可以检索最新的值，因为它具有正确的顺序，但我不知道如何检索。
下面是一张描述性图片：
另外，请在下面找到我使用的查询：
SELECT *
FROM item_forecast_detail
WHERE item_id = 177010 AND
forecast_dt = (SELECT MAX(forecast_dt)
FROM item_forecast_detail
WHERE item_id = 177010)
ORDER BY forecast_dt DESC
回答 4
Stack Overflow用户
发布于 2019-07-01 20:45:25
使用row_number()窗口分析函数按需排序，使用max(forecast_dt) over (order by forecast_dt desc)检测最新日期
SELECT *
FROM
(
SELECT d.*,
row_number() over (order by ending_hour desc) as rn,
max(forecast_dt) over (order by forecast_dt desc) as mx
FROM item_forecast_detail d
WHERE item_id=177010
)
WHERE mx = forecast_dt
ORDER BY rnStack Overflow用户
发布于 2019-07-01 20:52:56
一种选择是应用另一个条件，例如获取最新的starting_hour
SQL> with item_forecast_Detail (item_id, forecast_dt, starting_hour) as
2 (select 177010, date '2019-07-07', 21 from dual union all
3 select 177010, date '2019-07-07', 18 from dual union all
4 select 177010, date '2019-07-07', 15 from dual union all
5 select 177010, date '2019-07-07', 12 from dual union all
6 --
7 select 123456, date '2019-02-17', 09 from dual
8 )
9 select *
10 from item_forecast_Detail i
11 where i.item_id = 177010
12 and i.forecast_dt = (select max(i1.forecast_dt)
13 from item_forecast_detail i1
14 where i1.item_id = i.item_id
15 )
16 and i.starting_hour = (select max(i2.starting_hour)
17 from item_forecast_detail i2
18 where i2.item_id = i.item_id
19 );
ITEM_ID FORECAST_D STARTING_HOUR
---------- ---------- -------------
177010 07.07.2019 21
SQL>另一种方法是使用分析函数对它们进行排序，并将其应用于最终查询：
SQL> with item_forecast_Detail (item_id, forecast_dt, starting_hour) as
2 (select 177010, date '2019-07-07', 21 from dual union all
3 select 177010, date '2019-07-07', 18 from dual union all
4 select 177010, date '2019-07-07', 15 from dual union all
5 select 177010, date '2019-07-07', 12 from dual union all
6 --
7 select 123456, date '2019-02-17', 09 from dual
8 ),
9 sort as
10 (select i.*,
11 row_number() over (partition by item_id order by forecast_dt, starting_hour desc) rn
12 from item_forecast_Detail i
13 )
14 select *
15 from sort s
16 where s.item_id = 177010
17 and s.rn = 1;
ITEM_ID FORECAST_D STARTING_HOUR RN
---------- ---------- ------------- ----------
177010 07.07.2019 21 1
SQL>Stack Overflow用户
发布于 2019-07-01 21:29:38
您可以尝试使用MySQL、SQLServer：
SELECT * FROM TABLE ORDER BY FORECAST_DT DESC LIMIT 1
SELECT TOP 1 * FROM Table ORDER BY FORECAST_DT DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56842155
复制
腾讯云开发者
