· 9 years ago · Jan 02, 2017, 08:10 AM
1CREATE TABLE IF NOT EXISTS `discrete_archive` (
2`id` int(11) NOT NULL AUTO_INCREMENT,
3`dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
4`f1` smallint(6) NOT NULL,
5`f2` smallint(6) NOT NULL,
6`f3` smallint(6) NOT NULL,
7 PRIMARY KEY (`id`)
8) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
9
10dtime f1
1110:00 1
1210:01 2
1310:02 4
1410:03 4
1510:04 2
1610:05 7
17
1810:00 1
1910:01 2
2010:02 4
2110:04 2
2210:05 7
23
24(3 & 0x10) == (2 & 0x10)
25
26dtime f1
2710:00 1
2810:01 2
2910:02 4
3010:03 4
3110:04 2
3210:05 7
33
3410:00 (f1 & 0x10) = 0
3510:01 (f1 & 0x10) = 2
3610:02 (f1 & 0x10) = 0
3710:04 (f1 & 0x10) = 2
38
39SELECT dtime, f1
40FROM (Select discrete_archive.id, discrete_archive.dtime, discrete_archive.f1,
41IFNULL(t1.f1,discrete_archive.f1-1) as ff1
42FROM discrete_archive
43LEFT JOIN
44(Select id, f1
45FROM discrete_archive) as `t1`
46on discrete_archive.id=t1.id+1 ) as t2
47WHERE f1!=ff1
48
49select distinct(f1), dtime from 'table'