新手求助：如何根据条件进行排名

Question

有这样一张表：
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', '', '理化');
排名要求：1、把学生总分（zf）依据理化与政史分开排名。2、在理化班中，wldc与hxdc必需达1B1C以上（注：不分是物理还是化学是哪个是B哪个是C）且ls和zz必需达60分以上才能排名，没有达到1B1C的接在1B1C之后再依zf由高到低进行排名；政史班也是如此，lsdc与zzdc必需达1B1C以上（注：不分是lsdc还是zzdc是哪个是B哪个是C）且wl和hx必需达60分以上才能排名，没有达到1B1C的接在1B1C之后再依zf由高到低进行排名。以上1和2两个条件要求同时达到。请问如何进行排名。谢谢

Answer 1

按照你提供的测试数据，你认为正确的排名结果是什么样？ 
 (不要高估你的汉语表达能力或者我的汉语理解能力)
   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 2

我用的是mysql5.5,想得到如下结果：
biaozhi name bj zf MC wl wldc hx hxdc ls lsdc zz zzdc bx
101678 姓名8 1 264 1 88 78 88 B 85 B 政史
100377 姓名6 19 263 2 89 68 80 C 87 A 政史
100830 姓名5 1 262 3 89 78 80 C 88 A 政史
100646 姓名1 1 260 4 60 61 86 B 96 A+ 政史
100639 姓名7 1 256 5 90 98 77 D 86 B 政史
101582 姓名10 1 255 6 42 90 81 B 82 C 政史
101805 姓名9 1 253 7 90 87 71 D 85 B 政史
400023 姓名3 19 220 8 68 69 91 A 91 A 政史
100902 姓名2 19 220 8 59 89 88 B 95 A+ 政史
100863 姓名4 1 186 9 90 50 79 C 91 A 政史
400031 姓名11 1 186 9 91 30 80 C 79 C 政史
100898 姓名14 21 256 1 91 A 70 B 90 91 理化
100780 姓名15 3 263 2 91 A 64 C 88 98 理化
101682 姓名16 21 249 3 81 B 64 C 98 71 理化
100125 姓名17 3 261 4 86 A 61 C 60 61 理化
100322 姓名13 21 264 5 66 D 72 B 60 60 理化
101609 姓名20 3 261 6 69 D 57 D 69 91 理化
100076 姓名21 21 261 6 70 B 46 D 55 89 理化
101784 姓名18 21 256 8 82 B 60 C 59 62 理化
101750 姓名19 3 255 9 83 A 58 D 67 68 理化
101722 姓名12 3 240 10 104 A+ 77 A 90 50 理化

Answer 3

上面有点乱：用如下形式吧（也就是省略右侧一部分）
biaozhi name zf MC bj
101678 姓名8 264 1 1
100377 姓名6 263 2 19
100830 姓名5 262 3 1
100646 姓名1 260 4 1
100639 姓名7 256 5 1
101582 姓名10 255 6 1
101805 姓名9 253 7 1
400023 姓名3 220 8 19
100902 姓名2 220 8 19
100863 姓名4 186 9 1
400031 姓名11 186 9 1
100898 姓名14 256 1 21
100780 姓名15 263 2 3
101682 姓名16 249 3 21
100125 姓名17 261 4 3
100322 姓名13 264 5 21
101609 姓名20 261 6 3
100076 姓名21 261 6 21
101784 姓名18 256 8 21
101750 姓名19 255 9 3
101722 姓名12 240 10 3

Answer 4

不好意思，我是在excel中手工排的名，有点错。姓名17应为第3名，姓名16应为第4名。
也就是说：符合条件的按总分从高到低先排，不符合条件的按总分从高到低接着前面符合条件的学生后面再排。
政史班的数据有点巧，符合条件的都是高分，不符合条件的都是低分。但是理化班的数据中“姓名13”总分为264分在理化班中应排第一，但是由于wldc为D，所以就不是第一名，而是接在其它符合条件（wldc和hxdc达到1B1C以上且ls和zz的分数要大于等于60分）的学生后面排名。

Answer 5

版主：我用excel的形式，表述一下，还请您帮忙。本论坛不给上传附件，我在另一个论坛上传了一个附件。为了使数据更能说明问题，这个附件中的数据与本贴中的数据稍有一点不同，不影响排名要求。再次谢谢您。http://club.excelhome.net/forum.php?mod=attachment&aid=MTI2MjkxOXwzMGZkOTA4OXwxMzUwMTgyODU0fDE5NTU0OXw5MzExNjg%3D

Answer 6

就在这个网址 http://club.excelhome.net/thread-931168-1-1.html

Answer 7

换一个想法，把不符合条件的zf变小，然后再排名。由于是新手，只做出让zf变小，但是不会让它再进行排名。
SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf,
if((wl>=60 and hx>=60 and  
bx='政史' and ((ls>=81 and zz>=80)or(ls>=78 and zz>=85)))or
(ls>=60 and zz>=60 and 
bx='理化'  and ((wl>=70 and hx>=60)or(wl>=70 and hx>=70))),zf,zf-480) zzf
FROM sheet1  
ORDER BY bx,zzf desc;这一段话将会产生一个新表，把zf中不符合条件的变小。
排名用这样一段放：
SELECT biaozhi,zf,
(SELECT COUNT(zf) FROM sheet1 WHERE zf >a.zf)+1 mc
FROM sheet1 a
ORDER BY mc; 
请问大侠们，如何把这两句话弄在一起呀。谢谢

Answer 8

结果是这样的：
+---------+------+-----+------+----+------+----+------+----+------+-----+------+
| biaozhi | bx   | wl  | wldc | hx | hxdc | ls | lsdc | zz | zzdc | zf  | zzf  |
+---------+------+-----+------+----+------+----+------+----+------+-----+------+
| 101678  | 政史 |  88 |      | 78 |      | 88 | B    | 85 | B    | 264 |  264 |
| 100377  | 政史 |  89 |      | 68 |      | 80 | C    | 87 | A    | 263 |  263 |
| 100830  | 政史 |  89 |      | 78 |      | 80 | C    | 88 | A    | 262 |  262 |
| 100646  | 政史 |  60 |      | 61 |      | 86 | B    | 96 | A+   | 260 |  260 |
| 400023  | 政史 |  68 |      | 69 |      | 91 | A    | 91 | A    | 220 |  220 |
| 100639  | 政史 |  90 |      | 98 |      | 77 | D    | 86 | B    | 256 | -224 |
| 101582  | 政史 |  42 |      | 90 |      | 81 | B    | 82 | C    | 255 | -225 |
| 101805  | 政史 |  90 |      | 87 |      | 71 | D    | 85 | B    | 253 | -227 |
| 100902  | 政史 |  59 |      | 89 |      | 88 | B    | 95 | A+   | 220 | -260 |
| 100863  | 政史 |  90 |      | 50 |      | 79 | C    | 91 | A    | 186 | -294 |
| 400031  | 政史 |  91 |      | 30 |      | 80 | C    | 79 | C    | 186 | -294 |
| 100780  | 理化 |  91 | A    | 64 | C    | 88 |      | 98 |      | 263 |  263 |
| 100125  | 理化 |  86 | A    | 61 | C    | 60 |      | 61 |      | 261 |  261 |
| 100898  | 理化 |  91 | A    | 70 | B    | 90 |      | 91 |      | 256 |  256 |
| 101682  | 理化 |  81 | B    | 64 | C    | 98 |      | 71 |      | 249 |  249 |
| 100322  | 理化 |  66 | D    | 72 | B    | 60 |      | 60 |      | 264 | -216 |
| 101609  | 理化 |  69 | D    | 57 | D    | 69 |      | 91 |      | 261 | -219 |
| 100076  | 理化 |  70 | B    | 46 | D    | 55 |      | 89 |      | 261 | -219 |
| 101784  | 理化 |  82 | B    | 60 | C    | 59 |      | 62 |      | 256 | -224 |
| 101750  | 理化 |  83 | A    | 58 | D    | 67 |      | 68 |      | 255 | -225 |
| 101722  | 理化 | 104 | A+   | 77 | A    | 90 |      | 50 |      | 240 | -240 |
+---------+------+-----+------+----+------+----+------+----+------+-----+------+
21 rows in set
新手求助，谢谢

Answer 9

试试看：
SELECT biaozhi,bx,wl,wldc,hx,hxdc,ls,lsdc,zz,zzdc,zf 
FROM (SELECT *,'3' AS groupid 
FROM sheet1 
WHERE bx='理化' AND ((wldc<='B+' AND hxdc<='C+') OR (wldc<='C+' AND hxdc<='B+')) AND ls>=60 AND zz>=60 
UNION ALL 
SELECT *,'4' AS groupid 
FROM sheet1 
WHERE bx='理化' AND (hxdc>='C+' OR wldc>='C+' OR ls<60 OR zz<60) 
UNION ALL 
SELECT *,'1' AS groupid 
FROM sheet1 
WHERE bx='政史' AND ((lsdc<='B+' AND zzdc<='C+') OR (lsdc<='C+' AND zzdc<='B+')) AND wl>=60 AND hx>=60 
UNION ALL 
SELECT *,'2' AS groupid 
FROM sheet1 
WHERE bx='政史' AND (lsdc>='C+' OR zzdc>='C+' OR wl<60 OR hx<60)) tmp 
ORDER BY groupid,zf DESC,wldc,hxdc,lsdc,zzdc ; 

Answer 10

谢谢mysdzlt2007，这只是排出了顺序，“学生名次”如何加上去呢？