Table admin
adminID adminName admintype
1 xiaoming 3
2 xiaohua 2
3 xiaowang 2
4 yu 1
5 lai 4
Table userType
TypeID TypeName
1 Student
2 Teacher
3 Cleaner
4 Manager
5 Counselor
现在把Student-S,Teacher-T,Cleaner-C,Manager-M,用adminID adminName admintype显示数据,admintype显示用简写,并按符号的升序排序,如果出现重复就用adminName升序排序,请如何写SQL语句,谢谢了,急
adminID adminName admintype
1 xiaoming 3
2 xiaohua 2
3 xiaowang 2
4 yu 1
5 lai 4
Table userType
TypeID TypeName
1 Student
2 Teacher
3 Cleaner
4 Manager
5 Counselor
现在把Student-S,Teacher-T,Cleaner-C,Manager-M,用adminID adminName admintype显示数据,admintype显示用简写,并按符号的升序排序,如果出现重复就用adminName升序排序,请如何写SQL语句,谢谢了,急
1 xiaoming C
2 xiaohua T
3 xiaowang T
4 yu S
5 lai M
这样形式显示,并按C-T-S这些符号的升序排序,如果出现重复就用adminName升序排序
写出SQL语句,谢谢了
admintype=(select left(TypeName, 1) from userType where typeID=tmp.admintype)
from admin as tmp
order by 3, 2
----------- -------------------- ---------
1 xiaoming C
5 lai M
4 yu S
2 xiaohua T
3 xiaowang T(5 row(s) affected)
TypeName=(case TypeName when 'Student' then 's'
when 'Teacher' then 'T'
when 'Cleaner' then 'C'
when 'Manager'then 'M'
when 'Counselor'then 'CL'
end
) from usertype join admin on TypeID=admintype
admintype=(select left(TypeName, 1) from userType where typeID=tmp.admintype)
from admin as tmp
order by admintype,adminName