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 單值判斷 相當(dāng)于 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 第二種用法，相當(dāng)于多重 if 語句
select studentId ,
case
when english >=90 then ' 優(yōu) '
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 語句實現(xiàn)：當(dāng) A列大于 B 列時選擇A 列否則選擇 B 列，當(dāng)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

-- 練習(xí)
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
--結(jié)果如下

-- 有一張表student0 ，記錄學(xué)生成績
use demo
CREATE TABLE student0 ( name nvarchar (10 ), subject nvarchar (10 ), result int )
INSERT INTO student0 VALUES (' 張三 ', ' 語文' , 80)
INSERT INTO student0 VALUES (' 張三 ', ' 數(shù)學(xué)' , 90)
INSERT INTO student0 VALUES (' 張三 ', ' 物理' , 85)
INSERT INTO student0 VALUES (' 李四 ', ' 語文' , 85)
INSERT INTO student0 VALUES (' 李四 ', ' 數(shù)學(xué)' , 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 ' 數(shù)學(xué) ' then result
end ),0 ) as '數(shù)學(xué) ' ,
isnull (sum ( case subject
when ' 物理 ' then result
end ),0 ) as '物理 '
from student0
group by [name]

-- 子查詢將一個查詢語句做為一個結(jié)果集供其他 SQL 語句使用，就像使用普通的表一樣，
-- 被當(dāng)作結(jié)果集的查詢語句被稱為子查詢。所有可以使用表的地方幾乎都可以使用子查詢來代替。
use myschool
select sName from ( select * from student ) as t
select 1,( select sum ( english) from score ) as ' 和 ',( select avg ( sAge) from student ) as ' 平均年齡 '
-- 查詢高一一班所有的學(xué)生
select * from student where sClassId =
( select cId from class where cName = '高一一班 ' )
-- 查詢高一一班 高二一班所有的學(xué)生
-- 子查詢返回的值不止一個。當(dāng)子查詢跟隨在 = 、!= 、 、 = 、> 、 >= 之后
-- 子查詢跟在比較運算符之后，要求子查詢只返回一個值
-- 如果子查詢是多行單列的子查詢，這樣的子查詢的結(jié)果集其實是一個集合?？梢允褂?in 關(guān)鍵字代替 =號
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 ( '高一一班 ' ,' 高二一班 '))
-- 查詢劉關(guān)張的成績
select * from score where studentId in
( select sId from student where sName in ( '劉備 ' ,' 關(guān)羽 ', ' 張飛' ))
-- 刪除劉關(guān)張
delete from score where studentId in
( select sId from student where sName in ( '劉備 ' ,' 關(guān)羽 ', ' 張飛' ))

-- 實現(xiàn)分頁
-- 最近入學(xué)的個學(xué)生
select top 3 * from student
order by sId desc
-- 查詢第到個學(xué)生
select top 3 * from student
where sId not in ( select top 3 sId from student order by sId desc)
order by sId desc
-- 查詢到的學(xué)生
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 以前的實現(xiàn)方式。 SQLServer2005 后增加了Row_Number 函數(shù)簡化實現(xiàn)。
--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
-- 內(nèi)連接inner join...on...
select * from student
inner join class on sClassId = cId
select * from class
-- 查詢所有學(xué)生的姓名、年齡及所在班級
select sName , sAge, cName ,sSex from student
inner join class on sClassId = cId
where sSex =' 女 '
-- 查詢年齡超過歲的學(xué)生的姓名、年齡及所在班級
select sName , sAge, cName from class
inner join student on sClassId = cId
where sAge > 20
-- 外連接
--left join...on...
select sName , sAge, cName from class