以下出现sql练习题E-R关系图如下：
0.写在之前
尽管是一些比较基础的sql练习题，希望通过记录总结有所提升。
https://www.postgresql.org/docs/9.3/ postgresql 手册，内含详细函数语法
http://www.postgres.cn/docs/9.6/ 中文手册
https://blog.csdn.net/qq_42322103/article/details/100139708 csdn大神教你如何检索信息
1.关于一个sql语句执行顺序
(1)from
(2) join
(3) on
(4) where
(5)group by
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by
在对一个比较复杂的sql语句进行解析时，必须要搞清楚其执行顺序，才能真正理解运行流程，从而根据自己的需求反向构造一个sql语句，由于顺序中其中每一步为下一步查询生成了一张表结构，故from之后的语句要为之前的语句提供查询表而先执行，之后进行对from中表的连接操作（join，on，where之类），聚集函数进一步加工表，在此基础上执行聚集函数，之后一个可能为之后的递归查询提供了表，一步一步运行得出了结果。
2.关于with as 即公用表表达式（CTE）
(1)定义一个SQL片断，该SQL片断会被整个SQL语句所用到，有助于程序的可读性；
(2)CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用；
(3)as 之后的select要加（）；
(4)有多组as 时要以“，”进行分割；
(5)如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了；
(6)CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效；
(7)with recursive递归查询：
(8)递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。
eg.Question
Find成员ID 27的向上推荐链：即推荐它们的成员，以及推荐该成员的成员，等等。返回会员ID，名字和姓氏。按降序成员ID排序。
with recursive ar(memid,surname,firstname,recommendedby) as (select b.memid,b.surname,b.firstname,b.recommendedby from members a,members b where a.memid=27 and a.recommendedby=b.memid
union all select c.memid ,c.surname,c.firstname ,c.recommendedby from members as c join ar as an on an.recommendedby =c.memid)
select memid as recommendedby,firstname,surname from ar order by recommendedby desc
//递归调用，当第二个递归查询返回空结果集即没有向上推荐链时停止运行返回结果
3.关于rank()over(order by …)函数
按排序返回附加一列从1开始的rank排序，值相同则rank相同
rank() over是的作用是查出指定条件后进行一个排名，但是有一个特点。假如是对学生排名，那么实用这个函数，成绩相同的两名是并列。
dense_rank()的作用和rank()很像，唯一的一点区别就是，领命学生的成绩并列以后，下一位同学并不空出并列所占的名次。
row_number()就不一样了，它和上面两种的区别就很明显了，这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名。
eg.Question
根据收入，将设备平均分为高，平均和低三个等级。按等级和设施名称排序。
结果预期图如下：
解答如下:
with a as (select name,rank()over(order by sum(case when memid=0 then slots*guestcost else slots*membercost end) desc)as rank from facilities natural join bookings natural join members group by name ),
b as (select name,(select count(distinct name) from facilities) as num from facilities )
select name,(case when rank<=num/3 then 'high' else (case when rank>num/3*2 then 'low' else 'average'end)end) as revenue from a natural join b
order by rank,name
//其中用rank给排序并作为判断所处revenue的依据
4.关于"group by 字段列表"
表示根据后面的字段来分组,如果只有1个字段,那只是根据这个字段的值来进行一次分组就可以了；若后面有多个字段,那表示根据多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,然后在第1个字段值相同的记录中,再根据第2个字段的值进行分组；接着第2个字段值相同的记录中,再根据第3个字段的值进行分组.
(1) 出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.
(2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having
eg.Question
对于每一个会员，查找他们在2012年9月1日起的第一个订单。列表包含每个会员名称，ID和首次预订时间。按会员ID排序。
select surname,firstname, memid,min(starttime) from
bookings natural join members
where starttime>timestamp'2012-09-01'
group by surname,firstname,memid
order by memid
为什么group by 不能直接只用memid？
在聚集时，因为select要展示surname,firstname,如果只有group by memid 将该会报错，因为操作对象memid合并了，但是surname,firstname并无操作，无法出现查询结果，要么不选中surname,firstname，要么令其出现在group by 中，而在select中的聚集函数则是在聚集操作后进行运算的。
5.关于string用到的几个函数
eg.Question
计算设备总收入。输出表应包括设备名称和收入，按收入排序。请记住，会员和非会员的费用不同！
select a.name , sum(a.b) as revenue from
(select name , (case when memid = 0 then guestcost * slots else membercost * slots end) as b
from bookings join facilities on facilities.facid = bookings.facid) as a group by a.name
order by revenue;`
right函数的功能是从字符串右端取指定个数字符。
语法Right ( string, n ) 。
函数执行成功时返回string字符串右边n个字符，发生错误时返回空字符串（""）。如果任何参数的值为NULL，Right()函数返回NULL。如果n的值大于string字符串的长度，那么Right()函数返回整个string字符串，但并不增加其它字符。
concat函数
CONCAT(字串1, 字串2, 字串3, …): 将字串1、字串2、字串3，等字串连在一起。
eg.Question
数据库中的电话号码格式非常不一致。您要打印已删除“ - ”，“（”，“）”和“ ”字符后的会员ID和数字列表。按会员ID排序。
select a.memid , replace(a.telephone,'(','') as telephone from
(select b.memid , replace(b.telephone,')','') as telephone from
(select c.memid , replace(c.telephone,' ','') as telephone from
(select memid , replace(telephone,'-','') as telephone from members ) as c)as b )as a;
6.关于date的几个函数
eg.Question
为2012年8月的每一天，计算过去15天内总收入的滚动平均值。输出应包含日期和收入列，按日期排序。记住要考虑一天收入为零的可能性。这个有点难，不要害怕，请查看提示！
with a(d) as (select generate_series(timestamp'2012-08-01 00:00:00', timestamp'2012-08-31 00:00:00', '1 day') as date)
select date(a.d) as date, ( select sum(cost)/15 from ( select sum(slots*membercost) as cost from bookings natural join facilities natural join members where memid <> 0 and starttime between a.d - interval '14 days' and a.d+'24 hours' union select sum(slots*guestcost) as cost from bookings natural join facilities natural join members where memid = 0 and starttime between a.d - interval '14 days' and a.d+'24 hours' )
as bb )as revenue from a order by a.d;
//引用了generate_series，按天生成间隔列表，为下面的查询提供条件
eg.Question
计算 在时间戳’2012-08-31 01:00:00’和’2012-09-02 00:00:00’之间的秒数
select extract(epoch from (timestamp'2012-09-02 00:00:00'-timestamp'2012-08-31 01:00:00'))
//extract得到的非timestamp数据格式，仅为数字
eg.Question
返回每月订单的数量，按月份排序
select month,count(*) from
(select date_trunc('month',starttime) as month from bookings) as a
group by month
order by month
//子查询，date_turnc()用于截断日期，截断之后默认全零，属于timestamp格式可进行后续操作
eg.Question
对于任何给定的时间戳，计算当月剩余的天数。无论是当天中的任何时间都应算作一整天。使用“2012-02-11 01:00:00”作为示例时间戳以进行回答。将输出格式化为单个值。
select date_trunc('month',timestamp'2012-02-11 01:00:00')+ '1 month'-date_trunc('day',timestamp'2012-02-11 01:00:00')
as remaining;
//针对时间时间戳的运算，加引号即可，得到的结果是一个时间戳
eg.Question
:对于2012年度的每一个月，输出该月的天数。将输出格式化为第一列是该年的月份，第二列是天数。
select extract(month from t) as month , concat(date_part('days',date_trunc('month',t)+'1 month'-'1 day'),' ','days')
as length
from generate_series(timestamp'2012-01-01 00:00:00',timestamp '2012-12-31 00:00:00', '1 month') as t;
//from表单应该是每个月的一个列表，其中将其月份抽离extract（）
计算天数date_part,组合字符串concat（）。
eg.Question
不使用子查询的预订清单包含一些混乱的逻辑：我们必须在WHERE子句和CASE语句中计算预订成本。尝试使用子查询简化此计算。作为参考，问题是：如何制作”2012-09-14”当天的一份预订清单，清单中会员（或客人）的费用超过30美元？请记住，访客对成员的成本不同（列出的成本 1 slot = 0.5h），访客用户始终为ID 0. 在输出中包含设备名称，成员名称格式为单列,还有费用。按花费的价格降序排序。低成本排序。
select concat(firstname,' ',surname) as member, name as facility , type*slots as cost
from
(select starttime,firstname,surname,name,slots,(case when memid=0 then guestcost else membercost end) as type
from members natural join bookings natural join facilities) as a
where type*slots >30 and date_trunc('day',a.starttime)=timestamp'2012-09-14'
order by cost desc;
eg.Question
slots是2012年每个月每个设备所订购的slot的总数。在这个版本中，输出行包含每个设备的所有月份的总计，以及所有月份所有设备的总计（最后一行）。输出表应包含设备ID，月份和slots，按ID和月份排序。保留空值。
(select facid,extract(month from starttime) as month ,sum(slots) as slots from
bookings as a where extract(year from a.starttime)=2012 group by facid,month)
union
(select facid,null as month,sum(slots) as slots from bookings where extract(year from starttime)=2012 group by facid)
union
(select null as facid,null as month,sum(slots) as slots from bookings where extract(year from starttime)=2012 )
order by facid,month
eg.Question
按月计算每个设备的利用率百分比，按名称和月份排序，利用率四舍五入到小数点后1位。打开时间是早上8点，关闭时间是晚上8点30分。你可以将每月视为整月，无论俱乐部是否有一些日期未开放。
Expected Results
name month utilisation
Badminton Court 2012-07-01 00:00:00 23.2
Badminton Court 2012-08-01 00:00:00 59.2
Badminton Court 2012-09-01 00:00:00 76.0
Massage Room 1 2012-07-01 00:00:00 34.1
Massage Room 1 2012-08-01 00:00:00 63.5
Massage Room 1 2012-09-01 00:00:00 86.4
Massage Room 2 2012-07-01 00:00:00 3.1
Massage Room 2 2012-08-01 00:00:00 10.6
Massage Room 2 2012-09-01 00:00:00 16.3
Pool Table 2012-07-01 00:00:00 15.1
Pool Table 2012-08-01 00:00:00 41.5
Pool Table 2012-09-01 00:00:00 62.8
Pool Table 2013-01-01 00:00:00 0.1
Snooker Table 2012-07-01 00:00:00 20.1
Snooker Table 2012-08-01 00:00:00 42.1
Snooker Table 2012-09-01 00:00:00 56.8
Squash Court 2012-07-01 00:00:00 21.2
Squash Court 2012-08-01 00:00:00 51.6
Squash Court 2012-09-01 00:00:00 72.0
Table Tennis 2012-07-01 00:00:00 13.4
Table Tennis 2012-08-01 00:00:00 39.2
Table Tennis 2012-09-01 00:00:00 56.3
Tennis Court 1 2012-07-01 00:00:00 34.8
Tennis Court 1 2012-08-01 00:00:00 59.2
Tennis Court 1 2012-09-01 00:00:00 78.8
Tennis Court 2 2012-07-01 00:00:00 26.7
Tennis Court 2 2012-08-01 00:00:00 62.3
Tennis Court 2 2012-09-01 00:00:00 78.4
sql语句
with da as (select a as months,date_part('days',a+'1 month'-a) as length
from generate_series(timestamp'2012-07-01',timestamp'2013-1-30','1 month') as a),
co as (select name,date_trunc('month', starttime) as month,sum(slots) from bookings natural join facilities group by name,month )
select name,month,round(sum*4/length::numeric,1) as utilisation from co join da on co.month=da.months order by name,month
设备利用率即设施当月运行总小时数除以每天运行12.5小时*当月天数
故需要每月天数信息，每月设施总数的两个表信息，在select中计算即可得出答案
扫一扫
752
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
