解决方案 »
- MySQL Server Error:Can't connect to MySQL server on 'localhost' (10048)
- 一个操作mysql的问题?
- 请教一个关于去除数据的语句
- 为什么Navicat for MySQL 连接不上?谁帮帮我?
- 提高语句执行效率, 子查询限定记录数问题?
- mysql的视图能传参数吗
- SOL领域讨论: MySQL InnoDB 的性能问题
- count(*)与count(id)的速度问题
- 请问各位高人如何将远程服务器上的数据库导入到本地的机器上呢?马上给分哈!!!
- 10g数据通过workbench上传到mysql需要多长时间呢?
- 急求:MySQL 创建视图报字符集错误应该怎么处理 !!!!
- 关于Information_schema和Mysql库的问题.
-> (select if(MIN(editedscore)=0.00,0,1) from paper_exams_answer WHERE userid=a.userid) as `成绩是否被确认`
-> FROM class_user a
-> LEFT JOIN paper_exams_answer b ON a.userid = b.userid
-> LEFT JOIN class_paper c ON b.paperid = c.paperid
-> LEFT JOIN paper d ON b.paperid = d.id
-> WHERE b.optionscore IS NOT NULL
-> AND a.userid <>0
-> AND a.isdel <>1
-> and c.isdel <>1
-> group by a.userid;
+-----+--------+---------+----------+---------+--------+----------------+
| id | userid | classid | 试卷汇总 | paperid | 总成绩 | 成绩是否被确认 |
+-----+--------+---------+----------+---------+--------+----------------+
| 154 | 2 | 2 | 2 | 1 | 140.00 | 0 |
| 153 | 3 | 2 | 1 | 1 | 85.00 | 1 |
| 152 | 4 | 2 | 1 | 1 | 75.00 | 0 |
+-----+--------+---------+----------+---------+--------+----------------+
3 rows in set (0.00 sec)mysql>
tt AS '成绩是否被确认'
FROM class_user a
LEFT JOIN paper_exams_answer b ON a.userid = b.userid
LEFT JOIN class_paper c ON b.paperid = c.paperid
LEFT JOIN paper d ON b.paperid = d.id
LEFT JOIN
(SELECT a.userid,IF(editedscore=0.00,0,1) AS tt FROM paper_exams_answer a GROUP BY a.userid) e
ON a.userid=e.userid
WHERE b.optionscore IS NOT NULL
AND a.userid <>0
AND a.isdel <>1
AND c.isdel <>1
GROUP BY a.userid
版主 正解;
个人解析,希望能帮助到楼主;假设数据表中的数据按照以下格式排列
mysql>select * from paper_exams_answer order by editedscore desc+----+--------+---------+-------------+-------------+
| id | userid | paperid | optionscore | editedscore |
+----+--------+---------+-------------+-------------+
| 4 | 2 | 2 | 80.00 | 90.00 |
| 2 | 3 | 1 | 85.00 | 85.00 |
| 1 | 2 | 1 | 60.00 | 0.00 |
| 3 | 4 | 1 | 75.00 | 0.00 |
+----+--------+---------+-------------+-------------+
把这个结果集当作原表进行查询:mysql>select a.userid,if(a.editedscore=0,0,1) as '成绩是否被确认' from (select * from paper_exams_answer order by editedscore desc) a group by userid;+--------+----------------+
| userid | 成绩是否被确认
+--------+----------------+
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
+--------+----------------+ 这样子结果就不对了咯 ,userid 为2 的用户 实际上是没被确认的,所以语句就得这样写,select a.userid,if(min(a.editedscore)=0,0,1) as '成绩是否被确认' from (select * from paper_exams_answer order by editedscore desc) a group by userid;+--------+----------------+
| userid | 成绩是否被确认
+--------+----------------+
| 2 | 0 |
| 3 | 1 |
| 4 | 0 |
+--------+----------------+sql解析:也就是说按照userid分组的话,根据我们的需求 就是参加考试的user 必须几次考试的成绩一起被确认通过 才显示 为 1, 所以在分组的数据中 我们就是取每个组中 editedscore这个字段的最小值去进行判断,保证结果的正确性。thanks!