case--end 和子查询

MSSQL操作 case end 和子查询 代码如下：

  1 create database Test
  2 go
  3 use Test
  4 go
  5 create table [user]
  6 (
  7     uId int identity(1,1) primary key,
  8     name varchar(50),
  9     level int  --1骨灰 2大虾 3菜鸟
 10 )
 11 go
 12 insert into [user] (name,level) values('犀利哥',1)
 13 insert into [user] (name,level) values('小月月',2)
 14 insert into [user] (name,level) values('芙蓉姐姐',3)
 15 
 16 select * from [user]
 17 
 18 --1、case end 相当于c#中的swith
 19 select [name],
 20     case [level] 
 21         when 1 then '骨灰' 
 22         when 2 then '大虾'
 23         when 3 then '菜鸟'
 24         else '神仙'
 25     end as '等级'
 26 from [user]
 27 
 28 --2、case end 相当于c#的多重if语句
 29 --注意：case end的返回值类型要一致
 30 use MySchool
 31 
 32 select * from score
 33 
 34 select studentId,
 35     case
 36         when english >=90 and english <=100 then 'A'
 37         when english between 80 and 89 then 'B'
 38         when english between 70 and 79 then 'C'
 39         when english between 60 and 69 then 'D'
 40         else 'E'
 41     end
 42 from score
 43 
 44 --练习1
 45 select 
 46     case 
 47         when a>b then a
 48         else b
 49     end,
 50     case 
 51         when b > c then b
 52         else c
 53     end
 54 from biao
 55 
 56 
 57 select abs(-39)
 58 
 59 --练习2
 60 use Test
 61 go
 62 create table test
 63 (
 64     number varchar(10),
 65     amount int
 66 )
 67 insert into test(number,amount) values('RK1',10)
 68 insert into test(number,amount) values('RK2',20)
 69 insert into test(number,amount) values('RK3',-30)
 70 insert into test(number,amount) values('RK4',-10)
 71 go
 72 
 73 select * from test
 74 
 75 select number,
 76     case
 77         when amount > 0 then amount
 78         else 0
 79     end as 收入,
 80     case 
 81         when amount < 0 then abs(amount)
 82         else 0
 83     end as 支出
 84 from test
 85 
 86 --增加练习
 87 name sex -- 0 1 
 88 select name,
 89     case sex
 90         when 1 then '男'
 91         when 0 then '女'
 92     end 
 93 from student
 94 --练习3
 95 use Test
 96 go
 97 CREATE TABLE student0 (name nvarchar(10),subject nvarchar(10),result int)
 98 INSERT INTO student0 VALUES ('张三','语文',80)
 99 INSERT INTO student0 VALUES ('张三','数学',90)
100 INSERT INTO student0 VALUES ('张三','物理',85)
101 INSERT INTO student0 VALUES ('李四','语文',85)
102 INSERT INTO student0 VALUES ('李四','数学',92)
103 INSERT INTO student0 VALUES ('李四','物理',null)
104 
105 SELECT * FROM student0
106 
107 select name,
108     sum(case subject
109         when '语文' then result
110     end) as '语文',
111     sum(case subject 
112         when '数学' then result
113     end) as '数学',
114     isnull(sum(case subject
115         when '物理' then result
116     end),0) as '物理'
117 from student0
118 group by name
119 
120 --isnull(,)
121 
122 
123 
124 use MySchool
125 select * from student
126 
127 select * from [user]
128 delete from [user] where uid in (7,8,9)
129 insert into [user] (uUserName,uPwd,uTimes) values('123','123',0)
130 select * from [user]
131 
132 
133 
134 --子查询
135 select * from (select * from student where sAge<23) as t
136 
137 --
138 select max(english) from score
139 union all
140 select min(english) from score
141 union all
142 select avg(english) from score
143 
144 select (select max(english) from score) as t,
145 (select min(english) from score),
146 (select avg(english) from score)
147 
148 --查询高二一班所有的学生
149 select * from class
150 select * from student
151 
152 --当=、!=、<、<=、>、>=号后面是子查询的时候，
153 --子查询必须返回一列
154 --子查询必须返回一行
155 --错误的情况，因为子查询返回多行
156 select * from student where sClassId=
157 (select cId from class where cName='高二一班' or cName='高二二班')
158 
159 
160 --查询所有高二的学生
161 select * from student where sClassId in 
162 (select cId from class where cName like '高二%')
163 
164 --查询刘关张 的成绩
165 select * from score where studentId in
166 (select sId from student where sName in ('曹操','夏侯惇','张飞'))
167 
168 --子查询删除数据
169 delete from score where studentId in 
170 (select sId from student where sName in ('曹操','夏侯惇') )
171 
172 select * from score
173 
174 --分页
175 
176 select top 5 * from student order by sId desc
177 --查询最近插入的第6-10个人。除了前5个人的前5条
178 select top 5 * from student where sId not in
179 (select top (5*(3-1)) sId from student order by sId desc)
180 order by sId desc
181 
182 --sql 2005以后生成连续的编号
183 insert into student (sName,sSex,sAge) values('abc','男',18)
184 
185 select row_number() over(order by sId desc),*
186 from student
187 
188 select * from 
189 (select *,row_number() over(order by sId desc) as [no] from student) as t
190 where t.[no] between (5*(3-1))+1 and (5*3)
191 order by sId desc
192 
193 
194 select * from student

转载于:https://www.cnblogs.com/daban/archive/2012/08/17/2643779.html