Table UserInfo
UserID UserName UserEmail UserType UserAddress
=====================================
1 Winder [email protected] 1 3
2 David [email protected] 3 1
7 Jimmy [email protected] 2
10 Simon [email protected] 2 9
11 Mary 5 4
12 Lily [email protected] 6 10
15 Lucy 1 4
19 Ender [email protected] 1
20 Alex [email protected] 5 5Table UserAddress
UserAddressID UserCountry UserAddress1 UserAdderss2 UserPhone
=========================================
1 US 1234567
3 China Zhejiang Huzhou 3698523
4 China
5 US CA 78897987
9 France Unknow
10 China Beijing
Table UserType
TypeID TypeName
============
1 Engineer
2 QA
3 Market
5 Manager
6 Counselor
8 Sales
============h现在有一种情况,需要在显示用户信息的时候把TypeName显示成另外的符号,并按新的UserType升序排序,如果有重复在按UserName升序排序,显示效果如下,请用一句SQL语句来实现。对应关系是Engineer-K,Manager-A,QA-S,Counselor-Z,Market-V,Sales-G。最终显示的UserType字段的内容是所属的TypeID和对应的符号合起来,最终效果如下图:(3分)
UserID UserName UserEmail UserType UserAddress
=====================================
1 Winder [email protected] 1 3
2 David [email protected] 3 1
7 Jimmy [email protected] 2
10 Simon [email protected] 2 9
11 Mary 5 4
12 Lily [email protected] 6 10
15 Lucy 1 4
19 Ender [email protected] 1
20 Alex [email protected] 5 5Table UserAddress
UserAddressID UserCountry UserAddress1 UserAdderss2 UserPhone
=========================================
1 US 1234567
3 China Zhejiang Huzhou 3698523
4 China
5 US CA 78897987
9 France Unknow
10 China Beijing
Table UserType
TypeID TypeName
============
1 Engineer
2 QA
3 Market
5 Manager
6 Counselor
8 Sales
============h现在有一种情况,需要在显示用户信息的时候把TypeName显示成另外的符号,并按新的UserType升序排序,如果有重复在按UserName升序排序,显示效果如下,请用一句SQL语句来实现。对应关系是Engineer-K,Manager-A,QA-S,Counselor-Z,Market-V,Sales-G。最终显示的UserType字段的内容是所属的TypeID和对应的符号合起来,最终效果如下图:(3分)
解决方案 »
- javascript 取url的值
- web.config中,allow roles='rolename'中的role在程序中如何设置啊?
- 求个url的正则表达式
- 将C/S版的软件中某几个功能使用ASP.NET做到WEB上,关于权限的问题
- asp.net 缓存更新的问题,急需解决,兄弟姐妹帮帮忙
- 如何把一个ASP.NET页面定时自动转到另一个ASP.NET页面
- 请高手指点:asp.net中如何在sql查询条件中引入变量?
- datagrid不能分页了
- 动态装载自定义控件时,在响应完自定义的事件事件之后,多次装载切换时发生的错误!
- 高手请进。。。。烦。。。。
- CheckBoxList可否添加子节点
- 有关样式的问题
SELECT * FROM (
SELECT *,CASE TypeName WHEN 'Engineer' THEN 'K' WHEN 'Manage' THEN 'A' WHEN 'QA' THEN 'S' WHEN 'Counselor' THEN 'Z' WHEN 'Market' THEN 'V' WHEN 'Sales' THEN G END AS TYPE
FROM UserInfo AS U JOIN UserAddress AS A ON U.UserAddress=A.UserAddressID
JOIN UserType AS T ON T.TypeID=U.UserType
) ORDER BY TYPE,UserName
select UserID
,UserName
,UserEmail
,UserType=case UserType
when 1 then 'K'
when 2 then 'S'
when 3 then 'V'
when 5 then 'K'
when 6 then 'Z'
when 8 then 'G'
end
,UserAddress
from UserInfo
order by UserType,UserName