SQL：查找上次类型更改的最小日期

时间：2021-12-12 20:15:07

I have a log table like this:

我有一个这样的日志表：

line    Date                    status  Type
1   26.04.2016 08:58    IN  4
2   26.04.2016 08:59    OUT 80
3   26.04.2016 09:05    REZ 7
4   26.04.2016 09:06    IN  7
5   26.04.2016 09:22    EDIT    81
6   26.04.2016 09:23    EDIT    80
7   26.04.2016 09:24    OUT 80
8   26.04.2016 09:25    OUT 80
9   26.04.2016 09:25    OUT 80

"date" is the key.

“约会”是关键。

I want to get last type change date. In this example last type is "80" and the min date of it is 26.04.2016 09:23 (line 6).

我想获得最后一次更改日期。在这个例子中，最后一个类型是“80”，它的最小日期是26.04.2016 09:23（第6行）。

I know the last type at this point (@lasttype = 80). But if I run select min(date) from table where type = @lasttype, then I get line 2 instead of line 6.

我知道此时的最后一种类型（@lasttype = 80）。但是如果我从table中运行select min（date），其中type = @lasttype，那么我得到第2行而不是第6行。

How can I get min date of last block of "type" (line 6) in one query? I don't want to use several select after and after.

如何在一个查询中获取“类型”（第6行）的最后一个块的最小日期？我不想在之后使用几个选择。

I have just try this but line 6 doesn't come again:(

我刚尝试这个，但第6行不再来了:(

select max(date)
from
(
    select min(date) as date, type
    from MYTABLE
    where type = '80'
    group by type
) as t1

Is there a easy way?

有一个简单的方法吗？

Many thanks.

非常感谢。

Edit: Ok, I can get line 6:

编辑：好的，我可以得到第6行：

select min(date) from MYTABLE where date > (select max(date) from MYTABLE where type <> @lasttype)

Any more effective code?

更有效的代码？

Many thanks for your answers...

非常感谢您的回答......

4 个解决方案

#1

0  

Try This

尝试这个

select min(date) from MYTABLE where Type=@lasttype
group by Type

#2

0  

I think if you like to retrieve last time, you better use max instead of min function, check the below:

我想如果您想上次检索，最好使用max而不是min函数，请查看以下内容：

select top 1 type,max(date) as date from MYTABLE group by type order by max(date) desc

#3

0  

Ok, I can get line 6:

好的，我可以得到第6行：

select min(date) from MYTABLE where date > (select max(date) from MYTABLE where type <> @lasttype)

Any more effective code?

更有效的代码？

Many thanks for your answers...

非常感谢您的回答......

#4

0  

Try running this query

尝试运行此查询

select max(a.Date) from 
(
 Select top 4 * from table order by Date desc
)a;

#1

0  

Try This

尝试这个

select min(date) from MYTABLE where Type=@lasttype
group by Type

#2

0  

I think if you like to retrieve last time, you better use max instead of min function, check the below:

我想如果您想上次检索，最好使用max而不是min函数，请查看以下内容：

select top 1 type,max(date) as date from MYTABLE group by type order by max(date) desc

#3

0  

Ok, I can get line 6:

好的，我可以得到第6行：

select min(date) from MYTABLE where date > (select max(date) from MYTABLE where type <> @lasttype)

Any more effective code?

更有效的代码？

Many thanks for your answers...

非常感谢您的回答......

#4

0  

Try running this query

尝试运行此查询

select max(a.Date) from 
(
 Select top 4 * from table order by Date desc
)a;
标签：

相关文章