name 那个表可以不关心!!!可以去掉那个字段!我主要得到的是optionscore分数这个字段!我是想根据不同的学员,不同的试卷得到学员的分数。
SELECT * FROM `class_user` a LEFT JOIN (SELECT * FROM `paper_exams_answer` a WHERE NOT EXISTS(SELECT 1 FROM `paper_exams_answer` WHERE a.userid=userid AND a.optionscore>optionscore)) b ON a.userid=b.userid WHERE b.id IS NOT null
select class_user.id,class_user.classid,class_user.userid,customers.realname,paper_exams_answer.optionscore from class_user inner join class_paper on class_user.classid=class_paper.classid inner join customers on class_user.idcustomer =idcustomer inner join paper_exams_answer on class_user.userid=paper_exams_answer.userid where class_user.userid<>0 and class_user.isdel<>1 group by id,classid,userid
mysql> select class_user.id,userid,classid,optionscore -> from class_user inner join paper_exams_answer using(userid) -> group by userid; +-----+--------+---------+-------------+ | id | userid | classid | optionscore | +-----+--------+---------+-------------+ | 154 | 2 | 2 | 60.00 | | 153 | 3 | 2 | 65.00 | | 152 | 4 | 2 | 70.00 | +-----+--------+---------+-------------+ 3 rows in set (0.04 sec)mysql>
select class_user.id,class_user.classid,class_user.userid,paper_exams_answer.optionscore from class_user inner join class_paper on class_user.classid=class_paper.classid inner join paper_exams_answer on class_user.userid=paper_exams_answer.userid where class_user.userid<>0 and class_user.isdel<>1 group by id,classid,userid
6楼前辈!您的SQL语句我执行!报错啊!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.userid ) group by class_user.userid
SELECT a.id,a.userid,a.`classid`,b.`optionscore` 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 ORDER BY b.`optionscore`
id=154有两条记录,取分数小的?
(SELECT * FROM `paper_exams_answer` a WHERE NOT EXISTS(SELECT 1 FROM `paper_exams_answer` WHERE a.userid=userid
AND a.optionscore>optionscore)) b
ON a.userid=b.userid WHERE b.id IS NOT null
from class_user inner join class_paper on class_user.classid=class_paper.classid
inner join customers on class_user.idcustomer =idcustomer
inner join paper_exams_answer on class_user.userid=paper_exams_answer.userid
where class_user.userid<>0 and class_user.isdel<>1
group by id,classid,userid
-> from class_user inner join paper_exams_answer using(userid)
-> group by userid;
+-----+--------+---------+-------------+
| id | userid | classid | optionscore |
+-----+--------+---------+-------------+
| 154 | 2 | 2 | 60.00 |
| 153 | 3 | 2 | 65.00 |
| 152 | 4 | 2 | 70.00 |
+-----+--------+---------+-------------+
3 rows in set (0.04 sec)mysql>
select class_user.id,class_user.classid,class_user.userid,paper_exams_answer.optionscore
from class_user inner join class_paper on class_user.classid=class_paper.classid
inner join paper_exams_answer on class_user.userid=paper_exams_answer.userid
where class_user.userid<>0 and class_user.isdel<>1
group by id,classid,userid
--
-- 表的结构 `class_paper`
--CREATE TABLE IF NOT EXISTS `class_paper` (
`id` int(11) NOT NULL auto_increment,
`classid` int(11) NOT NULL COMMENT '班级id',
`paperid` int(11) NOT NULL COMMENT '试卷id',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='班级试卷表' AUTO_INCREMENT=14 ;--
-- 导出表中的数据 `class_paper`
--INSERT INTO `class_paper` (`id`, `classid`, `paperid`) VALUES
(12, 2, 2),
(13, 2, 1);-- ----------------------------------------------------------
-- 表的结构 `class_user`
--CREATE TABLE IF NOT EXISTS `class_user` (
`id` int(11) NOT NULL auto_increment,
`classid` int(11) NOT NULL COMMENT '班级id',
`userid` int(11) NOT NULL default '0' COMMENT '学员id',
`isdel` int(11) NOT NULL default '0' COMMENT '删除标记。0未删,1删除',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='班级学员表' AUTO_INCREMENT=155 ;--
-- 导出表中的数据 `class_user`
--INSERT INTO `class_user` (`id`, `classid`, `userid`, `isdel`) VALUES
(154, 2, 2, 0),
(153, 2, 3, 0),
(152, 2, 4, 0),
(151, 2, 0, 0),
(150, 2, 0, 0);-- ----------------------------------------------------------
-- 表的结构 `paper`
--CREATE TABLE IF NOT EXISTS `paper` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(256) NOT NULL COMMENT '试卷名称',
`isenable` int(4) NOT NULL default '1' COMMENT '是否可用0不可用1可用',
`type` varchar(256) default NULL COMMENT '试卷类型(如: 练习试卷, 考试试卷)',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='试卷表' AUTO_INCREMENT=3 ;--
-- 导出表中的数据 `paper`
--INSERT INTO `paper` (`id`, `name`, `isenable`, `type`) VALUES
(1, '财务会计试卷', 1, '课后测试'),
(2, '工业基础试卷', 1, '课后测试');-- ----------------------------------------------------------
-- 表的结构 `paper_exams_answer`
--CREATE TABLE IF NOT EXISTS `paper_exams_answer` (
`id` int(11) NOT NULL auto_increment,
`userid` int(11) NOT NULL COMMENT '用户id',
`paperid` int(11) NOT NULL COMMENT '试卷id',
`optionscore` decimal(10,2) default NULL COMMENT '分数',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='学员答题表' AUTO_INCREMENT=5 ;--
-- 导出表中的数据 `paper_exams_answer`
--INSERT INTO `paper_exams_answer` (`id`, `userid`, `paperid`, `optionscore`) VALUES
(1, 2, 1, 60.00),
(2, 3, 2, 65.00),
(3, 4, 1, 70.00),
(4, 2, 2, 80.00);
| id | userid | classid | optionscore|
+-----+--------+---------+------------+
| 154 | 2 | 2 | 60.00 |
| 153 | 3 | 2 | 65.00 |
| 152 | 4 | 2 | 70.00 |
| 154 | 2 | 2 | 80.00 |
+-----+--------+---------+------------+
-> from class_user inner join paper_exams_answer using(userid);
+-----+--------+---------+-------------+
| id | userid | classid | optionscore |
+-----+--------+---------+-------------+
| 154 | 2 | 2 | 60.00 |
| 154 | 2 | 2 | 80.00 |
| 153 | 3 | 2 | 65.00 |
| 152 | 4 | 2 | 70.00 |
+-----+--------+---------+-------------+
4 rows in set (0.00 sec)mysql>
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
ORDER BY b.`optionscore`