现有用户表user_profile(device_id 设备号, gender 性别, age 年龄, university 学校, gpa 绩点, active_days_within_30 近30天活跃天数, question_cnt 提问数, answer_cnt 答题数)，示例数据如下： 题目练习表question_practice_detail(device_id 设备号, question_id 题目ID, result 答题结果)，示例数据如下： 题目信息表question_detail(question_id 题目ID, difficult_level 题目难度)，示例数据如下： 请输出所有用户对高难度题目的刷题数question_cnt，对于没练过高难度题目的用户输出0，结果按question_cnt升序排序，示例数据输出如下：
示例1

输入

drop table if exists user_profile;
CREATE TABLE `user_profile` (
	`id` int PRIMARY KEY AUTO_INCREMENT,
	`device_id` int NOT NULL,
	`gender` varchar(14) NOT NULL,
	`age` int,
	`university` varchar(32) NOT NULL,
	`gpa` double,
	`active_days_within_30` int ,
	`question_cnt` int ,
	`answer_cnt` int 
);

drop table if exists `question_practice_detail`;
CREATE TABLE `question_practice_detail` (
	`id` int PRIMARY KEY AUTO_INCREMENT,
	`device_id` int NOT NULL,
	`question_id`int NOT NULL,
	`result` varchar(32) NOT NULL
);

drop table if exists `question_detail`;
CREATE TABLE `question_detail` (
	`id` int PRIMARY KEY AUTO_INCREMENT,
	`question_id`int NOT NULL,
	`difficult_level` varchar(32) NOT NULL
);


INSERT INTO user_profile(device_id, gender, age, university, gpa, active_days_within_30, question_cnt, answer_cnt) VALUES
	(3214,'male',23,'北京大学',3.7,15,5,25),
	(2215,'male',23,'北京大学',3.6,5,1,2),
	(6543,'female',22,'北京大学',3.9,12,3,30),
	(2138,'male',21,'北京大学',3.8,7,2,12),
	(2315,'male',22,'复旦大学',3.8,15,7,13);

INSERT INTO question_practice_detail(device_id,question_id,result) VALUES
	(2138,111,'wrong'),
	(3214,112,'wrong'),
	(3214,113,'wrong'),
	(6543,111,'right'),
	(2315,115,'right'),
	(2315,116,'right'),
	(2315,117,'wrong');

INSERT INTO question_detail(question_id,difficult_level) VALUES
	(111,'hard'),
	(112,'medium'),
	(113,'easy'),
	(114,'hard'),
	(115,'easy'),
	(116,'hard'),
	(117,'hard');

输出

device_id|question_cnt
3214|0
2215|0
6543|1
2138|1
2315|2
加载中...