如何获取此数据?
我有这样的事情:如何获取此数据?
Time EQID STATUS USER
12:12am EQ1 0 SA
12:14am EQ1 1 SA
02:30am EQ2 0 SA
03:30am EQ2 1 SA
05:30am EQ1 0 SA
06:30am EQ2 1 SA
现在所需要的输出是:
In_time Out_time EQID USER
12:12am 12:14am EQ1 SA
02:30am 03:30am EQ2 SA
05:30am 06:30am EQ1 SA
这里In_time
是时间状态 '0' 和Out_time
与状态的时间 '1'。我如何得到这个特定EQID和用户的In_time
和Out_time
。
注:喜欢我有多个用户,让很多行,我怎样才能得到呢?
CREATE TABLE #data (
[Time] datetime,
[EQID] nvarchar(10),
[Status] bit,
[User] nvarchar(10)
)
INSERT INTO #data VALUES ('2017-12-27 12:12am', 'EQ1', 0, 'SA')
INSERT INTO #data VALUES ('2017-12-27 12:14am', 'EQ1', 1, 'SA')
INSERT INTO #data VALUES ('2017-12-27 02:30am', 'EQ1', 0, 'SA')
INSERT INTO #data VALUES ('2017-12-27 03:30am', 'EQ1', 1, 'SA')
INSERT INTO #data VALUES ('2017-12-27 05:30am', 'EQ1', 0, 'SA')
INSERT INTO #data VALUES ('2017-12-27 06:30am', 'EQ1', 1, 'SA')
-- Get the next time where status is 1
SELECT D1.[Time] as 'In_Time'
, D2.[Time] as 'Out_Time'
, D1.[EQID]
, D1.[User]
FROM #data D1
LEFT JOIN #data D2
ON D2.[Time] = (SELECT TOP 1 [Time]
FROM #data
WHERE [Status] = 1
AND [Time] > D1.[Time]
AND [User] = D1.[User]
AND [EQID] = D1.[EQID]
ORDER BY [Time])
WHERE D1.[Status] = 0
您的连接条件查询没有充分关联。它不考虑EQID或用户。 –
@TabAlleman他的榜样不考虑这些领域,或者'EQ1'的出场时间不是上午6:30。该值属于'EQ2'。我想可以假设用户需要是相同的,虽然... – EMUEVIL
实际上在评论中,OP指出最后一个示例行实际上应该是EQ1。尽管如此,它还没有在问题中编辑过。但是,在正确的输出结束后的段落中,OP表示必须同时考虑EQID和用户。 –
一种方法是做所有的行,其中状态= 0的主查询。这会给你所有的列,除了out_time
。
添加到这一点,一个相关子查询以获得out_time
并获得第一行(为了通过Time
),其中EQID和用户是相同的主查询,状态= 1,并且Time
比Time
更大在主查询中。
(换句话说,该out_Time
是MIN Time
此EQID &用户与状态= 1比当前状态= 0行更大。)
状态,用户ID,eqid没让你可以请你写一个简单的查询@TabAlleman –
我已经编辑了清晰的答案。我不会为你写查询,但如果你尝试了,我会帮你调试它。 –
对不起。我没有得到这个.. –
这里有几个问题。
- 你存储
TIME
为VARCHAR
- 您存储TIME没有
DATE
这使得它无法确定当用户在主频的第一天,但没有时钟输出的下一个 - 您还没有提到EQID是什么,或者你为什么选择在EQ1 EQ2的最后一行
有了这样说......这里是一个办法。
declare @table table ( [Time] varchar(64),
EQID char(3),
[STATUS] int,
[USER] char(2))
insert into @table
values
('12:12am','EQ1',0,'SA'),
('12:14am','EQ1',1,'SA'),
('02:30am','EQ2',0,'SA'),
('03:30am','EQ2',1,'SA'),
('05:30am','EQ1',0,'SA'),
('06:30am','EQ2',1,'SA')
;with cte as(
select
[Time]
,EQID
,[USER]
,[Status]
,RN = row_number() over(partition by [USER] order by cast(stuff([Time],6,0,' ') as time))
from
@table t)
select
In_Time = i.[Time]
,Out_Time = o.[Time]
,i.EQID
,i.[USER]
from
cte i
left join cte o on
o.[USER] = i.[USER]
and o.RN = i.RN + 1
where
i.[STATUS] = 0
请只大写一个句子的第一个单词,人名和I.不要随机地大写其他单词,这会让阅读变得更难。 –
@MarkRotteveel好了... –
不应该在例如数据最后一个记录是'EQ1'? –