Name Count Timestamp
'1FODC1IN', 5, '2013-11-19 12:20:00'
'1FODC2IN', 6, '2013-11-19 12:20:00'
'1FODC3IN', 42, '2013-11-19 12:20:00'
'1FODC4IN', 34, '2013-11-19 12:20:00'
'2FDCIN', 2, '2013-11-19 12:20:00'
'2FISIN', 3, '2013-11-19 12:20:00'
'2FODC1IN', 24, '2013-11-19 12:20:00'
'2FODC3IN', 4, '2013-11-19 12:20:00'
'2FODC4IN', 6, '2013-11-19 12:20:00'
'CMNZ', 35, '2013-11-19 12:20:00'
'GFAODCIN', 29, '2013-11-19 12:20:00'
'MAINZN1', 111, '2013-11-19 12:20:00'
'1FODC1IN', 5, '2013-11-20 11:50:13'
'1FODC2IN', 6, '2013-11-20 11:50:13'
'1FODC3IN', 45, '2013-11-20 11:50:13'
'1FODC4IN', 39, '2013-11-20 11:50:13'
'2FISIN', 2, '2013-11-20 11:50:13'
'2FODC1IN', 20, '2013-11-20 11:50:13'
'2FODC3IN', 10, '2013-11-20 11:50:13'
'2FODC4IN', 7, '2013-11-20 11:50:13'
'CMNZ', 39, '2013-11-20 11:50:13'
'GFAODCIN', 23, '2013-11-20 11:50:13'
'MAINZN1', 131, '2013-11-20 11:50:13'
这种数据每15分钟间隔记录一次。
我的要求是在名称中找到“ IN”字,然后将所有一起出现的1F%IN,类似地一起出现2F%IN,一起GF%IN加起来。
需要输出为
Name count timestamp
first 87 2013-11-19 12:20:00
Second 39 2013-11-19 12:20:00
ground 29 2013-11-19 12:20:00
first 87 2013-11-20 11:50:13
Second 39 2013-11-20 11:50:13
ground 29 2013-11-20 11:50:13
基于时间戳的添加也非常重要。
我怎样才能做到这一点 。
SELECT GName,
SUM(`Count`),
`Timestamp`
FROM
(
select
CASE WHEN Name LIKE '1F%IN' THEN 'first'
WHEN Name LIKE '2F%IN' THEN 'second'
WHEN Name LIKE 'GF%IN' THEN 'ground'
ELSE 'Unknown'
END as GName,
`Count`,
`Timestamp`
FROM T
WHERE Name LIKE '%IN'
) T1
Group by GName,`Timestamp`
order by `Timestamp`,GName
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句