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
按userType列出TypeID,TypeName和UserCount(用户数量),如果这个Type下没有用户,则显示0,按UserCount降序排序,然后在按TypeName升序排序。
请问怎么求啊
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
按userType列出TypeID,TypeName和UserCount(用户数量),如果这个Type下没有用户,则显示0,按UserCount降序排序,然后在按TypeName升序排序。
请问怎么求啊
TypeName,
UserCount = ISNULL((SELECT COUNT(1)
FROM [admin]
WHERE [admin].admintype = [userType].TypeID),0)
FROM [userType]
ORDER BY
ISNULL((SELECT COUNT(1)
FROM [admin]
WHERE [admin].admintype = [userType].TypeID),0) DESC
insert admin select 1, 'xiaoming', 3
union all select 2, 'xiaohua', 2
union all select 3, 'xiaowang', 2
union all select 4, 'yu', 1
union all select 5, 'lai', 4 create table userType(TypeID int, TypeName varchar(20))
insert userType select 1, 'Student'
union all select 2, 'Teacher'
union all select 3, 'Cleaner'
union all select 4, 'Manager'
union all select 5, 'Counselor'
select *,
UserCount=(select count(*) from admin where admintype=tmp.TypeID)
from userType as tmp
order by 3 desc, 2 asc