对于5分钟之内的日期时间的SQL查询
问题描述:
我试图设置一个报表,在五分钟内查询对方的数据。这样,我可以认识到我的一台机器停机。这是我到目前为止:对于5分钟之内的日期时间的SQL查询
SELECT A_M_DEVICE.M_Device_ID,
A_M_DEVICE.Machine_Name,
A_M_DEVICE.IP_Address,
A_M_DEVICE.Device_Type,
A_M_DEVICE.M_Device_Status_ID,
A_M_DEVICE.Status_Date,
S_M_DEVICE_STATUS.M_Device_Status_Desc,
A_M_DEVICE.Domain_Name FROM MConsole.dbo.A_M_DEVICE A_M_DEVICE
INNER JOIN
MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
ON (A_M_DEVICE.M_Device_Status_ID =
S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
('DeVA',
'DevB',
))
AND (A_M_DEVICE.Status_Date = DateDiff (Second) <= 300)
由于我是一个新手,不允许图像。否则我会发布一个。
好吧 - 看起来我有足够的声誉的截图! 任何帮助,一如既往,将不胜感激。
预先感谢您。
答
选择具有不匹配的MAX状态日期状态日期行:
SELECT A_M_DEVICE.M_Device_ID,
A_M_DEVICE.Machine_Name,
A_M_DEVICE.IP_Address,
A_M_DEVICE.Device_Type,
A_M_DEVICE.M_Device_Status_ID,
A_M_DEVICE.Status_Date,
S_M_DEVICE_STATUS.M_Device_Status_Desc,
A_M_DEVICE.Domain_Name FROM MConsole.dbo.A_M_DEVICE A_M_DEVICE
INNER JOIN
MConsole.dbo.S_M_DEVICE_STATUS S_M_DEVICE_STATUS
ON (A_M_DEVICE.M_Device_Status_ID =
S_M_DEVICE_STATUS.M_Device_Status_ID) WHERE (A_M_DEVICE.Machine_Name IN
('DeVA',
'DevB',
))
AND (A_M_DEVICE.Status_Date NOT IN (SELECT MAX(A_M_DEVICE.Status_Date) FROM A_M_DEVICE)
的日期将是代表那些操作机器的所有行一样 - 所以消除那些符合该MAX日期记录代表没有进行状态更新的机器。
答
下面是一个近似值 - 检查 “< 5” 与 “> 5” 等
SELECT M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name
FROM
( SELECT M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name,
MAX(Status_Date)
FROM A_M_DEVICE
GROUP BY
M_Device_ID,
Machine_Name,
IP_Address,
Device_Type,
M_Device_Status_ID,
M_Device_Status_Desc,
Domain_Name
) AS a
JOIN A_M_DEVICE AS b
ON
a.Machine_Name = b.Machine_Name
AND a.IP_Address = b.IP_Address
AND a.Device_Type = b.Device_Type
AND a.M_Device_Status_ID = b.M_Device_Status_ID
AND a.M_Device_Status_Desc = b.M_Device_Status_Desc
AND a.Domain_Name = b.Domain_Name
WHERE
DATEDIFF(minute, a.Status_Date, b.Status_Date) < 5
@Shaji - 这也有助于指定版本。 – Thomas 2011-05-03 19:20:22