SQL 选择具有最大和最小日期的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7967010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select rows with max and min date
提问by user798612
I am trying to get 2 rows from a table at one shot. The one with the minimum datetime (today - 7) of last week and the one which is the latest (today).
My table:
我试图一次从一张表中获取 2 行。具有最小日期时间（今天 - 7）的上周和最新的（今天）。
我的表：
|id |dataIn |dataOut|date |MachineId |
-----+-------+-------+-----------------------+-------------------------------------+
|1 |5006 |58 |2011-10-25 09:03:17.000|7B788EE88E-6527-4CB4-AA4D-01B7F4048559
|2 |1200 |130 |2011-10-26 12:45:43.000|7B788EE88E-6527-4CB4-AA4D-01B7F4048559
...
|124 |1350 |480 |2011-10-29 13:29:04.000|7B788EE88E-6527-4CB4-AA4D-01B7F4048559
|125 |8005 |560 |2011-10-31 21:18:35.000|7B788EE88E-6527-4CB4-AA4D-01B7F4048559
I can select the data from last week with:
我可以选择上周的数据：
SELECT
dbo.myDatabase.Date AS [date], dbo.myDatabase.dataIn AS [in],
dbo.myDatabase.dataOut AS [out]
FROM
dbo.myDatabase WHERE
Date >=dateadd(day,datediff(day,0,GetDate())- 7,0)
AND
dbo.myDatabase.MachineId = '7B788EE88E-6527-4CB4-AA4D-01B7F4048559'
but I would only like row 1 AND 125 because those are the rows used for my calculations.
So my question is:
How do I select the 2 rows (with MIN and MAX date) from within the results of the previous query?
但我只想要第 1 行和第 125 行，因为这些是用于我的计算的行。所以我的问题是：
如何从上一个查询的结果中选择 2 行（带有 MIN 和 MAX 日期）？
采纳答案by vlad
you can use this:
你可以使用这个：
select * from dbo.myDatabase
where
([Date] = (select max([Date]) from /* your query */ ) or
[Date] = (select min([Date]) from /* your query */ ))
and MachineId = '7B788EE88E-6527-4CB4-AA4D-01B7F4048559' -- or any other id
Edit:since it's entirely possible that two machines have the same datevalue, the query should be updated to also include a MachineIdfilter in the whereclause. I updated the query to show this.
编辑：由于两台机器完全有可能具有相同的date值，因此应更新查询MachineId以在where子句中也包含过滤器。我更新了查询以显示这一点。
回答by Dibstar
In case you ever have multiple rows with an identical date, this query will make sure only one row is returned for the min / max (only for Sql 2005+).
如果您有多个具有相同日期的行，此查询将确保仅返回最小值/最大值的一行（仅适用于 Sql 2005+）。
;WITH dates
AS (SELECT dbo.Mydatabase(id) AS id,
dbo.mydatabase.DATE AS [date],
dbo.mydatabase.datain AS [in],
dbo.mydatabase.dataout AS [out],
Row_number() OVER (ORDER BY DATE ASC) AS row
FROM dbo.mydatabase
WHERE DATE >= Dateadd(DAY, Datediff(DAY, 0, Getdate()) - 7, 0)
AND dbo.mydatabase.machineid =
'7B788EE88E-6527-4CB4-AA4D-01B7F4048559'),
dates2
AS (SELECT id,
DATE,
in,
OUT,
row,
MIN(row) OVER (PARTITION BY (SELECT NULL)) AS lowest_row,
MAX(row) OVER (PARTITION BY (SELECT NULL)) AS highest_row
FROM dates)
SELECT id,
DATE,
in,
OUT
FROM dates2
WHERE row = lowest_row
OR row = highest_row
回答by DRapp
From reading the comments, and each machine doing its own INSERT, the insert wouldn't be inserting a value for the auto-increment column as that is handled by the engine. So, unless the machine is changing its date/time, there IS a direct correlation to the auto-increment ID to the date/time on a PER MACHINE basis. So, that said, and the sample I've created which gets the minimum and maximum ID per the MACHINE where the date/time is qualified WILL result in the definitive first and last ID for the range in question. Then you can get the specific ID records.
通过阅读评论，并且每台机器都在执行自己的 INSERT，插入不会像引擎处理的那样为自动增量列插入值。因此，除非机器更改其日期/时间，否则自动增量 ID 与基于每台机器的日期/时间存在直接关联。所以，也就是说，我创建的示例获取每个机器的最小和最大 ID，其中日期/时间是限定的，这将导致相关范围的确定的第一个和最后一个 ID。然后就可以得到具体的ID记录了。
So, if you have 3 machines, and they do an insert at exact same time, their respective IDs would be generated differently...
因此，如果您有 3 台机器，并且它们在完全相同的时间执行插入操作，则它们各自的 ID 的生成方式将有所不同...
|id |date |MachineId |
-----+-------+-------+-----------------
|1 |2011-10-25 09:03:17.000| A
|2 |2011-10-25 09:03:17.000| B
|3 |2011-10-25 09:03:17.000| C
|4 |2011-10-26 12:45:43.000| B
|5 |2011-10-26 12:45:43.000| A
|6 |2011-10-26 12:45:43.000| C
...
|124 |2011-10-29 13:29:04.000| C
|125 |2011-10-29 13:29:04.000| A
|126 |2011-10-29 13:29:04.000| B
|127 |2011-10-31 21:18:35.000| C
|128 |2011-10-31 21:18:35.000| B
|129 |2011-10-31 21:18:35.000| A
The first and last IDs per respective machine would become
Machine First ID Last ID
A 1 129
B 2 128
C 3 127
The inner pre-query is done once (PER THE SPECIFIC MACHINE), so you are getting the ID associated with the first/last instance per the machine date/time period. Then join that back to the table for the actual data with OR on the ID match.
内部预查询完成一次（根据特定机器），因此您将获得与每个机器日期/时间段的第一个/最后一个实例关联的 ID。然后将其连接回表以使用 ID 匹配上的 OR 获取实际数据。
select
D2.*
FROM
( SELECT
min( D1.ID ) MinDateID,
max( D1.ID ) MaxDateID
from
dbo.myDatabase D1
where
D1.MachineId = '7B788EE88E-6527-4CB4-AA4D-01B7F4048559'
AND D1.Date >=dateadd(day,datediff(day,0,GetDate())- 7,0)
) PreQuery
JOIN dbo.MyDatabase D2
on PreQuery.MinDateID = D2.ID
OR PreQuery.MaxDateID = D2.ID
