如何根据时间首次出现标志1记录和最后出现标志0记录 [英] How to get first occurrence of flag 1 record and last occurrence flag 0 record based on time
本文介绍了如何根据时间首次出现标志1记录和最后出现标志0记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有ms sql表中给出的记录列表
time flag
2018-04-18 16:24:08.0000000 0
2018-04-18 16:14:08.0000000 1
2018-04-18 16:04:08.0000000 0
2018-04-18 15:54:08.0000000 0
2018-04-18 15:44:08.0000000 1
2018-04-18 15:34:08.0000000 1
2018-04-18 15:24:08.0000000 0
2018-04-18 15:04:08.0000000 1
2018-04-18 14:54:08.0000000 1
2018-04-18 14:44:08.0000000 0
如何获得第一次出现的标志1记录(只有一个记录基于时间发生了第一次)和最后一次出现的标志0记录(只有一个记录最后出现)
结果应该是:
2018-04-18 14:54:08.0000000 1
2018-04-18 16:24:08.0000000 0
(两条记录)
或
2018-04-18 14:54:08.0000000 1 2018-04-18 16:24:08.0000000 0(单曲)
我尝试了什么:
我尝试过使用Rownumber()的乐趣但我没有得到预期的结果
解决方案
尝试:
SELECT CASE WHEN 标志= 1 那么 MAX([时间])
ELSE MIN([时间])
END AS [时间],
标志
FROM MyTable
GROUP BY 标记
一种方法是使用相关的NOT EXISTS条件。类似
SELECT *
FROM TableNameGoesHere t1
WHERE t1.Flag = 1
AND NOT EXISTS ( SELECT 1
FROM TableNameGoesHere t2
WHERE t2.Flag = 1
AND t2。时间< t1。时间)
I have list of records as given below in ms sql table
time flag 2018-04-18 16:24:08.0000000 0 2018-04-18 16:14:08.0000000 1 2018-04-18 16:04:08.0000000 0 2018-04-18 15:54:08.0000000 0 2018-04-18 15:44:08.0000000 1 2018-04-18 15:34:08.0000000 1 2018-04-18 15:24:08.0000000 0 2018-04-18 15:04:08.0000000 1 2018-04-18 14:54:08.0000000 1 2018-04-18 14:44:08.0000000 0
how to get first occurrence of flag 1 record(only one record that has occurred 1 st time based on time) and the last occurrence of flag 0 record(only one record that has occurred last) Result should be : 2018-04-18 14:54:08.0000000 1 2018-04-18 16:24:08.0000000 0 (two records) or else 2018-04-18 14:54:08.0000000 1 2018-04-18 16:24:08.0000000 0(single record)
What I have tried:
I have tried using Rownumber() function but i havent get expected result
解决方案
Try:
SELECT CASE WHEN Flag = 1 THEN MAX([Time]) ELSE MIN([Time]) END AS [Time], Flag FROM MyTable GROUP BY Flag
One way is to use a correlated NOT EXISTS condition. Something like
SELECT * FROM TableNameGoesHere t1 WHERE t1.Flag = 1 AND NOT EXISTS ( SELECT 1 FROM TableNameGoesHere t2 WHERE t2.Flag = 1 AND t2.Time < t1.Time)
这篇关于如何根据时间首次出现标志1记录和最后出现标志0记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文