解决方案 »
- 如何用一个SQL语句,实现把某列值组合成一个字串串?
- 为什么这个视图a.type='1'这个条件无效
- 求教1个关于排名的问题
- 当有多个连接的临时表存在时,怎么查询当前连接的临时表名
- 报 用户 'NT AUTHORITY\SYSTEM' 登录失败。 急急急急急。。。。。。。。
- 请问下面这个存储语句为什么有“不能对包含聚合或子查询的表达式执行聚合函数。”应该怎么改,谢谢了!!!
- 用ODBC API连接数据库, 能实现连接池吗?
- select求助,您请进...
- SQL SERVER2000小问题
- 请问各位大侠,我怎样才能得到的Oracle存储过程的返回值,在程序中!
- 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