| 1 |
DROP TABLE IF EXISTS CC, C, BB; |
| 2 |
CREATE TABLE `CC` ( |
| 3 |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
| 4 |
`int_nokey` int(11) NOT NULL, |
| 5 |
`datetime_key` datetime NOT NULL, |
| 6 |
`varchar_key` varchar(1) NOT NULL, |
| 7 |
PRIMARY KEY (`pk`), |
| 8 |
KEY `datetime_key` (`datetime_key`), |
| 9 |
KEY `varchar_key` (`varchar_key`) |
| 10 |
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; |
| 11 |
INSERT INTO `CC` VALUES |
| 12 |
(10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'), |
| 13 |
(12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'), |
| 14 |
(14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''), |
| 15 |
(16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'), |
| 16 |
(18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'), |
| 17 |
(20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'), |
| 18 |
(22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''), |
| 19 |
(24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'), |
| 20 |
(26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'), |
| 21 |
(28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x'); |
| 22 |
CREATE TABLE `C` ( |
| 23 |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
| 24 |
`int_nokey` int(11) NOT NULL, |
| 25 |
`datetime_key` datetime NOT NULL, |
| 26 |
`varchar_key` varchar(1) NOT NULL, |
| 27 |
PRIMARY KEY (`pk`), |
| 28 |
KEY `datetime_key` (`datetime_key`), |
| 29 |
KEY `varchar_key` (`varchar_key`) |
| 30 |
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; |
| 31 |
INSERT INTO `C` VALUES |
| 32 |
(1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'), |
| 33 |
(3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'), |
| 34 |
(5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'), |
| 35 |
(7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'), |
| 36 |
(9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'), |
| 37 |
(11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'), |
| 38 |
(13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'), |
| 39 |
(15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'), |
| 40 |
(17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''), |
| 41 |
(19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u'); |
| 42 |
CREATE TABLE `BB` ( |
| 43 |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
| 44 |
`int_nokey` int(11) NOT NULL, |
| 45 |
`datetime_key` datetime NOT NULL, |
| 46 |
`varchar_key` varchar(1) NOT NULL, |
| 47 |
PRIMARY KEY (`pk`), |
| 48 |
KEY `datetime_key` (`datetime_key`), |
| 49 |
KEY `varchar_key` (`varchar_key`) |
| 50 |
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; |
| 51 |
INSERT INTO `BB` VALUES |
| 52 |
(10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51',''); |
| 53 |
SELECT DISTINCT `datetime_key` |
| 54 |
FROM C |
| 55 |
WHERE ( `int_nokey` , `pk` ) IN ( |
| 56 |
SELECT INNR .`pk` , INNR .`pk` |
| 57 |
FROM CC LEFT JOIN BB INNR ON INNR .`varchar_key` ) AND `pk` = 9 ; |
| 58 |
datetime_key |
| 59 |
DROP TABLE CC, C, BB; |
| 60 |
DROP TABLE IF EXISTS CC, C, BB; |
| 61 |
CREATE TABLE `CC` ( |
| 62 |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
| 63 |
`int_nokey` int(11) NOT NULL, |
| 64 |
`int_key` int(11) NOT NULL, |
| 65 |
`date_key` date NOT NULL, |
| 66 |
`datetime_key` datetime NOT NULL, |
| 67 |
`varchar_key` varchar(1) NOT NULL, |
| 68 |
`varchar_nokey` varchar(1) NOT NULL, |
| 69 |
PRIMARY KEY (`pk`), |
| 70 |
KEY `int_key` (`int_key`), |
| 71 |
KEY `date_key` (`date_key`), |
| 72 |
KEY `datetime_key` (`datetime_key`), |
| 73 |
KEY `varchar_key` (`varchar_key`) |
| 74 |
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; |
| 75 |
INSERT INTO `CC` VALUES |
| 76 |
(10,0,8,'2007-02-14','2006-07-07 07:26:28','q','q'), |
| 77 |
(11,5,8,'2002-10-03','2002-09-23 00:00:00','m','m'), |
| 78 |
(12,7,3,'2006-12-02','0000-00-00 00:00:00','j','j'), |
| 79 |
(13,1,2,'2007-05-02','2006-06-07 00:00:00','z','z'), |
| 80 |
(14,8,2,'2001-11-18','2000-09-16 12:15:34','a','a'), |
| 81 |
(15,2,6,'2006-09-09','2007-08-05 15:47:52','',''), |
| 82 |
(16,1,8,'0000-00-00','0000-00-00 00:00:00','e','e'), |
| 83 |
(17,8,9,'2003-07-22','2005-12-02 19:34:26','t','t'), |
| 84 |
(18,5,2,'2001-12-22','0000-00-00 00:00:00','q','q'), |
| 85 |
(19,4,6,'0000-00-00','0000-00-00 00:00:00','b','b'), |
| 86 |
(20,5,5,'2006-09-02','2007-12-28 00:00:00','w','w'), |
| 87 |
(21,3,2,'0000-00-00','2004-08-02 11:48:43','m','m'), |
| 88 |
(22,0,4,'0000-00-00','0000-00-00 00:00:00','x','x'), |
| 89 |
(23,8,9,'2001-02-28','2004-04-19 12:18:43','',''), |
| 90 |
(24,0,6,'0000-00-00','2009-04-27 00:00:00','w','w'), |
| 91 |
(25,4,5,'2007-05-19','2006-10-20 14:52:15','x','x'), |
| 92 |
(26,0,0,'2005-02-15','0000-00-00 00:00:00','e','e'), |
| 93 |
(27,0,0,'2000-10-19','2002-03-22 11:48:37','e','e'), |
| 94 |
(28,2,8,'2005-07-07','0000-00-00 00:00:00','p','p'), |
| 95 |
(29,0,0,'2008-10-18','2001-01-04 03:55:07','x','x'); |
| 96 |
CREATE TABLE `C` ( |
| 97 |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
| 98 |
`int_nokey` int(11) NOT NULL, |
| 99 |
`int_key` int(11) NOT NULL, |
| 100 |
`date_key` date NOT NULL, |
| 101 |
`datetime_key` datetime NOT NULL, |
| 102 |
`varchar_key` varchar(1) NOT NULL, |
| 103 |
`varchar_nokey` varchar(1) NOT NULL, |
| 104 |
PRIMARY KEY (`pk`), |
| 105 |
KEY `int_key` (`int_key`), |
| 106 |
KEY `date_key` (`date_key`), |
| 107 |
KEY `datetime_key` (`datetime_key`), |
| 108 |
KEY `varchar_key` (`varchar_key`) |
| 109 |
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; |
| 110 |
INSERT INTO `C` VALUES |
| 111 |
(1,9,9,'2007-12-01','0000-00-00 00:00:00','p','p'), |
| 112 |
(2,0,0,'0000-00-00','2002-02-09 07:38:13','v','v'), |
| 113 |
(3,8,6,'0000-00-00','2001-05-03 12:08:14','t','t'), |
| 114 |
(4,3,6,'2002-05-07','0000-00-00 00:00:00','u','u'), |
| 115 |
(5,7,6,'0000-00-00','2009-07-28 03:43:30','n','n'), |
| 116 |
(6,0,4,'0000-00-00','2009-08-04 00:00:00','l','l'), |
| 117 |
(7,1,7,'0000-00-00','0000-00-00 00:00:00','h','h'), |
| 118 |
(8,9,4,'0000-00-00','0000-00-00 00:00:00','u','u'), |
| 119 |
(9,0,8,'0000-00-00','2005-08-02 17:16:54','n','n'), |
| 120 |
(10,9,4,'2000-12-18','2002-12-21 00:00:00','j','j'), |
| 121 |
(11,0,7,'2005-11-13','2005-08-15 12:37:35','k','k'), |
| 122 |
(12,5,5,'0000-00-00','0000-00-00 00:00:00','e','e'), |
| 123 |
(13,0,0,'2003-11-12','2006-03-10 00:00:00','i','i'), |
| 124 |
(14,8,5,'2006-02-20','2005-05-16 11:02:36','u','u'), |
| 125 |
(15,8,7,'2005-02-12','2008-11-02 00:00:00','n','n'), |
| 126 |
(16,5,2,'2009-07-20','2006-03-15 00:00:00','b','b'), |
| 127 |
(17,1,8,'2005-02-24','0000-00-00 00:00:00','x','x'), |
| 128 |
(18,7,0,'0000-00-00','0000-00-00 00:00:00','',''), |
| 129 |
(19,0,9,'0000-00-00','2008-12-17 20:15:40','q','q'), |
| 130 |
(20,9,5,'0000-00-00','0000-00-00 00:00:00','u','u'); |
| 131 |
CREATE TABLE `BB` ( |
| 132 |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
| 133 |
`int_nokey` int(11) NOT NULL, |
| 134 |
`int_key` int(11) NOT NULL, |
| 135 |
`date_key` date NOT NULL, |
| 136 |
`datetime_key` datetime NOT NULL, |
| 137 |
`varchar_key` varchar(1) NOT NULL, |
| 138 |
`varchar_nokey` varchar(1) NOT NULL, |
| 139 |
PRIMARY KEY (`pk`), |
| 140 |
KEY `int_key` (`int_key`), |
| 141 |
KEY `date_key` (`date_key`), |
| 142 |
KEY `datetime_key` (`datetime_key`), |
| 143 |
KEY `varchar_key` (`varchar_key`) |
| 144 |
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; |
| 145 |
INSERT INTO `BB` VALUES (10,8,5,'0000-00-00','2007-08-19 |
| 146 |
08:08:38','i','i'),(11,0,8,'2005-08-18','2000-05-21 03:51:51','',''); |
| 147 |
SELECT DISTINCT BIT_AND( OUTR . `datetime_key` ) AS X |
| 148 |
FROM C AS OUTR |
| 149 |
WHERE ( OUTR . `int_nokey` , OUTR . `pk` ) IN ( |
| 150 |
SELECT DISTINCT INNR . `pk` AS X , INNR . `pk` AS Y |
| 151 |
FROM CC AS INNR2 LEFT JOIN BB AS INNR ON |
| 152 |
( INNR2 . `varchar_nokey` = INNR . `varchar_key` ) |
| 153 |
WHERE INNR . `date_key` BETWEEN '2009-04-26' AND '2004-08-21' ) |
| 154 |
AND OUTR . `pk` = 9 |
| 155 |
ORDER BY OUTR . `int_key` , OUTR . `pk`; |
| 156 |
X |
| 157 |
18446744073709551615 |
| 158 |
DROP TABLE CC, C, BB; |