如何为此用例编写SQL查询？

Suppose I've got a table with three fields: event_name, event_timestamp event_result. Each row is an occurrence of an event event_name occurred at event_timestamp with some event_result. For example:

假设我有一个包含三个字段的表：event_name，event_timestamp event_result。每一行都是在event_timestamp中发生的事件event_name，其中包含一些event_result。例如：

 event_name | event_timestamp  | event_result
 -------------------------------------------
 event_a    | 2018-04-29 00:00 | result_x1
 event_a    | 2018-04-29 00:00 | result_x2
 event_b    | 2018-04-29 00:00 | result_x1
 event_a    | 2018-04-29 00:00 | result_x3
 event_b    | 2018-04-29 00:00 | result_x3

Now I need a SQL query to select rows with the latest event_timestamp for every event_name within given range of event_timestamp.

现在我需要一个SQL查询来为event_timestamp的给定范围内的每个event_name选择具有最新event_timestamp的行。

How would you write such a query ? I guess it should be standard SQL that works with any reasonable SQL database.

你会怎么写这样的查询？我想它应该是适用于任何合理的SQL数据库的标准SQL。

6 个解决方案

#1

Use subquery :

使用子查询：

select * 
from table t
where event_timestamp = (select max(event_timestamp) 
                         from table 
                         where event_name = t.event_name);

And, in general the most effective method is to use ranking functions (if support)

而且，一般来说最有效的方法是使用排名函数（如果支持）

select * from (
      select *,
             row_number() over (partition by event_name order by event_timestamp desc) Seq 
      from table t
      where  event_timestamp between @start and @end
) t
where Seq = 1;

#2

The most portable solution would probably be something like:

最便携的解决方案可能是这样的：

SELECT    E.*
FROM      Event E
INNER JOIN (
             SELECT  event_name 
             ,       MAX(event_timestamp) MAX_DT
             FROM    Event 
             WHERE   event_timestamp <= @SomeDate
                  AND event_timestamp > @SomeOtherDate
             GROUP BY event_name 
           ) MAXED
        ON MAXED.event_name = E.event_name 
        AND MAXED.MAX_DT = E.event_timestamp

#3

Something like:

就像是：

SELECT event_name
    ,MAX(event_timestamp)
FROM myTable
WHERE event_timestamp BETWEEN date1
        AND date2
GROUP BY event_name

#4

you can use group by and include the max event_timestamp In MSSQL:

你可以使用group by并在MSSQL中包含max event_timestamp：

select
event_name
, MAX(event_timestamp)
from [table_name]
where event_timestamp between '2018-04-20' and '2018-04-30'
group by event_name

#5

SELECT * FROM `your_table` 
WHERE `event_timestamp` BETWEEN 'your_start_range'AND 'your_end_range'
GROUP BY `event_name`
ORDER BY `event_timestamp` DESC
LIMIT 1;

#6

this code work for ms sql

这段代码适用于ms sql

SELECT *
FROM
    (SELECT
        *, ROW_NUMBER() OVER(PARTITION BY event_name ORDER BY event_timestamp DESC) AS row_no
     FROM    Event 
     WHERE
        event_timestamp BETWEEN @BeginDate AND @EndDate) as tbl
WHERE row_no = 1

#1