SELECT a.number * 100 / b.number AS 比例
FROM (SELECT COUNT(*) AS number FROM EC_ROLE WHERE (GRADE >= 25) AND (GRADE <= 30) AND (SCHOOL = '0’)AND (ID IN (SELECT t1.ROLE_ID FROM EC_ROLE_TASK AS t1 INNER JOIN EC_TASK AS t2 ON t1.TASK_ID = t2.ID
WHERE (t2.GRADE >= 15) AND (t2.GRADE <= 20) AND (t1.TASK_STATE = '3')))) AS a ,
(SELECT COUNT(*) AS number FROM EC_ROLE AS EC_ROLE_1 WHERE (GRADE >= 25) AND (GRADE <= 30) AND (SCHOOL = ' 0')) AS b
FROM (SELECT COUNT(*) AS number FROM EC_ROLE WHERE (GRADE >= 25) AND (GRADE <= 30) AND (SCHOOL = '0’)AND (ID IN (SELECT t1.ROLE_ID FROM EC_ROLE_TASK AS t1 INNER JOIN EC_TASK AS t2 ON t1.TASK_ID = t2.ID
WHERE (t2.GRADE >= 15) AND (t2.GRADE <= 20) AND (t1.TASK_STATE = '3')))) AS a ,
(SELECT COUNT(*) AS number FROM EC_ROLE AS EC_ROLE_1 WHERE (GRADE >= 25) AND (GRADE <= 30) AND (SCHOOL = ' 0')) AS b
第一张表为ec_role(角色表)字段如下
id name(姓名) sex(性别) grade(角色等级等级) school(帮派)
第二张表为ec_task(任务表) 字段如下 id title(任务名称) grade(任务等级) 第三张表为ec_role_task(角色任务表) 字段如下
role_id task_id task_state(状态) 其中0表示未完成 3表示完成
求完成15-20级所有任务的25-30级角色占25-30级总角色数的比例按帮派来分
表ec_role,其中school为帮派,grade为角色等级Role_Id Nickname School Grade
10898 张三 0 8
10899 李四 1 4
10900 王五 0 1
10901 马六 0 13
10902 小zhang 0 21
10903 小李 1 44
10904 Xiaowen 0 80表ec_task,其中grade为任务等级Task_Id Title Grade
760 调查xiaow 30
761 Fsadfsa 55
762 Fasfsad 55
763 Fsaf 5
764 Qingjfsf 10
765 为民除害 15表ec_role_task为中间表,其中task_state为完成状态,3表示完成,1表示未完成Role_id Task_id Task_state
10898 760 3
10899 761 3
10900 764 1
10904 765 1要求:完成15-20级所有任务的25-30级角色占25-30级总角色数的比例,按帮派(school)来分组