sql case end

create database demo
use demo

create table [user]
(
	[uId] int identity(1,1) primary key,
	[name] varchar(50),
	[level] int  --1骨灰2大虾3菜鸟
)
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 * from score
--case end第二种用法，相当于多重if语句
select studentId,
	case
		when english >=90 then '优'
		when english >=80 and english <90 then '良'
		when english >=70 and english < 80 then '中'
		when english >= 60 and english < 70 then '可'
		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 then a
		else b
	end,
	case	
		when b>c then b
		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 > 0 then amount
		else 0
	end as '收入',
	case
		when amount < 0 then abs(amount)
		else 0
	end as '支出'
from test


--有一张表student0，记录学生成绩
use demo
CREATE TABLE student0 (name nvarchar(10),subject nvarchar(10),result int)
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 * from student0

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 * from student) as t



select 1,(select sum(english) from score) as '和',(select avg(sAge) from student) as '平均年龄'

--查询高一一班所有的学生
select * from student where sClassId = 
(select cId from class where cName='高一一班')

--查询高一一班  高二一班 所有的学生
--子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后
--子查询跟在比较运算符之后，要求子查询只返回一个值
select * from student where sClassId =
(select cId from class where cName in ('高一一班','高二一班'))


select * from student where sClassId in
(select cId from class where cName in ('高一一班','高二一班'))


--查询刘关张的成绩
select * from score where studentId in
(select sId from student where sName in ('刘备123','关羽','张飞'))

select * from student

--删除刘关张
delete from score where studentId in
(select sId from student where sName in ('刘备123','关羽','张飞'))


--实现分页
--最近入学的3个学生
select top 3 * from student 
order by sId desc

--查询第4到6个学生
select top 3 * from student
where sId not in (select top 3 sId from student order by sId desc)
order by sId desc


--查询7到9个学生
select top 3 * from student
where sId not in (select top 6 sId from student order by sId desc)
order by sId desc

--查询第n页的学生
select top 5 * from student
where sId not in (select top (5*(2-1)) sId from student order by sId desc)
order by sId desc


select * from student


--sql 2005中的分页

select * from 
(select row_number() over(order by sId desc) as num,* from student) as t
where num between 1 and 3


select * from 
(select row_number() over(order by sId desc) as num,* from student) as t
where num between 4 and 6

select * from 
(select row_number() over(order by sId desc) as num,* from student) as t
where num between 7 and 9


select * from 
(select row_number() over(order by sId desc) as num,* from student) as t
where num between 3*(3-1) + 1 and 3*3



--表连接
--交叉连接cross join
select * from student 
cross join class


--内连接inner join...on...
select * from student
inner join class on sClassId=cId

select * from class

--查询所有学生的姓名、年龄及所在班级
select sName,sAge,cName,sSex from student
inner join class on sClassId = cId
where sSex ='女'
--查询年龄超过20岁的学生的姓名、年龄及所在班级
select sName,sAge,cName from class
inner join student on sClassId = cId
where sAge > 20

--外连接
--left join...on...
select sName,sAge,cName from class
left join student on sClassId = cId