6,108
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `test1` (
`sbid` varchar(255) DEFAULT NULL,
`Ename` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL
)
INSERT INTO `test1` VALUES ('JS.1', '甲1', '厂家2');
INSERT INTO `test1` VALUES ('JS.2', '甲2', '厂家1');
INSERT INTO `test1` VALUES ('JS.3', '甲3', '厂家1');
INSERT INTO `test1` VALUES ('JS.4', '甲4', '厂家2');
INSERT INTO `test1` VALUES ('JS.5', '乙1', '厂家2');
INSERT INTO `test1` VALUES ('HN.1', '甲11', '厂家1');
INSERT INTO `test1` VALUES ('HN.2', '乙2', '厂家1');
INSERT INTO `test1` VALUES ('HN.3', '乙3', '厂家1');
INSERT INTO `test1` VALUES ('HN.4', '乙8', '厂家1');
DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
`Ename` varchar(255) DEFAULT NULL
)
INSERT INTO `test2` VALUES ('甲1');
INSERT INTO `test2` VALUES ('甲2');
INSERT INTO `test2` VALUES ('甲3');
INSERT INTO `test2` VALUES ('甲4');
INSERT INTO `test2` VALUES ('甲5');
INSERT INTO `test2` VALUES ('甲6');
INSERT INTO `test2` VALUES ('甲7');
INSERT INTO `test2` VALUES ('甲8');
DROP TABLE IF EXISTS `test3`;
CREATE TABLE `test3` (
`Ename` varchar(255) DEFAULT NULL
)
INSERT INTO `test3` VALUES ('乙1');
INSERT INTO `test3` VALUES ('乙2');
INSERT INTO `test3` VALUES ('乙3');
INSERT INTO `test3` VALUES ('乙4');
INSERT INTO `test3` VALUES ('乙5');
mysql> select left(sbid ,2),
-> Count(b.Ename)/count(*) as `总录入率`,
-> Sum(if(type ='厂家1' and b.Ename is not null ,1,0))/sum(if(type ='厂家1',1,0)) as `厂家1`,
-> Sum(if(type ='厂家2' and b.Ename is not null ,1,0))/sum(if(type ='厂家2',1,0)) as `厂家2`
-> from test1 a left join
-> (
-> select Ename from test2
-> Union All
-> select Ename from test3
-> ) b on a.Ename =b.Ename
-> group by Left(sbid ,2);
+---------------+----------+--------+--------+
| left(sbid ,2) | 总录入率 | 厂家1 | 厂家2 |
+---------------+----------+--------+--------+
| HN | 0.5000 | 0.5000 | NULL |
| JS | 1.0000 | 1.0000 | 1.0000 |
+---------------+----------+--------+--------+
2 rows in set (0.06 sec)