有这样一张表:
DROP TABLE IF EXISTS `sheet1`;
CREATE TABLE `sheet1` (
`biaozhi` varchar(6) DEFAULT NULL,
`name` varchar(8) DEFAULT NULL,
`bj` varchar(2) DEFAULT NULL,
`zf` int(11) DEFAULT NULL,
`MC` varchar(2) DEFAULT NULL,
`wl` int(11) DEFAULT NULL,
`wldc` varchar(2) DEFAULT NULL,
`hx` int(11) DEFAULT NULL,
`hxdc` varchar(2) DEFAULT NULL,
`ls` int(11) DEFAULT NULL,
`lsdc` varchar(2) DEFAULT NULL,
`zz` int(11) DEFAULT NULL,
`zzdc` varchar(2) DEFAULT NULL,
`bx` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sheet1` VALUES ('100646', '姓名1', '1', '260', '', '60', '', '61', '', '86', 'B', '96', 'A+', '政史');
INSERT INTO `sheet1` VALUES ('100902', '姓名2', '19', '220', '', '59', '', '89', '', '88', 'B', '95', 'A+', '政史');
INSERT INTO `sheet1` VALUES ('400023', '姓名3', '19', '220', '', '68', '', '69', '', '91', 'A', '91', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100863', '姓名4', '1', '186', '', '90', '', '50', '', '79', 'C', '91', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100830', '姓名5', '1', '262', '', '89', '', '78', '', '80', 'C', '88', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100377', '姓名6', '19', '263', '', '89', '', '68', '', '80', 'C', '87', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100639', '姓名7', '1', '256', '', '90', '', '98', '', '77', 'D', '86', 'B', '政史');
INSERT INTO `sheet1` VALUES ('101678', '姓名8', '1', '264', '', '88', '', '78', '', '88', 'B', '85', 'B', '政史');
INSERT INTO `sheet1` VALUES ('101805', '姓名9', '1', '253', '', '90', '', '87', '', '71', 'D', '85', 'B', '政史');
INSERT INTO `sheet1` VALUES ('101582', '姓名10', '1', '255', '', '42', '', '90', '', '81', 'B', '82', 'C', '政史');
INSERT INTO `sheet1` VALUES ('400031', '姓名11', '1', '186', '', '91', '', '30', '', '80', 'C', '79', 'C', '政史');
INSERT INTO `sheet1` VALUES ('101722', '姓名12', '3', '240', '', '104', 'A+', '77', 'A', '90', '', '50', '', '理化');
INSERT INTO `sheet1` VALUES ('100322', '姓名13', '21', '264', '', '66', 'D', '72', 'B', '60', '', '60', '', '理化');
INSERT INTO `sheet1` VALUES ('100898', '姓名14', '21', '256', '', '91', 'A', '70', 'B', '90', '', '91', '', '理化');
INSERT INTO `sheet1` VALUES ('100780', '姓名15', '3', '263', '', '91', 'A', '64', 'C', '88', '', '98', '', '理化');
INSERT INTO `sheet1` VALUES ('101682', '姓名16', '21', '249', '', '81', 'B', '64', 'C', '98', '', '71', '', '理化');
INSERT INTO `sheet1` VALUES ('100125', '姓名17', '3', '261', '', '86', 'A', '61', 'C', '60', '', '61', '', '理化');
INSERT INTO `sheet1` VALUES ('101784', '姓名18', '21', '256', '', '82', 'B', '60', 'C', '60', '', '62', '', '理化');
INSERT INTO `sheet1` VALUES ('101750', '姓名19', '3', '255', '', '83', 'A', '58', 'D', '67', '', '68', '', '理化');
INSERT INTO `sheet1` VALUES ('101609', '姓名20', '3', '261', '', '69', 'D', '57', 'D', '69', '', '91', '', '理化');
INSERT INTO `sheet1` VALUES ('100076', '姓名21', '21', '261', '', '70', 'B', '46', 'D', '55', '', '89', '', '理化');
按特定的条件进行了排名,但是我觉得有点繁,请高手帮忙简化一下:
SELECT biaozhi,zf,bx,
(SELECT COUNT(zf) FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(wl>=60 and hx>=60 and ((ls>=81 and zz>=80)or(ls>=78 and zz>=85)),zf,zf-480) zzf
FROM sheet1 where bx='政史') c WHERE zf >d.zf)+1 mc
FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(wl>=60 and hx>=60 and ((ls>=81 and zz>=80)or(ls>=78 and zz>=85)),zf,zf-480) zzf
FROM sheet1 where bx='政史') d
UNION ALLSELECT biaozhi,zf,bx,
(SELECT COUNT(zf) FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(ls>=60 and zz>=60 and ((wl>=70 and hx>=60)or(wl>=70 and hx>=70)),zf,zf-480) zzf
FROM sheet1 where bx='理化') c WHERE zf >d.zf)+1 mc
FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(ls>=60 and zz>=60 and ((wl>=70 and hx>=60)or(wl>=70 and hx>=70)),zf,zf-480) zzf
FROM sheet1 where bx='理化') d
ORDER BY bx,mc; UNION ALL前红色部分是一样的,UNION ALL后面红色部分是一样的,这样程序运行肯定效率低,能否高效率一些。谢谢
DROP TABLE IF EXISTS `sheet1`;
CREATE TABLE `sheet1` (
`biaozhi` varchar(6) DEFAULT NULL,
`name` varchar(8) DEFAULT NULL,
`bj` varchar(2) DEFAULT NULL,
`zf` int(11) DEFAULT NULL,
`MC` varchar(2) DEFAULT NULL,
`wl` int(11) DEFAULT NULL,
`wldc` varchar(2) DEFAULT NULL,
`hx` int(11) DEFAULT NULL,
`hxdc` varchar(2) DEFAULT NULL,
`ls` int(11) DEFAULT NULL,
`lsdc` varchar(2) DEFAULT NULL,
`zz` int(11) DEFAULT NULL,
`zzdc` varchar(2) DEFAULT NULL,
`bx` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sheet1` VALUES ('100646', '姓名1', '1', '260', '', '60', '', '61', '', '86', 'B', '96', 'A+', '政史');
INSERT INTO `sheet1` VALUES ('100902', '姓名2', '19', '220', '', '59', '', '89', '', '88', 'B', '95', 'A+', '政史');
INSERT INTO `sheet1` VALUES ('400023', '姓名3', '19', '220', '', '68', '', '69', '', '91', 'A', '91', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100863', '姓名4', '1', '186', '', '90', '', '50', '', '79', 'C', '91', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100830', '姓名5', '1', '262', '', '89', '', '78', '', '80', 'C', '88', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100377', '姓名6', '19', '263', '', '89', '', '68', '', '80', 'C', '87', 'A', '政史');
INSERT INTO `sheet1` VALUES ('100639', '姓名7', '1', '256', '', '90', '', '98', '', '77', 'D', '86', 'B', '政史');
INSERT INTO `sheet1` VALUES ('101678', '姓名8', '1', '264', '', '88', '', '78', '', '88', 'B', '85', 'B', '政史');
INSERT INTO `sheet1` VALUES ('101805', '姓名9', '1', '253', '', '90', '', '87', '', '71', 'D', '85', 'B', '政史');
INSERT INTO `sheet1` VALUES ('101582', '姓名10', '1', '255', '', '42', '', '90', '', '81', 'B', '82', 'C', '政史');
INSERT INTO `sheet1` VALUES ('400031', '姓名11', '1', '186', '', '91', '', '30', '', '80', 'C', '79', 'C', '政史');
INSERT INTO `sheet1` VALUES ('101722', '姓名12', '3', '240', '', '104', 'A+', '77', 'A', '90', '', '50', '', '理化');
INSERT INTO `sheet1` VALUES ('100322', '姓名13', '21', '264', '', '66', 'D', '72', 'B', '60', '', '60', '', '理化');
INSERT INTO `sheet1` VALUES ('100898', '姓名14', '21', '256', '', '91', 'A', '70', 'B', '90', '', '91', '', '理化');
INSERT INTO `sheet1` VALUES ('100780', '姓名15', '3', '263', '', '91', 'A', '64', 'C', '88', '', '98', '', '理化');
INSERT INTO `sheet1` VALUES ('101682', '姓名16', '21', '249', '', '81', 'B', '64', 'C', '98', '', '71', '', '理化');
INSERT INTO `sheet1` VALUES ('100125', '姓名17', '3', '261', '', '86', 'A', '61', 'C', '60', '', '61', '', '理化');
INSERT INTO `sheet1` VALUES ('101784', '姓名18', '21', '256', '', '82', 'B', '60', 'C', '60', '', '62', '', '理化');
INSERT INTO `sheet1` VALUES ('101750', '姓名19', '3', '255', '', '83', 'A', '58', 'D', '67', '', '68', '', '理化');
INSERT INTO `sheet1` VALUES ('101609', '姓名20', '3', '261', '', '69', 'D', '57', 'D', '69', '', '91', '', '理化');
INSERT INTO `sheet1` VALUES ('100076', '姓名21', '21', '261', '', '70', 'B', '46', 'D', '55', '', '89', '', '理化');
按特定的条件进行了排名,但是我觉得有点繁,请高手帮忙简化一下:
SELECT biaozhi,zf,bx,
(SELECT COUNT(zf) FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(wl>=60 and hx>=60 and ((ls>=81 and zz>=80)or(ls>=78 and zz>=85)),zf,zf-480) zzf
FROM sheet1 where bx='政史') c WHERE zf >d.zf)+1 mc
FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(wl>=60 and hx>=60 and ((ls>=81 and zz>=80)or(ls>=78 and zz>=85)),zf,zf-480) zzf
FROM sheet1 where bx='政史') d
UNION ALLSELECT biaozhi,zf,bx,
(SELECT COUNT(zf) FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(ls>=60 and zz>=60 and ((wl>=70 and hx>=60)or(wl>=70 and hx>=70)),zf,zf-480) zzf
FROM sheet1 where bx='理化') c WHERE zf >d.zf)+1 mc
FROM (SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,if(ls>=60 and zz>=60 and ((wl>=70 and hx>=60)or(wl>=70 and hx>=70)),zf,zf-480) zzf
FROM sheet1 where bx='理化') d
ORDER BY bx,mc; UNION ALL前红色部分是一样的,UNION ALL后面红色部分是一样的,这样程序运行肯定效率低,能否高效率一些。谢谢
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货