SQL选择具有max和min日期的行

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:

我试着从一张桌子上得到两排。一个是上周的最小日期(今天- 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行(最小和最大日期)?

3 个解决方案

#1

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 date value, the query should be updated to also include a MachineId filter in the where clause. I updated the query to show this.

编辑:由于两个机器具有相同的日期值是完全可能的，因此应该更新查询，以便在where子句中也包含一个MachineId过滤器。我更新了查询以显示这一点。

#2

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

#3

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.

从读取注释，以及每台机器自己执行插入操作，插入不会为自动增量列插入一个值，因为这是由引擎处理的。因此，除非机器正在更改它的日期/时间，否则在每台机器的基础上，自动递增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匹配的实际数据的表。

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

#1