select `ID`,`rkno` ,packageno from `rk_check` where hl_status = '1'order by rkno asc
例中的201202060005不显示的原因:hl_status的值不是全为1,有null的情况。
你的结果没问题啊 mysql> select `ID`,`rkno` from `rk_check` where hl_status = '1' group by rkno or der by rkno asc; +----+--------------+ | ID | rkno | +----+--------------+ | 71 | 201202060005 | | 66 | 201202070006 | +----+--------------+ 2 rows in set (0.05 sec)
貌似hl_status=NULL有点问题,用not exists 试试: SELECT * FROM rk_check WHERE rkno NOT IN (SELECT rkno FROM rk_check WHERE ID not IN (SELECT ID FROM rk_check WHERE hl_status=1)) GROUP BY rkno ORDER BY rkno asc; 结果:
not inSELECT * FROM rk_check WHERE rkno NOT IN (SELECT rkno FROM rk_check WHERE ID NOT IN (SELECT ID FROM rk_check WHERE hl_status=1)) GROUP BY rkno ORDER BY rkno asc;
der by rkno asc;
+----+--------------+
| ID | rkno |
+----+--------------+
| 71 | 201202060005 |
| 66 | 201202070006 |
+----+--------------+
2 rows in set (0.05 sec)
结果不是我想要的啊。我要的是rkno对应hl_status全为1的结果啊。
例中要得到rkno字段为201202070006的结果啊。201202060005结果不符合,因为对应的结果有null值啊。
(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
SELECT * FROM rk_check WHERE rkno NOT IN (SELECT rkno FROM rk_check WHERE ID not IN (SELECT ID FROM rk_check WHERE hl_status=1)) GROUP BY rkno ORDER BY rkno asc;
结果: