这个sql语句很难啊，求高手解答

Question

现在数据库中有一个字段含有多个名字，然后要做统计，实在想不出怎么写sql语句
比如table1中有两个个字段叫class和pass
class里面记录了课程，pass里面记录了所有通过该class的学生比如：
class         pass
 1           张三;李四;王麻子
 2           黎明;刘德华;张学友
 3           张三;张学友
 .           .....
 .           .....
现在要统计，通过课程最多的前5个，应该怎么写啊pass里面的人使用分号隔开的，有几个难点，感觉，首先对每一条记录里的pass字段，怎么用；将其分割，其次每个记录里pass里的；的个数是不一样的，好难啊，感觉，能实现吗

Answer 1

length(pass)-length(replace(pass,';',''))+1

Answer 2

测试一下直接这一个语句测试不了，我不太理解为什么用length函数

Answer 3

select length(pass)-length(replace(pass,';',''))+1 from tt

Answer 4

可以基于MYSQL的 instr函数（   instr('张三;李四;王麻子','张三')>0 或  instr('张三;李四;王麻子','李四')=0 ）做两表连接(inner join)查询 实现 。

Answer 5

不知道'pass'字段下的人名是怎么来的 ，一般来讲你会有个人员信息表 比如是test2_name 原给定的表为test2
下面可能是你要的结果 分组统计每人完成课程数自己写create table test2 (calss NUMERIC,pass CHAR (100) );
insert into test2 values(1,'张三;李四;王麻子');
insert into test2 values(2,'黎明;刘德华;张学友');
insert into test2 values (3,'张三;张学友');create table test2_name(id  INT(8) NOT NULL  AUTO_INCREMENT, PRIMARY KEY(`id`),name char(20));
insert into test2_name (name) values ('张三');
insert into test2_name (name) values ('李四');
insert into test2_name (name) values ('王麻子');
insert into test2_name (name) values ('黎明');
insert into test2_name (name) values ('刘德华');
insert into test2_name (name) values ('张学友');select t.calss,n.name from test2 t inner join test2_name n on instr( t.pass,n.name)>0

Answer 6

这个只是统计了每一条记录相应字段里姓名的个数，并没有得到我想要的结果最后一段 order 一下不就结了？select class,pass,length(pass)-length(replace(pass,';',''))+1 a from tt
order by a desc

Answer 7

这个只是统计了每一条记录相应字段里姓名的个数，并没有得到我想要的结果最后一段 order 一下不就结了？select class,pass,length(pass)-length(replace(pass,';',''))+1 a from tt
order by a desc
没有错，思路已经有了，难道要把代码完整写出来？

Answer 8

我的想法是用存储过程进行分割，试了一下，ok的。
具体如下：（1）、原表：
CREATE TABLE `tab_1` (
  `class` int(11) NOT NULL AUTO_INCREMENT,
  `pass` varchar(255) NOT NULL,
  PRIMARY KEY (`class`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
insert  into `tab_1`(`class`,`pass`) values (1,'张三;李四;王五'),(2,'张三'),(3,'张三;李四;王五'),(4,'张三;李四;王五'),(5,'张三;李四;王五'),(6,'王五;赵二'),(7,'赵二;钱大;孙子;周瑜;伍子胥'),(8,'赵二;钱大;孙子;周瑜;伍子胥;王五'),(9,'王五;赵二;孙子;周瑜;伍子胥'),(10,'张三;钱大;孙子;周瑜');（2）、分割后的表：CREATE TABLE `tab_2` (
  `class` int(11) NOT NULL,
  `passname` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;（3）、存储过程：
/* Procedure structure for procedure `sp_1` *//*!50003 DROP PROCEDURE IF EXISTS  `sp_1` */;DELIMITER $$/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`()
BEGIN    DECLARE classid INT;    DECLARE passStr VARCHAR(255);    DECLARE passname VARCHAR(30);    DECLARE STOP INT DEFAULT 0;    DECLARE cur1 CURSOR FOR SELECT class,pass FROM tab_1;     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP=1;    truncate table tab_2;   OPEN cur1;    SET classid=NULL,passStr=NULL,passname=NULL;    FETCH cur1 INTO classid,passStr;        WHILE STOP <> 1 DO        IF passStr IS NULL THEN         SET passname=NULL;        ELSE               IF LOCATE(";",passStr)<>0 THEN          SET passname=SUBSTRING(passStr,1,LOCATE(";",passStr)-1);         SET passStr=SUBSTRING(passStr,LOCATE(";",passStr)+1);       ELSE          SET passname=passStr;         SET passStr=NULL;        END IF;       END IF;        WHILE passname IS NOT NULL DO        INSERT INTO tab_2(class,passname)VALUES(classid,passname);               IF passStr IS NULL THEN         SET passname=NULL;        ELSE               IF LOCATE(";",passStr)<>0 THEN          SET passname=SUBSTRING(passStr,1,LOCATE(";",passStr)-1);         SET passStr=SUBSTRING(passStr,LOCATE(";",passStr)+1);       ELSE          SET passname=passStr;         SET passStr=NULL;        END IF;               END IF;              END WHILE;         SET classid=NULL,passStr=NULL,passname=NULL;    FETCH cur1 INTO classid,passStr;    END WHILE;      CLOSE cur1; END */$$
DELIMITER ;
（4）、调用存储过程： call sp_1();
（5）、查看汇总：
SELECT passname,COUNT(1) FROM tab_2 GROUP BY passname HAVING COUNT(1)>=5;

Answer 9

（5）、查看汇总：
应该是这个，看错了：
SELECT passname,COUNT(1) AS num FROM tab_2 GROUP BY passname  ORDER BY num DESC LIMIT 5

Answer 10

1、游标遍历源表数据
2、将pass字段按;分割
3、将分割后的数据插入临时表，
   结构如下：
   1   张三
   1   李四
   1   王麻子
   2   黎明
   2   刘德华
   2   张学友
4、简单的查询临时表，就可以统计出来了

Answer 11

如果有一个花名册table2，统计工作就会好作了:
SELECT A.stname, count(B.classid) AS stcnt
FROM table2 A INNER JOIN table1 B ON LOCATE(CONCAT(';',A.stname,';'), CONCAT(';',B.passed,';'))>0
GROUP BY A.stname
ORDER BY stcnt DESC
LIMIT 5如果没有花名册，可以用存储过程分解出来。