SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `table2`
-- ----------------------------
DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
`record_id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(20) DEFAULT NULL,
`myd_code_name` varchar(50) DEFAULT NULL COMMENT '回访名称',
`myd_is_true` char(2) DEFAULT NULL COMMENT '值',
PRIMARY KEY (`record_id`),
KEY `idx_t_gd_az_zp_cust_myd` (`zp_no`,`myd_code_name`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1462 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of table2
-- ----------------------------
INSERT INTO `table2` VALUES ('3', '100519132835001', '试机', '是');
INSERT INTO `table2` VALUES ('4', '100519132835001', '清理现场', '是');
INSERT INTO `table2` VALUES ('5', '100519132835002', '试机', '');
INSERT INTO `table2` VALUES ('6', '100519132835002', '清理现场', '是');
INSERT INTO `table2` VALUES ('8', '100519132835003', '清理现场', '否');
INSERT INTO `table2` VALUES ('7', '100519132835003', '试机', '是');mysql怎样实现,只查找出 都有值的 zp_no
上述数据,希望查询出结果如下:
zp_no
100519132835001
100519132835003
-- ----------------------------
-- Table structure for `table2`
-- ----------------------------
DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
`record_id` int(11) NOT NULL AUTO_INCREMENT,
`zp_no` varchar(20) DEFAULT NULL,
`myd_code_name` varchar(50) DEFAULT NULL COMMENT '回访名称',
`myd_is_true` char(2) DEFAULT NULL COMMENT '值',
PRIMARY KEY (`record_id`),
KEY `idx_t_gd_az_zp_cust_myd` (`zp_no`,`myd_code_name`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1462 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of table2
-- ----------------------------
INSERT INTO `table2` VALUES ('3', '100519132835001', '试机', '是');
INSERT INTO `table2` VALUES ('4', '100519132835001', '清理现场', '是');
INSERT INTO `table2` VALUES ('5', '100519132835002', '试机', '');
INSERT INTO `table2` VALUES ('6', '100519132835002', '清理现场', '是');
INSERT INTO `table2` VALUES ('8', '100519132835003', '清理现场', '否');
INSERT INTO `table2` VALUES ('7', '100519132835003', '试机', '是');mysql怎样实现,只查找出 都有值的 zp_no
上述数据,希望查询出结果如下:
zp_no
100519132835001
100519132835003
+-----------+-----------------+---------------+-------------+
| record_id | zp_no | myd_code_name | myd_is_true |
+-----------+-----------------+---------------+-------------+
| 3 | 100519132835001 | 试机 | 是 |
| 4 | 100519132835001 | 清理现场 | 是 |
| 5 | 100519132835002 | 试机 | |
| 6 | 100519132835002 | 清理现场 | 是 |
| 8 | 100519132835003 | 清理现场 | 否 |
| 7 | 100519132835003 | 试机 | 是 |
+-----------+-----------------+---------------+-------------+
6 rows in set (0.05 sec)mysql>mysql> select distinct zp_no
-> from table2 b
-> where not exists (select 1 from table2 where zp_no=b.zp_no and myd_is_true='' or myd_is_true is null);
+-----------------+
| zp_no |
+-----------------+
| 100519132835001 |
| 100519132835003 |
+-----------------+
2 rows in set (0.05 sec)mysql>