列上存在日期时间时获取唯一行

2024-06-03 • 问答

您好，我有一张桌子，每次计数时都会插入其中。使用一个订单，同一天可能会超过一个订单。在该表中，我要插入当前班次。我想做的是获取每个班次的第一行，并且在该班次中是否超过1个顺序都没关系。

此刻是我的查询

SELECT DISTINCT Part [Model],Station,Operators,Workorder,ShiftId,min([Date]) [StartDate],CONVERT(date,min([Date])) [StartDateFormat] 
FROM  Orders
WHERE Station IN ('S1','S2')
group by Part,ShiftId
order by Station

有关完整示例，请check this SQL Fiddle

我期望的结果是下表：

Model  |  Station  | Operator  |  Workorder  |  ShiftId  |  StartDate            |  StartDateFormat  |
A1     |   S1      |   5       |  45010001   |     1     | 2020-01-17T10:24:08Z  |   2020-01-17      |   
A1     |   S1      |   5       |  45010022   |     2     | 2020-01-17T11:35:08Z  |   2020-01-17      |
A1     |   S1      |   15      |  45010004   |     3     | 2020-01-18T19:35:08Z  |   2020-01-18      |
Rows for Station S2 Are OK

换句话说，我想从每个班次获得第一行

有很多方法可以解决每组头1个问题。

一种解决方案是使用相关子查询进行过滤：

select o.*
from orders o
where o.date = (
    select min(o1.date) 
    from orders o1
    where o1.shiftId = o.shiftId and cast(o1.date as date) = cast(o.date as date) 
)
order by o.date

这为您每天和轮班提供最早的记录。

如果您希望整天每个班次都记录第一条记录，则可以对查询进行一些修改：

select o.*
from orders o
where o.date = (
    select min(o1.date) 
    from orders o1
    where o1.shiftId = o.shiftId 
)
order by o.date

我也喜欢抗left join技术：

select o.*
from orders o
left join orders o1 
    on  o1.shiftId = o.shiftId 
    and cast(o1.date as date) = cast(o.date as date)
where o1.id is null
order by o.date

如果我理解正确，那么您需要这样的东西：

SELECT  *
FROM    (
            SELECT  PART [MODEL],STATION,OPERATORS,WORKORDER,SHIFTID,[DATE]  AS  [STARTDATE],CONVERT(DATE,[DATE]) AS [STARTDATEFORMAT],ROW_NUMBER() OVER(PARTITION BY CONVERT(DATE,[DATE]),SHIFTID ORDER BY [DATE])  AS  RN
            FROM    ORDERS
            WHERE STATION IN ('S1','S2')
        ) V1
WHERE   V1.RN = 1

SQL Fiddle

列上存在日期时间时获取唯一行

qq842663987 回答：列上存在日期时间时获取唯一行

大家都在问