select date(`date`),count(*) from `barcode_2d_y` where `date`>'2011-07-25' AND `date`<'2011-08-01' group by date(`date`) asc union all select date(`date`),count(*) from `barcode_2d_n` where `date`>'2011-07-25' AND `date`<'2011-08-01' group by date(`date`) asc结果如下:
date(`date`) count(*)
2011-07-28 4
2011-07-31 3
2011-07-28 3
2011-07-31 10有无办法让结果变成:
date(`date`) count(*)y表 count(*)n表
2011-07-28 4 3
2011-07-31 3 10
date(`date`) count(*)
2011-07-28 4
2011-07-31 3
2011-07-28 3
2011-07-31 10有无办法让结果变成:
date(`date`) count(*)y表 count(*)n表
2011-07-28 4 3
2011-07-31 3 10
-- 表的结构 `barcode_2d_y`
--CREATE TABLE IF NOT EXISTS `barcode_2d_y` (
`ID` int(20) NOT NULL AUTO_INCREMENT,
`username` varchar(5) NOT NULL,
`lotno` varchar(10) NOT NULL,
`barcode` varchar(50) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;--
-- 转存表中的数据 `barcode_2d_y`
--INSERT INTO `barcode_2d_y` (`ID`, `username`, `lotno`, `barcode`, `date`) VALUES
(1, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:43:15'),
(2, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:43:10'),
(3, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:42:35'),
(4, '00868', '10713', 'TUSBDBASD-562423-HGF', '2011-07-13 13:25:46'),
(5, '00868', '10713', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-13 13:25:35'),
(6, '00868', '10713', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-13 13:25:39'),
(7, '00868', '10713', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-13 13:25:43'),
(8, '00868', '10713', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-13 13:25:23'),
(9, '00868', '10728', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-28 13:28:15'),
(10, '00868', '10728', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-28 13:28:22'),
(11, '00868', '10728', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-28 13:28:26'),
(12, '00868', '10728', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-28 13:28:30');--
-- 表的结构 `barcode_2d_n`
--CREATE TABLE IF NOT EXISTS `barcode_2d_n` (
`ID` int(20) NOT NULL AUTO_INCREMENT,
`username` varchar(5) NOT NULL,
`lotno` varchar(10) NOT NULL,
`barcode` varchar(50) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;--
-- 转存表中的数据 `barcode_2d_n`
--INSERT INTO `barcode_2d_n` (`ID`, `username`, `lotno`, `barcode`, `date`) VALUES
(1, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:43:07'),
(2, '00868', '10802', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-02 15:03:27'),
(3, '00812', '10731', 'TUSBDBASD-562423-HGF', '2011-07-31 14:43:02'),
(4, '00812', '10731', 'TUSBDBASD-562422-HGF', '2011-07-31 14:42:58'),
(5, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:42:45'),
(6, '00812', '10731', 'TUSBDBASD-562422-HGF', '2011-07-31 14:42:31'),
(7, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:42:40'),
(8, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:42:27'),
(9, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:42:22'),
(10, '00812', '10731', 'TUSBDBASD-562422-HCF', '2011-07-31 14:42:19'),
(11, '00812', '10731', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-07-31 14:42:15'),
(12, '00868', '10713', 'TUSBDBASD-562422-HGF', '2011-07-13 13:25:27'),
(13, '00868', '10713', 'TUSBDBASD-562422-HGF', '2011-07-13 13:25:31'),
(14, '00868', '10728', 'TUSBDBASD-562423-HGF', '2011-07-28 13:28:19'),
(15, '00868', '10728', 'TUSBDBASD-562422-HGF', '2011-07-28 13:28:33'),
(16, '00868', '10728', 'TUSBDBASD-562423-HGF', '2011-07-28 13:28:36');
(select date(`date`) as dat,count(*) from `barcode_2d_y` where `date`>'2011-07-25' AND `date`<'2011-08-01' group by date(`date`) asc) t1,
(select date(`date`) as dat,count(*) from `barcode_2d_n` where `date`>'2011-07-25' AND `date`<'2011-08-01' group by date(`date`) asc) t2
WHERE t1.dat=t2.datdat count( * ) dat count( * )
2011-07-28 4 2011-07-28 3
2011-07-31 3 2011-07-31 10 自己调整一下输出字段