select
c.difficult_level,
c.a/d.b
from
(
select
question_detail.difficult_level,
count(question_practice_detail.result) a
from question_practice_detail inner join user_profile on question_practice_detail.device_id = user_profile.device_id join question_detail on question_practice_detail.question_id = question_detail.question_id
where user_profile.university = '浙江大学' and question_practice_detail.result = 'right'
group by user_profile.university ,question_detail.difficult_level
) c
inner join
(
select
question_detail.difficult_level,
count(question_practice_detail.result) b
from question_practice_detail inner join user_profile on question_practice_detail.device_id = user_profile.device_id join question_detail on question_practice_detail.question_id = question_detail.question_id
where user_profile.university = '浙江大学'
group by user_profile.university ,question_detail.difficult_level
)d
on c.difficult_level = d.difficult_level
order by c.a/d.b desc
其结果显示为：
hard 1
mediu 1
easy 0.4286
换成升序排序
select
c.difficult_level,
c.a/d.b
from
(
select
question_detail.difficult_level,
count(question_practice_detail.result) a
from question_practice_detail inner join user_profile on question_practice_detail.device_id = user_profile.device_id join question_detail on question_practice_detail.question_id = question_detail.question_id
where user_profile.university = '浙江大学' and question_practice_detail.result = 'right'
group by user_profile.university ,question_detail.difficult_level
) c
inner join
(
select
question_detail.difficult_level,
count(question_practice_detail.result) b
from question_practice_detail inner join user_profile on question_practice_detail.device_id = user_profile.device_id join question_detail on question_practice_detail.question_id = question_detail.question_id
where user_profile.university = '浙江大学'
group by user_profile.university ,question_detail.difficult_level
)d
on c.difficult_level = d.difficult_level
order by c.a/d.b asc
其结果显示：
medium 0.6667
hard 1
为什么不同排序方式的输出还不一样呢？排序不是对已经形成的表进行排序吗？
建议截图证明。
另外,对于某些数据库,除数为0时,也会有部分显示结果,但结果显示是不完整的。
因此你可以先不加这个除法,把结果都查出来看看,看看这个b里面是否存在为0的记录。
还有,不排除碰上了bug的情况,但这个概率是微乎其微