现在有两张表,一个是评定标准表(assess):
+-------+-------------+
| score | description |
+-------+-------------+
| 60 | 差 |
| 80 | 中 |
| 100 | 优 |
+-------+-------------+
这个表表示小于60分的是“差”,大于等于60小于80的是“中”,大于等于80小于100的是“优”。
一个学生成绩表(student):
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 95 |
| lisi | 85 |
| wangwu | 76 |
| zhaoliu | 34 |
| huqi | 65 |
| zhba | 70 |
| chenjiu | 80 |
+----------+-------+
帮忙写一个SQL或者存储过程能把学生名字和成绩相对应的评定显示出来,查询的最终结果如下:
+----------+-------+-------------+
| name | score | description |
+----------+-------+-------------+
| zhangsan | 95 | 优 |
| lisi | 85 | 优 |
| wangwu | 76 | 中 |
| zhaoliu | 34 | 差 |
| huqi | 65 | 中 |
| zhba | 70 | 中 |
| chenjiu | 80 | 优 |
+----------+-------+-------------+
+-------+-------------+
| score | description |
+-------+-------------+
| 60 | 差 |
| 80 | 中 |
| 100 | 优 |
+-------+-------------+
这个表表示小于60分的是“差”,大于等于60小于80的是“中”,大于等于80小于100的是“优”。
一个学生成绩表(student):
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 95 |
| lisi | 85 |
| wangwu | 76 |
| zhaoliu | 34 |
| huqi | 65 |
| zhba | 70 |
| chenjiu | 80 |
+----------+-------+
帮忙写一个SQL或者存储过程能把学生名字和成绩相对应的评定显示出来,查询的最终结果如下:
+----------+-------+-------------+
| name | score | description |
+----------+-------+-------------+
| zhangsan | 95 | 优 |
| lisi | 85 | 优 |
| wangwu | 76 | 中 |
| zhaoliu | 34 | 差 |
| huqi | 65 | 中 |
| zhba | 70 | 中 |
| chenjiu | 80 | 优 |
+----------+-------+-------------+
from student a
(SELECT description FROM assess WHERE score>a.score ORDER BY score LIMIT 1) AS ss FROM studenta a
+-------+-------------+-------+
| score | description | title |
+-------+-------------+-------+
| 60 | 不及格 | 差 |
| 80 | 及格 | 中 |
| 100 | 优 | NULL |
+-------+-------------+-------+
并且我在结果中希望把 description 和 title 都显示出来就会报错,我的SQL和错误如下:
mysql> select a.*, (select title,description from t1 where t1.score>a.score order by score limit 1) as assess from student a;
ERROR 1241 (21000): Operand should contain 1 column(s)请问应该如何解决,谢谢
(SELECT description FROM assess WHERE score>a.score ORDER BY score LIMIT 1) AS ss ,
(SELECT COALESCE(title,'') FROM assess WHERE score>a.score ORDER BY score LIMIT 1) AS ss1 FROM studenta a
用CONCAT(description,title)这个表表示小于60分的是“差”,大于等于60小于80的是“中”,大于等于80小于100的是“优”。修改表结构:
f1 f2 f3
0 59 差
60 79 中
80 100 优
SELECT a.*,(SELECT description FROM t1 WHERE score>=a.f1 and score<=a.f2 ) AS ss ,(SELECT COALESCE(title,'') FROM t1 WHERE score>=a.f1 and score<=a.f2) AS ss1 FROM student a