题解 | #统计每个学校的答过题的用户的平均答题数#

说明：本文内容不是题解，是一个求助的文章。

问题是：两个逻辑一样的查询sql，一个能完成除法运算，一个不能。

我知道两句sql都不是题解，但是这个除法不生效的现象就很奇怪，本人学艺不精，想问问有没有知道原因的大佬，请各位大神指教。

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);
CREATE TABLE `question_practice_detail` (
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
INSERT INTO user_profile VALUES(1234,'male',21,'北京大学',3.4,6);
INSERT INTO user_profile VALUES(1235,'male',null,'复旦大学',4.0,15);
INSERT INTO user_profile VALUES(1236,'female',20,'北京大学',3.2,3);
INSERT INTO user_profile VALUES(1237,'female',23,'浙江大学',3.6,9);
INSERT INTO user_profile VALUES(1238,'male',25,'山东大学',3.9,25);
INSERT INTO user_profile VALUES(1239,'male',25,'清华大学',3.2,5);
INSERT INTO user_profile VALUES(1240,'male',null,'清华大学',3.6,8);
INSERT INTO user_profile VALUES(1241,'female',null,'北京理工大学',3.7,12);
INSERT INTO question_practice_detail VALUES(1234,111,'wrong');
INSERT INTO question_practice_detail VALUES(1234,112,'wrong');
INSERT INTO question_practice_detail VALUES(1235,113,'wrong');
INSERT INTO question_practice_detail VALUES(1236,111,'right');
INSERT INTO question_practice_detail VALUES(1236,115,'right');
INSERT INTO question_practice_detail VALUES(1237,116,'right');
INSERT INTO question_practice_detail VALUES(1237,117,'wrong');
INSERT INTO question_practice_detail VALUES(1238,117,'wrong');
INSERT INTO question_practice_detail VALUES(1239,112,'wrong');
INSERT INTO question_practice_detail VALUES(1239,111,'right');
INSERT INTO question_practice_detail VALUES(1239,113,'wrong');

-- 没有执行除法的异常sql
SELECT
	t3.university,
	count(*) AS '文章数据',
	( SELECT count(*) FROM user_profile WHERE university = t3.university ) AS '用户数',
	-- 这里的除法没有执行，为什么没有执行呢
	( count(*) / ( SELECT count(*) FROM user_profile WHERE university = t3.university ) ) AS avg_answer_cnt,
	( SELECT DISTINCT university FROM user_profile WHERE university = t3.university )
FROM
	question_practice_detail t4
	JOIN (
	SELECT
		t1.device_id,
		t1.university 
	FROM
		user_profile t1
		JOIN ( SELECT DISTINCT university FROM user_profile ) t2 ON t1.university = t2.university 
	) t3 ON t3.device_id = t4.device_id 
GROUP BY
	t3.university;

-- 正常执行了除法的sql
SELECT
	t3.university,
	count(*) AS '文章数据',
	( SELECT count(*) FROM user_profile WHERE university = t3.university ) AS '用户数' ,
	( count(*) / ( SELECT count(*) FROM user_profile WHERE university = t3.university ) ) AS avg_answer_cnt,
	( SELECT DISTINCT university FROM user_profile WHERE university = t3.university )
FROM
	question_practice_detail t4
	JOIN user_profile t3 ON t3.device_id = t4.device_id 
GROUP BY
	t3.university;