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', '1001', '试机', '是');
INSERT INTO `table2` VALUES ('4', '1001', '清理现场', '是');
INSERT INTO `table2` VALUES ('5', '1002', '试机', '');
INSERT INTO `table2` VALUES ('6', '1002', '清理现场', '是');
INSERT INTO `table2` VALUES ('8', '1003', '清理现场', '否');
INSERT INTO `table2` VALUES ('7', '1003', '试机', '');mysql怎样实现,只查找出 都有空值或‘’的 zp_no
上述数据,希望查询出结果如下:
zp_no
1002
1003
-- ----------------------------
-- 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', '1001', '试机', '是');
INSERT INTO `table2` VALUES ('4', '1001', '清理现场', '是');
INSERT INTO `table2` VALUES ('5', '1002', '试机', '');
INSERT INTO `table2` VALUES ('6', '1002', '清理现场', '是');
INSERT INTO `table2` VALUES ('8', '1003', '清理现场', '否');
INSERT INTO `table2` VALUES ('7', '1003', '试机', '');mysql怎样实现,只查找出 都有空值或‘’的 zp_no
上述数据,希望查询出结果如下:
zp_no
1002
1003
解决方案 »
- 特殊字符前后的字符串提取
- windows下mysqldump存储过程的问题
- 5/27/2010 8:04:52 PM 这样格式的时间怎么插入到MySql数据库里面的一个DateTime字段里面去.
- Mysql query returned no resultset
- vc 2005 中怎样使用mysql
- MySQL和数据仓库数据库选择的问题
- MySQL实现双向同步,但其中的一台机器需要经常的重起,关闭这该如何办呢?
- mysql启动时的1067错误如何解决啊?
- 好象有PHP--Apache--Mysql在一起安装的程序,并且已经完成Win98下的配置,谁知道在哪下载??
- sysbench测试时,CPU值高,内存利用率低是为什么?
- insert语句的询问!
- 求一个SQL语句
`myd_is_true`=''
-- ----------------------------
-- Records of table2
-- ----------------------------
INSERT INTO `table2` VALUES ('3', '1001', '试机', '是');
INSERT INTO `table2` VALUES ('4', '1001', '清理现场', '是');
INSERT INTO `table2` VALUES ('5', '1002', '试机', '');
INSERT INTO `table2` VALUES ('6', '1002', '清理现场', '');
INSERT INTO `table2` VALUES ('8', '1003', '清理现场', '否');
INSERT INTO `table2` VALUES ('7', '1003', '试机', '');mysql怎样实现,只查找出 都是空值或‘’的 zp_no
上述数据,希望查询出结果如下:
zp_no
1002
select zp_no from table2 where `myd_is_true` ='' or `myd_is_true` is null
group by zp_no having count(*)>1;
(SELECT a.zp_no FROM table2 a
WHERE NOT EXISTS(SELECT 1 FROM table2 WHERE a.zp_no=zp_no AND a.myd_is_true ='') ) b
ON a1.zp_no=b.zp_no WHERE b.zp_no IS NULL
select A.zp_no from (
select zp_no,count(*) from table2 where `myd_is_true` ='' or `myd_is_true` is null group by zp_no ) A,
(select zp_no,count(*) from table2 group by zp_no) B
where a.zp_no=b.zp_no and a.`count(*)`=b.`count(*)`;
4楼的是错误的。
如果有3个1002的,其中一个的myd_is_true有值,结果就不对了。
7楼的解决了这个问题。
mysql> select A.zp_no from (
-> select zp_no,count(*) from table2 where `myd_is_true` ='' or `myd_is_true
` is null group by zp_no ) A,
-> (select zp_no,count(*) from table2 group by zp_no) B
-> where a.zp_no=b.zp_no and a.`count(*)`=b.`count(*)`;
+-------+
| zp_no |
+-------+
| 1002 |
+-------+
1 row in set (0.00 sec)mysql>
FROM (
SELECT zp_no, count( * )
FROM table2
WHERE `myd_is_true` = '' OR `myd_is_true` IS NULL
GROUP BY zp_no
)A, (SELECT zp_no, count( * )
FROM table2
GROUP BY zp_no
)B
WHERE a.zp_no = b.zp_no AND a.`count(*)` = b.`count(*)`
LIMIT 0 , 30 MySQL 返回:
#1054 - Unknown column 'a.zp_no' in 'where clause'
FROM (
SELECT zp_no, count( * )
FROM table2
WHERE `myd_is_true` = '' OR `myd_is_true` IS NULL
GROUP BY zp_no
)a, (SELECT zp_no, count( * )
FROM table2
GROUP BY zp_no
)b
WHERE a.zp_no = b.zp_no AND a.`count(*)` = b.`count(*)`
LIMIT 0 , 30 MySQL 返回:
#1054 - Unknown column 'a.count(*)' in 'where clause'