查询某个字段值的问题 本帖最后由 ACMAIN_CHM 于 2010-12-15 22:00:21 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql> select * from `paper_exams_answer`;+----+--------+---------+-------------+-------------+| id | userid | paperid | optionscore | editedscore |+----+--------+---------+-------------+-------------+| 1 | 2 | 1 | 60.00 | 70.00 || 2 | 2 | 2 | 85.00 | 0.00 || 3 | 2 | 3 | 75.00 | 85.00 || 4 | 2 | 4 | 80.00 | 85.00 |+----+--------+---------+-------------+-------------+4 rows in set (0.42 sec)mysql> select if((editedscore=0.00),0,1) as chengji from paper_exams_answer;+---------+| chengji |+---------+| 1 || 0 || 1 || 1 |+---------+4 rows in set (0.08 sec)mysql> select if(editedscore=0.00,0,1) from paper_exams_answer 或者用case when editedscore=0 then 0 else 1 end select if((editedscore==0.00),0,1) as chengji from paper_exams_answer你多了一个等号,SQL中的等号运算符,就是一个等号。 我把所有表语句都贴出来!在帮忙给我看看!---- 表的结构 `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', `isdel` int(4) NOT NULL default '1' COMMENT '是否被禁用0禁用 1启用', PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='班级试卷表' AUTO_INCREMENT=15 ;---- 导出表中的数据 `class_paper`--INSERT INTO `class_paper` (`id`, `classid`, `paperid`, `isdel`) VALUES(14, 2, 2, 0),(13, 2, 1, 0);-- ------------------------------------------------------------ 表的结构 `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 '分数', `editedscore` decimal(10,2) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='学员答题表' AUTO_INCREMENT=5 ;---- 导出表中的数据 `paper_exams_answer`--INSERT INTO `paper_exams_answer` (`id`, `userid`, `paperid`, `optionscore`, `editedscore`) VALUES(1, 2, 1, 60.00, 0.00),(2, 3, 1, 85.00, 85.00),(3, 4, 1, 75.00, 0.00),(4, 2, 2, 80.00, 90.00);其实我想做的是如下操作!请各位在帮我看看!SELECT a.id, a.userid, a.classid,count(b.paperid) as 试卷汇总,b.paperid, sum(b.optionscore) as 总成绩,(select if(editedscore=0.00,0,1) from paper_exams_answer) as 成绩是否被确认 FROM class_user aLEFT JOIN paper_exams_answer b ON a.userid = b.useridLEFT JOIN class_paper c ON b.paperid = c.paperidLEFT JOIN paper d ON b.paperid = d.idWHERE b.optionscore IS NOT NULL AND a.userid <>0AND a.isdel <>1and c.isdel <>1 group by a.userid 你想得到什么样的结果? (不要高估你的汉语表达能力或者我的汉语理解能力) 建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样,(并给以简单的算法描述) 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。 另外建议在发贴的顶楼就把问题提出,不要别人回答完后,结果你的问题再逐步放大。http://blog.csdn.net/happyparrot/archive/2010/04/27/5533622.aspx#1360524 我想得到这样的结果!+-----+--------+---------+----------+---------+--------+----------------+| 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 |+-----+--------+---------+----------+---------+--------+----------------+ +-----+--------+---------+----------+---------+--------+----------------+| 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 |+-----+--------+---------+----------+---------+--------+----------------+我想得到这样的结果! mysql> SELECT a.id, a.userid, a.classid,count(b.paperid) as `试卷汇总`,b.paperid, sum(b.optionscore) as `总成绩`, -> (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> 非常感谢!ACMAIN_CHM太感谢了! 疑难杂症啊 , 2003 connot connect to ...10061 MYSQL导入数据失败.No query specified 表的联合问题,很慢,怎么优化 大家好!请问我的mysql编译时出了问题 及其简单问题!!!!!!!!!!! 在window下怎样备份和还原mysql数据库? 从数据库提取的字段值,显示在前台表格中,如果字段有值,就显示该值,如果为null,则什么也不显示 为什么我输入net stop MYSQL 会没有反应呢? 请问下我这条sql那里有问题 sql将两个结果集并在一起 商品订单和物流表的ER图怎么画? 新婚归来,散分! 选择性的触发器
+----+--------+---------+-------------+-------------+
| id | userid | paperid | optionscore | editedscore |
+----+--------+---------+-------------+-------------+
| 1 | 2 | 1 | 60.00 | 70.00 |
| 2 | 2 | 2 | 85.00 | 0.00 |
| 3 | 2 | 3 | 75.00 | 85.00 |
| 4 | 2 | 4 | 80.00 | 85.00 |
+----+--------+---------+-------------+-------------+
4 rows in set (0.42 sec)mysql> select if((editedscore=0.00),0,1) as chengji from paper_exams_answer;
+---------+
| chengji |
+---------+
| 1 |
| 0 |
| 1 |
| 1 |
+---------+
4 rows in set (0.08 sec)mysql>
你多了一个等号,SQL中的等号运算符,就是一个等号。
-- 表的结构 `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',
`isdel` int(4) NOT NULL default '1' COMMENT '是否被禁用0禁用 1启用',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='班级试卷表' AUTO_INCREMENT=15 ;--
-- 导出表中的数据 `class_paper`
--INSERT INTO `class_paper` (`id`, `classid`, `paperid`, `isdel`) VALUES
(14, 2, 2, 0),
(13, 2, 1, 0);-- ----------------------------------------------------------
-- 表的结构 `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 '分数',
`editedscore` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='学员答题表' AUTO_INCREMENT=5 ;--
-- 导出表中的数据 `paper_exams_answer`
--INSERT INTO `paper_exams_answer` (`id`, `userid`, `paperid`, `optionscore`, `editedscore`) VALUES
(1, 2, 1, 60.00, 0.00),
(2, 3, 1, 85.00, 85.00),
(3, 4, 1, 75.00, 0.00),
(4, 2, 2, 80.00, 90.00);
其实我想做的是如下操作!请各位在帮我看看!SELECT a.id, a.userid, a.classid,count(b.paperid) as 试卷汇总,b.paperid, sum(b.optionscore) as 总成绩,
(select if(editedscore=0.00,0,1) from paper_exams_answer) 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
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
| 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 |
+-----+--------+---------+----------+---------+--------+----------------+
| 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 |
+-----+--------+---------+----------+---------+--------+----------------+我想得到这样的结果!
-> (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>