Sql从记录中获取返回MAX值的其他字段 [英] Sql get other fields from record that returned MAX value

问题描述

SQL不是我的第一语言！



我想使用MAX函数找到一行但返回该行的其他列。

不确定这是否可能？



假设我们有一张简单的桌子：



账户，发票，日期时间，价值



我想获得每个账户的最新日期时间以及与该行相关的值。假设没有索引，发票不保证按任何特定顺序排列。



所以我可以说：

SQL is not my first language!

I want to locate a row using the MAX function but return other columns from that row.
Not sure if this is even possible?

Lets say we have a simple table:

Account, Invoice, DateTime, Value

I want to get the latest DateTime for each Account and the value associated with that row. Lets pretend there are no indexes and the Invoice is not guaranteed to be in any particular order.

So I can say:

SELECT Account, MAX(DateTime) AS Maxdate FROM Table GROUP BY Account

所以非常好，但我也想从返回最新DateTime的行返回值列，但我该怎么做？

我知道我可以内部加入同一张桌子，但桌子很大而且非常慢。



这不是我正在使用的数据，但我想用查询返回的内容的插图。



这不是我的数据库所以我不能自由添加任何索引......！



非常感谢您的期待。



我的尝试：



我试图在子查询中选择具有MAX函数的记录，但这很慢。

我也尝试将表连接到自身，但这又是非常慢的。

So far so good, but I also want to return the value column from the row that returned the latest DateTime but how can I do that?
I know I can inner join back to the same table, but the table is huge and that is very slow.

This is not exactly the data I am using but an illustration of what I want to return with the query.

It is not my database so I am not at liberty to add any indexes...!

Many thanks in anticipation.

What I have tried:

I have tried to select the records with the MAX function in a subquery but this is very slow.
I also tried inner joining the table to itself but again this is very slow.

推荐答案

您可以在线试用： SQL Fiddle [ ^ ]

You can try this out online at: SQL Fiddle[^]

CREATE TABLE ForgeRock
    ([productName] varchar(13), 
     [description] varchar(57),
     [price] int)
;
    
INSERT INTO ForgeRock
    ([productName], [description], [price])
VALUES
    ('OpenIDM', 'Platform for building enterprise provisioning solutions', 1),
    ('OpenAM', 'Full-featured access management', 2),
    ('OpenDJ', 'Robust LDAP server for Java', 3)
;


SELECT a.productName, a.description, a.price FROM ForgeRock a
	JOIN (SELECT MAX(t.price) AS max_subkey
	FROM ForgeRock t) b 
    ON b.max_subkey = a.price

- 救生临时表我的朋友

--Temporary tables to the rescue my friend

create table maxdate(Account varchar(255),thedate datetime,thevalue int)

insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-25',4)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-26',2)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-27',1)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-28',8)
insert into maxdate(Account,thedate,thevalue) values('joe bloggs','2017-06-29',16)

insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-25',16)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-26',8)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-27',4)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-28',2)
insert into maxdate(Account,thedate,thevalue) values('Superman','2017-05-29',1)

insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-25',1)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-26',2)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-27',3)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-28',4)
insert into maxdate(Account,thedate,thevalue) values('Batman','2017-05-29',5)

if OBJECT_ID('tempdb..#accountlatestdate') is not null
begin
	drop table #accountlatestdate 
end 

SELECT Account,MAX(thedate) AS Maxdate
into #accountlatestdate
FROM maxdate
group by Account

select b.* 
from #accountlatestdate a 
join maxdate b on a.Account = b.Account and a.Maxdate = b.thedate

结果

Results

joe bloggs	2017-06-29 00:00:00.000	16
Superman	2017-05-29 00:00:00.000	1
Batman	2017-05-29 00:00:00.000	5

假设您正在使用Microsoft SQL Server 2008或更高版本， ROW_NUMBER [ ^ ]函数可能会有所帮助：

Assuming you're using Microsoft SQL Server 2008 or later, the ROW_NUMBER[^] function will probably help:

WITH cteOrderedData As
(
    SELECT
        Account,
        DateTime,
        Value,
        ROW_NUMBER() OVER (PARTITION BY Account ORDER BY DateTime DESC) As RN
    FROM
        Table
)
SELECT 
    Account, 
    DateTime AS Maxdate,
    Value
FROM 
    cteOrderedData
WHERE
    RN = 1
;

这篇关于Sql从记录中获取返回MAX值的其他字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！