SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `table1`
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`zp_no` varchar(20) DEFAULT NULL COMMENT '指派单号',
`hf_pro_myd_score` varchar(20) DEFAULT NULL COMMENT '满意度分数',
`hf_myd_score` varchar(1000) DEFAULT NULL COMMENT '用户反馈',
PRIMARY KEY (`record_id`),
KEY `gd_no` (`zp_no`) USING BTREE,
KEY `IX_zp_no` (`zp_no`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=14358 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES ('1', '100519132835001', null, null);
INSERT INTO `table1` VALUES ('2', '100519132835002', '4', '');
INSERT INTO `table1` VALUES ('3', '100519132835003', '3', '5');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', '试机', '是');
上面有2个表,存在一对多 关系zp_nomysql 如何实现,统计hf_pro_myd_score、hf_myd_score和myd_is_true 都不为空的记录,有一个没有值,都不参与计数统计,结果如下说明:myd_is_true 的值为“是”,则计数+1,为“否”,则+0 序号 记录数 试机 清理现场
100519132835003 1 1 0
-- ----------------------------
-- Table structure for `table1`
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`zp_no` varchar(20) DEFAULT NULL COMMENT '指派单号',
`hf_pro_myd_score` varchar(20) DEFAULT NULL COMMENT '满意度分数',
`hf_myd_score` varchar(1000) DEFAULT NULL COMMENT '用户反馈',
PRIMARY KEY (`record_id`),
KEY `gd_no` (`zp_no`) USING BTREE,
KEY `IX_zp_no` (`zp_no`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=14358 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES ('1', '100519132835001', null, null);
INSERT INTO `table1` VALUES ('2', '100519132835002', '4', '');
INSERT INTO `table1` VALUES ('3', '100519132835003', '3', '5');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', '试机', '是');
上面有2个表,存在一对多 关系zp_nomysql 如何实现,统计hf_pro_myd_score、hf_myd_score和myd_is_true 都不为空的记录,有一个没有值,都不参与计数统计,结果如下说明:myd_is_true 的值为“是”,则计数+1,为“否”,则+0 序号 记录数 试机 清理现场
100519132835003 1 1 0
+-----------+-----------------+------------------+--------------+
| record_id | zp_no | hf_pro_myd_score | hf_myd_score |
+-----------+-----------------+------------------+--------------+
| 1 | 100519132835001 | NULL | NULL |
| 2 | 100519132835002 | 4 | |
| 3 | 100519132835003 | 3 | 5 |
+-----------+-----------------+------------------+--------------+
3 rows in set (0.00 sec)mysql> select * from table2;
+-----------+-----------------+---------------+-------------+
| 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.00 sec)mysql> select a.zp_no,
-> count(*) as `记录数`,
-> sum(if(myd_code_name='试机',1,0)) as `试机`,
-> sum(if(myd_code_name='清理现场',1,0)) as `清理现场`
-> from table1 a inner join table2 b on a.zp_no=b.zp_no
-> where hf_pro_myd_score is not null and hf_pro_myd_score != ''
-> and hf_myd_score is not null and hf_myd_score != ''
-> and myd_is_true='是'
-> group by a.zp_no;
+-----------------+--------+------+----------+
| zp_no | 记录数 | 试机 | 清理现场 |
+-----------------+--------+------+----------+
| 100519132835003 | 1 | 1 | 0 |
+-----------------+--------+------+----------+
1 row in set (0.03 sec)mysql>
-- ----------------------------
-- 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', '试机', '是');2楼怎样实现,只查找出 都有值的 zp_no
上述数据,希望查询出结果如下:
zp_no
100519132835001
100519132835003