.jpg)
Case 子查詢連接查詢?cè)趕ql開(kāi)發(fā)中很常用的一個(gè)查詢功能,感興趣的你可不要錯(cuò)過(guò)本文，接下來(lái)為你詳細(xì)介紹下
代碼如下:
select * from score
create database demo
use demo
create table [user]
(
[uId] int identity( 1 ,1 ) primary key,
[name] varchar ( 50),
[level] int –1骨灰大蝦菜鳥(niǎo)
)
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 ‘菜鳥(niǎo) ‘
end as ‘等級(jí) ‘
from [user]
use MySchool
select * from score
–case end 第二種用法，相當(dāng)于多重 if 語(yǔ)句
select studentId ,
case
when english >=90 then ‘ 優(yōu) ‘
when english >=80 and english when english >=70 and english when english >= 60 and english else ‘ 差 ‘
end as ‘成績(jī) ‘
from score
order by english
— 表中有A B C 三列 ,用 SQL 語(yǔ)句實(shí)現(xiàn)：當(dāng) A列大于 B 列時(shí)選擇A 列否則選擇 B 列，當(dāng)B 列大于 C列時(shí)選擇 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 else 0
end as ‘支出 ‘
from test
–結(jié)果如下
代碼如下:
— 有一張表student0 ，記錄學(xué)生成績(jī)
use demo
CREATE TABLE student0 ( name nvarchar (10 ), subject nvarchar (10 ), result int )
INSERT INTO student0 VALUES (‘ 張三 ‘, ‘ 語(yǔ)文’ , 80)
INSERT INTO student0 VALUES (‘ 張三 ‘, ‘ 數(shù)學(xué)’ , 90)
INSERT INTO student0 VALUES (‘ 張三 ‘, ‘ 物理’ , 85)
INSERT INTO student0 VALUES (‘ 李四 ‘, ‘ 語(yǔ)文’ , 85)
INSERT INTO student0 VALUES (‘ 李四 ‘, ‘ 數(shù)學(xué)’ , 92)
INSERT INTO student0 VALUES (‘ 李四 ‘, ‘ 物理’ ,null)
select * from student0
select [name] ,
isnull (sum ( case subject
when ‘ 語(yǔ)文 ‘ then result
end ),0 ) as ‘語(yǔ)文 ‘ ,
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]
代碼如下:
— 子查詢將一個(gè)查詢語(yǔ)句做為一個(gè)結(jié)果集供其他 SQL 語(yǔ)句使用，就像使用普通的表一樣，
— 被當(dāng)作結(jié)果集的查詢語(yǔ)句被稱為子查詢。所有可以使用表的地方幾乎都可以使用子查詢來(lái)代替。
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é)生
— 子查詢返回的值不止一個(gè)。當(dāng)子查詢跟隨在 = 、!= 、 、 >= 之后
— 子查詢跟在比較運(yùn)算符之后，要求子查詢只返回一個(gè)值
— 如果子查詢是多行單列的子查詢，這樣的子查詢的結(jié)果集其實(shí)是一個(gè)集合。可以使用 in 關(guān)鍵字代替 =號(hào)
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)張的成績(jī)
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)羽 ‘, ‘ 張飛’ ))
— 實(shí)現(xiàn)分頁(yè)
— 最近入學(xué)的個(gè)學(xué)生
select top 3 * from student
order by sId desc
— 查詢第到個(gè)學(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 以前的實(shí)現(xiàn)方式。 SQLServer2005 后增加了Row_Number 函數(shù)簡(jiǎn)化實(shí)現(xiàn)。
–sql 2005 中的分頁(yè)
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é)生的姓名、年齡及所在班級(jí)
select sName , sAge, cName ,sSex from student
inner join class on sClassId = cId
where sSex =’ 女 ‘
— 查詢年齡超過(guò)歲的學(xué)生的姓名、年齡及所在班級(jí)
select sName , sAge, cName from class
inner join student on sClassId = cId
where sAge > 20
— 外連接
–left join…on…
select sName , sAge, cName from class
.png)
