数据库case，分页

–case使用

case expr
when 值 then 值
when .. then ..
end

case
when 条件 then 值
when 条件 then 值
else 值
end
–表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select * from T8

select
列1=case
when A>B then A
else B
end,
列2=case
when B>C then B
else C
end
from T8

–在订单表中，统计每个销售员的总销售金额，
–列出销售员名、总销售金额、称号（>6000金牌，>5500银牌,>4500铜牌，否则普通）
select * from MyOrders
select
销售员,
销售总金额=sum(销售数量*销售价格),
称号=case
when sum(销售数量*销售价格)>6000 then ‘金’
when sum(销售数量*销售价格)>5500 then ‘银’
when sum(销售数量*销售价格)>4500 then ‘铜’
else ‘普’
end
from MyOrders
group by 销售员

–在成绩表中，查询的时候增加一列，显示“优”、“良”、“中”、“差”
–英语或数学只要有一门功课没有高于70分→ “差”
–两门课都高于70分
–都高于85分
–都高于95分算是优

select * from TblScore
select
*,
等级=
case
when tEnglish>=95 and tMath>=95 then ‘优’
when tEnglish>=85 and tMath>=85 then ‘良’
when tEnglish>=70 and tMath>=70 then ‘中’
else ‘差’
end
from TblScore

–====================
select * from test
select
number,
收入=
case
when amount>0 then amount
else 0
end,
支出=
case
when amount<0 then abs(amount)
else 0
end
from test

–================================
select * from TeamScore
select
teamName,
胜=
sum(case
when gameresult=’胜’ then 1
else 0
end),
负=sum(case when gameresult=’负’ then 1 else 0 end)
from TeamScore
group by teamName

select
*,
teamName,
胜=case when gameresult=’胜’ then 1 else 0 end,
负=case when gameresult=’负’ then 1 else 0 end
from TeamScore
–=====================================================================
select * from nbascore

select
teamName,
第一赛季=max(case when seasonname=’第1赛季’ then score else null end),
第二赛季=max(case when seasonname=’第2赛季’ then score else null end),
第三赛季=max(case when seasonname=’第3赛季’ then score else null end)
from nbascore
group by teamname

———子查询，在一个查询中又包含另外一个查询，这种情况就叫做子查询

select * from (select tsname,tsage,tsgender from TblStudent) as t1

–错误！！！！
–select * from select tsname,tsage,tsgender from TblStudent

–黄忠,关羽,小乔乔
select * from TblScore where tsid in
(select tsid from TblStudent where tsname in (‘黄忠’,’关羽’,’小乔乔’))

select * from TblStudent

select * from TblClass

select tsid,tsgender,tsname,tsclassId from TblStudent
where tsclassId in
(
select tclassId from TblClass where tclassname=’高一一班’ or tclassname=’高二二班’
)

select * from TblStudent as ts where exists
(
select * from TblClass as tc
where tc.tclassId=ts.tsclassId and (tc.tclassname=’高一一班’ or tc.tclassname=’高二二班’)
)

if exists(select ….)

select * from TblStudent
select * from TblScore

select * from Customers

分页

1.通过top来实现,需要先按照指定的规则来排序

–假设每页5条数据

–第一页
select top 5 * from Customers order by CustomerId
–第二页
–1.先找

–第n页的数据，没页5条
–1.先找已经看过的数据
–2.从整个数据中排除已经看过的数据
–3.然后在对未查看过的数据进行排序，取前5条。
select top 5 * from Customers where CustomerId not in
(select top (n-1)*5 CutomerID from Customers order by CustomerId asc)
order by CustomerId asc

select * from Customers order by CustomerId asc

select top 5 * from Customers where CustomerId not in
(select top (8*5) CustomerId from Customers order by CustomerId asc)
order by CustomerId asc

—使用row_number()来进行分页,row_number()是对查询出的结果集进行编号，并不影响表中已有的数据

假设每页5条，要看第n页的数据
(n-1)*5+1 到 n*5

select
row_number() over(order by CustomerId asc) as Rn ,
*
from Customers

where ContactName like ‘m%’

–每页7条，看第4页。
select * from
(
select
row_number() over(order by CustomerId asc) as Rn ,
*
from Customers
) as Tbl where Tbl.Rn between (4-1)*7+1 and 4*7

select * from Customers

over子句演示

over子句只要用在聚合函数，排名函数中，用over子句不用使用order by

select * from MyOrders

select country,count(*) from Customers group by Country

select * from Customers
select CustomerId,country,count(*) from Customers group by Country

select
Customerid,
Country,
count(*) over(partition by Country)
from Customers

select
Customerid,
Country,
count(*) over() –over()中什么都不写，表示把整个表分成了一个区。
from Customers

-