case 嵌套查询与连接查询你需要懂得

select * from score 
create database demo 
use demo 
create table [user] 
( 
[uId] int identity( 1 ,1 ) primary key, 
[name] varchar ( 50), 
[level] int --1骨灰大虾菜鸟 
) 
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 

-- 练习 
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]

-- 子查询将一个查询语句做为一个结果集供其他 SQL 语句使用，就像使用普通的表一样， 
-- 被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。 
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 = '高一一班 ' ) 
-- 查询高一一班 高二一班所有的学生 
-- 子查询返回的值不止一个。当子查询跟随在 = 、!= 、 <、 <= 、> 、 >= 之后 
-- 子查询跟在比较运算符之后，要求子查询只返回一个值 
-- 如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。可以使用 in 关键字代替 =号 
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 ( '刘备 ' ,' 关羽 ', ' 张飞' )) 
-- 删除刘关张 
delete from score where studentId in 
( select sId from student where sName in ( '刘备 ' ,' 关羽 ', ' 张飞' )) 

-- 实现分页 
-- 最近入学的个学生 
select top 3 * from student 
order by sId desc 
-- 查询第到个学生 
select top 3 * from student 
where sId not in ( select top 3 sId from student order by sId desc) 
order by sId desc 
-- 查询到的学生 
select top 3 * from student 
where sId not in ( select top 6 sId from student order by sId desc) 
order by sId desc 
-- 上面是sql 2000 以前的实现方式。 SQLServer2005 后增加了Row_Number 函数简化实现。 
--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 =' 女 ' 
-- 查询年龄超过岁的学生的姓名、年龄及所在班级 
select sName , sAge, cName from class 
inner join student on sClassId = cId 
where sAge > 20 
-- 外连接 
--left join...on... 
select sName , sAge, cName from class