子查询的数据小于外查询的数据不一致怎么办?
例如:
学生表(student):
StudentID name age class Grade
1 张三 16 1 4
2 李四 15 5
3 王五 17
4 赵六 16 2班级/年级表(class):
classID type className
1 class 1班
2 class 2班
3 class 3班
4 grade 5年级
5 grade 6年级我想要的查询结果:
StudentID name age className grade
1 张三 16 1班 5年级
2 李四 15 6年级
3 王五 17
4 赵六 16 2班我的查询语句:
select studentId,name,age,(select className from class where classId = a.class and type = 'class') className,(select className from class where classId = a.Grade and type = 'grade') grade from student aPs:因为学生表中班级和年级字段有的是空的,所以子查询的数据少了,报错:值过多。如果想得到我要的查询结果该怎么查?
例如:
学生表(student):
StudentID name age class Grade
1 张三 16 1 4
2 李四 15 5
3 王五 17
4 赵六 16 2班级/年级表(class):
classID type className
1 class 1班
2 class 2班
3 class 3班
4 grade 5年级
5 grade 6年级我想要的查询结果:
StudentID name age className grade
1 张三 16 1班 5年级
2 李四 15 6年级
3 王五 17
4 赵六 16 2班我的查询语句:
select studentId,name,age,(select className from class where classId = a.class and type = 'class') className,(select className from class where classId = a.Grade and type = 'grade') grade from student aPs:因为学生表中班级和年级字段有的是空的,所以子查询的数据少了,报错:值过多。如果想得到我要的查询结果该怎么查?
select A.StudentID,
A.name,
A.age,
B.classname,
b.grade
from student a left outer join class b
on a.class=b.classID
select A.StudentID,
A.name,
A.age,
case when b.class='class' then B.classname as class,
case when b.class='grade' then b.classname as grade
from student a left outer join class b
on a.class=b.classID
select A.StudentID,
A.name,
A.age,
case when b.type='class' then B.classname as class,
case when b.type='grade' then b.classname as grade
from student a left outer join class b
on a.class=b.classID
select A.StudentID,
A.name,
A.age,
case when b.type='class' then B.classname as class,
case when b.type='grade' then b.classname as grade
from student a left outer join class b
on a.class=b.classID
Thanks
select A.StudentID,
A.name,
A.age,
A.classname||'班' class,
A.grade+1||'年级' grade
from student;