SQL查询根据给定多条记录的不同最大值获取最大值 [英] SQL Query to Get Max Value Based on Different Max Value Given Multiple Records
问题描述
我知道我可以用 CTE 或其他方式来做到这一点,但我想知道这是否可以在一个选择查询中完成(没有子选择).我想为 po_nbr 找到最新的 crt_ts,然后获取关联的 id.
I know I can do this with a CTE or another means but I am wondering if this can be done in one select query (no sub-selects). I want to find the most recent crt_ts for the po_nbr and then take the associated id.
id 列不能保证是连续的,我为这个问题简化了它.
The id column is not guaranteed to be sequential, I simplified it for this question.
create table temp
(
id int,
po_nbr int,
crt_ts datetime
)
insert into temp values (20, 100, '09/01/2009')
insert into temp values (3, 100, '09/03/2009')
insert into temp values (5, 100, '09/05/2009')
insert into temp values (6, 100, '09/07/2009')
insert into temp values (4, 200, '08/01/2009')
insert into temp values (29, 200, '08/03/2009')
insert into temp values (12, 200, '08/05/2009')
insert into temp values (18, 200, '08/07/2009')
预期结果:
id po_nbr
---------
6 100
18 200
推荐答案
SELECT
MAX(id) id,
po_nbr
FROM
temp
GROUP BY
po_nbr
要获得关联的日期,您可以这样做(请注意,这意味着顺序 ID):
To have the associated date, you could do (beware, this implies a sequential id):
SELECT
temp.id,
temp.po_nbr,
temp.crt_ts
FROM
temp
INNER JOIN (
SELECT MAX(id) id FROM temp GROUP BY po_nbr
) latest ON latest.id = temp.id
如果没有连续的 id,那就是:
Without a sequential id, it would be:
SELECT
MAX(temp.id) id,
temp.po_nbr,
temp.crt_ts
FROM
temp INNER JOIN (
SELECT MAX(crt_ts) crt_ts, po_nbr
FROM temp i
GROUP BY po_nbr
) latest ON latest.crt_ts = temp.crt_ts AND latest.po_nbr = temp.po_nbr
GROUP BY
temp.po_nbr,
temp.crt_ts
如果保证每个 po_nbr
组没有两个相等的日期,则可以省略 GROUP BY
.
The GROUP BY
can be left out if there are guaranteed to be no two equal dates per po_nbr
group.
crt_ts
和 po_nbr
上的索引有助于最后一个查询,最好创建一个组合索引.
Indexes on crt_ts
and po_nbr
help in the last query, creating one combined index would be best.
这篇关于SQL查询根据给定多条记录的不同最大值获取最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!