我想求一个packageno,当同一个packageno的状态都为‘Y’时且个数>=16,则显示packageno如果同一的状态'Y'的packageno个数<16时,也显示packno这两个sql语句怎么写?
按照packageno显示。数据表和部分数据如下:CREATE TABLE IF NOT EXISTS `barcode_2d` (
`ID` int(20) NOT NULL AUTO_INCREMENT,
`username` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`packageno` varchar(50) NOT NULL,
`lotno` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(50) NOT NULL,
`barcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`date` datetime NOT NULL,
`status` varchar(5) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;--
-- 转存表中的数据 `barcode_2d`
--INSERT INTO `barcode_2d` (`ID`, `username`, `packageno`, `lotno`, `description`, `barcode`, `date`, `status`) VALUES
(1, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:10', 'Y'),
(16, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:10', 'Y'),
(15, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:06', 'Y'),
(14, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:02', 'Y'),
(13, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:58', 'Y'),
(12, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:54', 'Y'),
(11, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:50', 'Y'),
(10, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:46', 'Y'),
(9, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:42', 'Y'),
(8, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:38', 'Y'),
(2, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:14', 'Y'),
(3, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:18', 'Y'),
(4, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:21', 'Y'),
(5, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:25', 'Y'),
(6, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:29', 'Y'),
(7, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:33', 'Y'),
(17, '00812', 'CVT110822002', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:24', 'Y'),
(18, '00812', 'CVT110823001', '10823', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-23 14:48:33', 'N'),
(19, '00812', 'CVT110824001', '10824', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-24 15:16:09', 'N');
按照packageno显示。数据表和部分数据如下:CREATE TABLE IF NOT EXISTS `barcode_2d` (
`ID` int(20) NOT NULL AUTO_INCREMENT,
`username` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`packageno` varchar(50) NOT NULL,
`lotno` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(50) NOT NULL,
`barcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`date` datetime NOT NULL,
`status` varchar(5) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;--
-- 转存表中的数据 `barcode_2d`
--INSERT INTO `barcode_2d` (`ID`, `username`, `packageno`, `lotno`, `description`, `barcode`, `date`, `status`) VALUES
(1, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:10', 'Y'),
(16, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:10', 'Y'),
(15, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:06', 'Y'),
(14, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:02', 'Y'),
(13, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:58', 'Y'),
(12, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:54', 'Y'),
(11, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:50', 'Y'),
(10, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:46', 'Y'),
(9, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:42', 'Y'),
(8, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:38', 'Y'),
(2, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:14', 'Y'),
(3, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:18', 'Y'),
(4, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:21', 'Y'),
(5, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:25', 'Y'),
(6, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:29', 'Y'),
(7, '00812', 'CVT110822001', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:47:33', 'Y'),
(17, '00812', 'CVT110822002', '10822', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-22 14:48:24', 'Y'),
(18, '00812', 'CVT110823001', '10823', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-23 14:48:33', 'N'),
(19, '00812', 'CVT110824001', '10824', 'CVT', 'KAETC56-600NC1161656600N31036 KA62A0001', '2011-08-24 15:16:09', 'N');
ID username packageno lotno description barcode date status count(*)
1 00812 CVT110822001 10822 CVT KAETC56-600NC1161656600N31036 KA62A0001 2011-08-22 14:47:10 Y 16
17 00812 CVT110822002 10822 CVT KAETC56-600NC1161656600N31036 KA62A0001 2011-08-22 14:48:24 Y 1
我想求得同一个packageno且status=Y count>=16的这个值。
你加个 HAVING 子句不是就有了吗?