如何找出当前状态 [英] How can find out the current status

问题描述

USE [TestDemo]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 04/28/2015 16:05:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table_1](
    [Id] [int] NOT NULL,
    [Status] [varchar](50) NULL,
    [Time] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

插入值



ID状态时间

1 IN 8:00

1 OUT 8：5
1 IN 8:10

2 IN 8:00

2 OUT 8：5

3 IN 8：5

3 OUT 8:15



Out可以获得当前状态

=====================

ID状态

1 IN

2 OUT

3 OUT



给出sql查询



SHOUTING删除 - OriginalGriff [/ edit]

Insert value

ID STATUS TIME
1 IN 8:00
1 OUT 8:5
1 IN 8:10
2 IN 8:00
2 OUT 8:5
3 IN 8:5
3 OUT 8:15

Out can given current status
=====================
ID STATUS
1 IN
2 OUT
3 OUT

Given for sql query

[edit]SHOUTING removed - OriginalGriff[/edit]

推荐答案

技巧是找到每[Id]的最新值。有两种方法，您可以使用按ID分组并查找最大时间然后再加入原始表。或者您可以使用 ROW_NUMBER [ ^ ]生成降序的函数每个ID的时间。

The trick is to find the latest value per [Id]. There are two ways, either you use group by Id and find out max Time then join back to the original table. Or you can use ROW_NUMBER[^] function to generate a descending sequence by Time per Id.

select [Id], [Status], [Time]
from (
	select [Id], [Status], [Time], 
		row_number() over(partition by [Id] order by [Time] desc) seq 
	from [dbo].[Table_1] 
	) x
where x.seq = 1

这当然假设时间列是datetime而不是varchar。

This of course assumes that the Time columns is datetime not varchar.

这篇关于如何找出当前状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！