我有一个选择COUNT distinct的问题:我需要的答案是6。 在计数时,时间差小于300秒视为1。 我的SQL语句:
SELECT COUNT(DISTINCT(cast([Date] as date) + cast([time] as datetime)))
FROM [Table1]WHERE [DATE] = '2013-06-22'
AND ErrCode = 'Scrubber Failure'AND Frequency = 1
我有一个选择COUNT distinct的问题:我需要的答案是6。 在计数时,时间差小于300秒视为1。 我的SQL语句:
SELECT COUNT(DISTINCT(cast([Date] as date) + cast([time] as datetime)))
FROM [Table1]WHERE [DATE] = '2013-06-22'
AND ErrCode = 'Scrubber Failure'AND Frequency = 1
UPDATED
SELECT COUNT(DISTINCT
ROUND(CONVERT(DECIMAL(20, 4),
DATEDIFF(minute,
CONVERT(datetime, '2013-01-01 00:00:00', 120),
CONVERT(datetime, date + ' ' + time, 120))) / 5, 0)) n
FROM table1
WHERE date = '2013-06-22'
AND errcode = 'Scrubber Failure'
AND frequency = 1
输出:
| N | ----- | 6 |
这里是SQLFiddle演示。