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提供两张表的数据:--
-- 表的结构 `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');
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 count(*) from `barcode_2d_n` where `date`>'2011-07-25' AND `date`<'2011-08-01'
and date(a.`date`)=date(`date`) and a.`id`=id) as cn
from `barcode_2d_y` a where a.`date`>'2011-07-25' AND a.`date`<'2011-08-01' group by date(a.`date`)
date(a.`date`) count(*) cn
2011-07-28 4 0
2011-07-31 3 1