解决方案 »
- 如何设置tempdb自增大小??????
- 郁闷,散分!
- java往MySQL数据库插入中文数据有问题,请各位大虾帮忙!
- reportviewer 打印問題
- 请教这样的sql语句怎么写?
- 帮我看看这个触发器
- 特殊的列转行问题
- 求效率最高的sql-server查询实现方法(续,另加分贴)
- 配置过Windows 2000 Advanced Server 中的群集管理器的高手请进来
- Sql Server中,使用select top 10 no,name from table语句(no为新增字段),使no按顺序存放1,2,3-10
- TSQL查询【成绩个数】【最高分】【最低分】【平均分】
- 取前5名数据 剩下的显示合计其他
SELECT logid,logtime
,COUNT(1)OVER(PARTITION BY logid)C
,MIN(logtime)OVER(PARTITION BY logid)Minlogtime
,MAX(logtime)OVER(PARTITION BY logid)Maxlogtime
FROM logtable
WHERE logtime BETWEEN '2015-3-1 11:00:00' AND '2015-3-1 13:00:00'
)
SELECT logid,logtime
FROM CTE
WHERE C=4 AND DATEDIFF(MINUTE,Minlogtime,Maxlogtime)>=40
(SELECT logid FROM logtable WHERE logtime BETWEEN '2015-3-1 11:00:00' AND '2015-3-1 13:00:00' GROUP BY Logid HAVING COUNT(*)=4)
AND logtime BETWEEN '2015-3-1 11:00:00' AND '2015-3-1 13:00:00' ) A
WHERE logid in(select logid FROM A GROUP BY logid HAVING DATEDIFF(MINUTE,MIN(logtime),MAX(logtime))>40)
ORDER by logname,logtime
SELECT *
FROM logtablE a
INNER JOIN (
SELECT logid,MAX(logtime) AS MaxLogTime,MIN(logtime) AS MinLogTime
FROM logtable
WHERE logtime BETWEEN '2015-3-1 11:00:00' AND '2015-3-1 13:00:00'
GROUP BY Logid HAVING COUNT(*)=4) b ON a.logid=b.logid AND DATEDIFF(MINUTE,b.MinLogTime,b.MaxLogTime)>40