sql查询有关问题

求一个sql查询问题

SQL code

select `ID`,`rkno` from `rk_check` where hl_status = '1' group by rkno order by rkno asc表字段：CREATE TABLE IF NOT EXISTS `rk_check` (  `ID` int(11) NOT NULL AUTO_INCREMENT,  `rkno` varchar(20) NOT NULL,  `packageno` varchar(20) NOT NULL,  `hl_status` tinyint(1) DEFAULT NULL,  `hl_user` varchar(40) NOT NULL,  `pm_status` tinyint(1) DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=MyISAMINSERT INTO `rk_check` (`ID`, `rkno`, `packageno`, `hl_status`, `hl_user`, `pm_status`) VALUES(71, '201202060005', 'CVT120206034', 1, 'admin', NULL),(70, '201202060005', 'CVT120206035', 1, 'admin', NULL),(69, '201202060005', 'CVT120206036', NULL, '', NULL),(68, '201202060005', 'CVT120206037', NULL, '', NULL),(67, '201202060005', 'CVT120206038', NULL, '', NULL),(66, '201202070006', 'CVT120207037', 1, 'admin', NULL),(72, '201202060005', 'CVT120206022', 1, 'admin', NULL);

一个rkno字段对应多个packageno字段。现在我想求同一个rkno对应的hl_status的值都为1的数据筛选出来。求对应修改的sql语句。

像例中的筛选结果只显示rkno为201202070006数据的值。



[解决办法]
SELECT * FROM `rk_check` a1 WHERE NOT EXISTS(SELECT 1 FROM `rk_check` WHERE a1.`rkno`=`rkno` AND IFNULL(`hl_status`,2)<>1)

[解决办法]
假设 a1.`rkno`='201202060005'

满足a1.`rkno`=`rkno`的记录
(71, '201202060005', 'CVT120206034', 1, 'admin', NULL),
(70, '201202060005', 'CVT120206035', 1, 'admin', NULL),
(69, '201202060005', 'CVT120206036', NULL, '', NULL),
(68, '201202060005', 'CVT120206037', NULL, '', NULL),
(67, '201202060005', 'CVT120206038', NULL, '', NULL),
(72, '201202060005', 'CVT120206022', 1, 'admin', NULL);
满足a1.`rkno`=`rkno` AND IFNULL(`hl_status`,2)<>1的记录
(69, '201202060005', 'CVT120206036', NULL, '', NULL),
(68, '201202060005', 'CVT120206037', NULL, '', NULL),
(67, '201202060005', 'CVT120206038', NULL, '', NULL),
返回3条记录 ，NOT EXISTS返回FLASE

查看更多 下一篇