SQL中case,when,then,end的用法(…
来源:互联网 发布:笑郭网络验证4.0破解 编辑:程序博客网 时间:2024/05/17 09:31
转载自:http://blog.csdn.net/chaojishuaigeli/article/details/8165802
- create
database demo - use
demo -
- create
table [ user] - (
-
[uId] int identity(1,1) primarykey, -
[name] varchar(50), -
[level] int - )
- insert
into [ user]( name,level)values('犀利哥',1) - insert
into [ user]( name,level)values('小月月',2) - insert
into [ user]( name,level)values('芙蓉姐姐',3) -
-
-
- --case
end 相当于switch case - --then后面的返回值类型必须一致
- select
[ name], -
case [ level] -
when 1 then'骨灰' -
when 2 then'大虾' -
when 3 then'菜鸟' -
end as '等级' - from
[ user] -
- use
MySchool - select
* fromscore - --case
end第二种用法,相当于多重if语句 - select
studentId, -
case -
when english then>=90 '优' -
when english and>=80 english then<90 '良' -
when english and>=70 english then< 80 '中' -
when english and>= 60 english then< 70 '可' -
else '差' -
end as '成绩' - from
score - order
by english -
-
- --表中有A
B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。 - select
-
case -
when a>b thena -
else b -
end, -
case -
when b>c thenb -
else c -
end - from
T -
-
- --练习2
- create
table test - (
-
number varchar(10), -
amount int - )
- insert
into test(number,amount) values('RK1',10) - insert
into test(number,amount) values('RK2',20) - insert
into test(number,amount) values('RK3',-30) - insert
into test(number,amount) values('RK4',-10) -
- select
number, -
case -
when amount then> 0 amount -
else 0 -
end as '收入', -
case -
when amount then< 0 abs(amount) -
else 0 -
end as '支出' - from
test -
-
- --有一张表student0,记录学生成绩
- use
demo - CREATE
TABLE student0 name( nvarchar(10),subject int)nvarchar(10),result - INSERT
INTO student0 VALUES( '张三','语文',80) - INSERT
INTO student0 VALUES( '张三','数学',90) - INSERT
INTO student0 VALUES( '张三','物理',85) - INSERT
INTO student0 VALUES( '李四','语文',85) - INSERT
INTO student0 VALUES( '李四','数学',92) - INSERT
INTO student0 VALUES( '李四','物理',null) -
- select
* fromstudent0 -
- select
[ name], -
isnull(sum(case subject -
when '语文' then result -
end),0) as '语文', -
isnull(sum(case subject -
when '数学' then result -
end),0) as '数学', -
isnull(sum(case subject -
when '物理' then result -
end),0) as '物理' - from
student0 - group
by [ name] -
-
-
- --子查询
- use
myschool - select
sName from( select* fromstudent) ast -
-
-
- select
1,( selectsum(english) from score) as'和',(select avg(sAge) from student) as'平均年龄' -
- --查询高一一班所有的学生
- select
* fromstudent wheresClassId = - (select
cId fromclass wherecName= '高一一班') -
- --查询高一一班
高二一班 所有的学生 - --子查询返回的值不止一个。当子查询跟随在
=、!=、<、<=、>、>= 之后 - --子查询跟在比较运算符之后,要求子查询只返回一个值
- select
* fromstudent wheresClassId = - (select
cId fromclass wherecName in( '高一一班','高二一班')) -
-
- select
* fromstudent wheresClassId in - (select
cId fromclass wherecName in( '高一一班','高二一班')) -
-
- --查询刘关张的成绩
- select
* fromscore wherestudentId in - (select
sId fromstudent wheresName in( '刘备123','关羽','张飞')) -
- select
* fromstudent -
- --删除刘关张
- delete
from score wherestudentId in - (select
sId fromstudent wheresName in( '刘备123','关羽','张飞')) -
-
- --实现分页
- --最近入学的3个学生
- select
top 3 from* student - order
by sId desc -
- --查询第4到6个学生
- select
top 3 from* student - where
sId notin ( selecttop 3 fromsId student orderby sId desc) - order
by sId desc -
-
- --查询7到9个学生
- select
top 3 from* student - where
sId notin ( selecttop 6 fromsId student orderby sId desc) - order
by sId desc -
- --查询第n页的学生
- select
top 5 from* student - where
sId notin ( selecttop (5*(2-1)) fromsId student orderby sId desc) - order
by sId desc -
-
- select
* fromstudent -
-
- --sql
2005中的分页 -
- select
* from - (select
row_number() orderover( by sId desc)as num,* fromstudent) ast - where
num between1 and3 -
-
- select
* from - (select
row_number() orderover( by sId desc)as num,* fromstudent) ast - where
num between4 and6 -
- select
* from - (select
row_number() orderover( by sId desc)as num,* fromstudent) ast - where
num between7 and9 -
-
- select
* from - (select
row_number() orderover( by sId desc)as num,* fromstudent) ast - where
num between3*(3-1) and+ 1 3*3 -
-
-
- --表连接
- --交叉连接cross
join - select
* fromstudent - cross
join class -
-
- --内连接inner
join...on... - select
* fromstudent - inner
join class onsClassId=cId -
- select
* fromclass -
- --查询所有学生的姓名、年龄及所在班级
- select
sName,sAge,cName,sSex fromstudent - inner
join class onsClassId = cId - where
sSex '女'= - --查询年龄超过20岁的学生的姓名、年龄及所在班级
- select
sName,sAge,cName fromclass - inner
join student onsClassId = cId - where
sAge > 20 -
- --外连接
- --left
join...on... - select
sName,sAge,cName fromclass - left
join student onsClassId = cId
阅读全文
0 0
- SQL中case,when,then,end的用法(…
- SQL中 case when then end的用法
- SQL SERVER中case when ... then ... else... end用法
- SQL case when then end 简单用法
- sql case when then end 用法
- sql case when then end 用法
- SQL中CASE WHEN THEN的用法
- SQL的case when then else end语句的用法
- sql学习之CASE WHEN THEN ELSE END的用法
- MySql 中 case when then else end 的用法
- MySql 中 case when then else end 的用法
- MySql 中 case when then else end 的用法
- MySql 中 case when then else end 的用法
- MySql-中-case-when-then-else-end-的用法
- MySql 中 case when then else end 的用法
- case when then else end的用法
- sql中case when then简单用法
- SQL中case when then用法
- session与cookie的区别
- Java问题二:为什么要用线程池
- 流水线技术原理和Verilog HDL实现
- jQuery ajax - ajax() 方法
- 解决无线路由可以上网而有线不可以…
- SQL中case,when,then,end的用法(…
- mysql安装 赋权限
- JQuery对象与dom对象的转化
- 悼念512汶川大地震遇难同胞
- 将博客搬至CSDN
- AMD INSTINCT
- 用Github的Api发现stars最多开源项目
- try{}catch(Exception e){}异常处理
- Stanford CoreNLP学习日记2