r9W6MGSY

1CREATE DATABASE IF NOT EXISTS `puzzle` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
2
3USE `puzzle`;
4
5DROP TABLE IF EXISTS `event`;
6
7CREATE TABLE `event` (
8  `eventId` bigint(20) NOT NULL AUTO_INCREMENT,
9  `sourceId` bigint(20) NOT NULL COMMENT 'think of source as camera',
10  `carCode` bigint(20) NOT NULL COMMENT 'ex: A',
11  `carNumber` varchar(40) NOT NULL COMMENT 'ex: 5849',
12  `createdOn` datetime DEFAULT NULL,
13  PRIMARY KEY (`eventId`)
14) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
15
16
17INSERT INTO `event` (`eventId`, `sourceId`, `carCode`, `carNumber`, `createdOn`) VALUES
18    (1, 44,'A', '4456', '2016-09-20 20:24:05'),
19    (2, 26,'B', '26484', '2016-09-20 20:24:05'),
20    (3, 5,'A', '4456', '2016-09-20 20:24:06'),
21    (4, 3,'C', '72704', '2016-09-20 20:24:15'),
22    (5, 3,'D' ,'399606', '2016-09-20 20:26:15'),
23    (6, 5, 'A', '4456', '2016-09-20 20:27:25'),
24    (7, 44,'C', '72704', '2016-09-20 20:29:25'),
25    (8, 3,'A' ,'4456', '2016-09-20 20:30:55'),
26    (9, 44,'B' ,'26484', '2016-09-20 20:34:55'),
27    (10, 26,'B' ,'4456', '2016-09-20 20:35:15'),
28    (11, 3, 'C','72704', '2016-09-20 20:35:15'),
29    (12, 3,'D', '399606', '2016-09-20 20:44:35'),
30    (13, 26,'A' ,'4456', '2016-09-20 20:49:45');
31	
32select distinct e1.carNumber, e1.carCode from event e1  
33
34    inner join (select e2.carNumber, e2.carCode from event e2 where e2.sourceId in (44,3) ) e3 on e1.carNumber= e3.carNumber 
35    inner join (select e2.carNumber, e2.carCode from event e2  where e2.sourceId in (44,3)  ) e4 on e1.carCode= e4.carCode 
36
37   inner join (select e2.carNumber, e2.carCode from event e2 where e2.sourceId in  (26,3,5) ) e5 on e1.carNumber= e5.carNumber 
38    inner join (select e2.carNumber, e2.carCode from event e2  where e2.sourceId in  (26,3,5) ) e6 on e1.carCode= e6.carCode