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