本文主要介绍下 postgreSQL一些稍微复杂的语法。
假设目前有表 cd.facilities、cd.members、cd.bookings，具体表内信息，可进入任务页面，通过 SELECT * FROM 表名;查询表内所有内容，此处不一一赘述。
| 序号 | 实现功能 | 关键语法 | 实现步骤 | 完整语句 |
|---|---|---|---|---|
| 1 | 1. 从表 cb.bookings 筛选出 starttime 2. starttime 是表 cd.members 中 surname = 'Farrell' && firstname = 'David'的数据 |
1.JOIN...ON:两表中取交集，等同于 INNER JOIN ... ON,其他可用 left join, right join 2. AS
|
1. select b.starttime from cb.bookings as b join cb.members as b on b.memid = m.memid;: 筛选出两表中 memid 一致的数据；2. where m.surname='Farrell' and m.firstname = 'David';：添加限制条件； |
select b.starttime from cd.bookings as b join cd.members as m on b.memid = m.memid where m.surname = 'Farrell' and m.firstname = 'David'; |
| 2 | 1.从表 cd.bookings 筛选出 starttime 、表 cd.facilities 中筛选出 name 2. starttime 的时间范围为 '2012-09021'当天，按照 starttime 排序3.且 name 的名字为 tennis courts
|
1. join...on 2. as3. where4. like
|
1.select b.starttime as start, f.name from cd.bookings as b join cd.facilities as f on b.facid = f.facid：筛选出两个表中 facid 相等的数据2. where f.name like 'Tennis%' AND b.starttime >='2012-09-21' and b.starttime < '2012-09-22'：添加限制条件 |
select b.starttime as start, f.name from cd.bookings as b join cd.facilities as f on b.facid = f.facid where f.name like 'Tennis%' AND b.starttime < '2012-09-22' and b.starttime >= '2012-09-21' order by b.starttime; |
| 3 | 1.从表cd.members中筛选出 firstname、surname 2. 筛选出的数据需要满足要求：此条数据的用户推荐过其他用户，及 cd.members 中 memid === recommendedby3.数据按照 surname、firstname 排序4.数据不能有重复 |
1.join...on2. as3. order by4. distinct
|
1.select m1.firstname,m1.surname from cd.members as m1 join cd.members as m2 on m1.memid = m2.recommendedby：初步筛选数据2. order by surname, firstname：添加排序 3. 在 select后添加关键字distinct 过滤重复数据 |
select distinct m.firstname, m.surname from cd.members as m join cd.members as m2 on m.memid = m2.recommendedby order by surname, firstname; |
| 4 | 1.从现有的表中列出哪些用户使用过 Tennis Court2.筛选出 member 作为用户的 firstname + surname，facility 作为场地具体信息3.按照 member 排序4.数据不可以重复 |
1.join...on...join...on2. as3. order by4. distinct5. in or like6.主要考察关联多个表，使用 select xx from table1 join table2 on table1.x = table2.y join table3 on table2.xx = table3.yy where ***
|
1.select * from cd.facilities as f join cd.bookings as b on f.facid = b.facid join cd.members as m on b.memid = m.memid从三个表中筛选中交集数据2.设置表格数据组成： m.firstname as membre：将 firstname和surname组合成 member3. where f.facid in (0,1)：筛选出使用过 Tennis court 的数据，也可使用where f.facility like 'Tennis%'4. order by membre和distinct分别用来排序和过滤重复数据 |
示例如下： |
select distinct m.firstname || ' ' || m.surname as member, f.name as facility
from
cd.facilities as f join cd.bookings as b
on f.facid = b.facid
join cd.members as m
on b.memid = m.memid
where f.facid in (0,1)
order by member;
| 序号 | 实现功能 | 关键语法 | 实现步骤 | 完整语句 |
|---|---|---|---|---|
| 5 | 1.从表中筛选出 2012-9-14 日预定场地花费大于 30 的用户信息2.信息包含由 fistname和lastname组成的member，场地信息facility和花费的金额3.按照金额降序排序 |
1.join...on...join...on2. case when ... then ... else ... end ...3. order by、desc
|
1.过滤数据:select * from cd.facilities as f join cd.bookings as b on f.memid = b.memid join cd.facilities as f on b.facid = f.facid，取交集2.计算 cost，游客和会员的 cost 不同，case when m.memid = 0 then b.slots * f.guestcost else b.slots * membercost end as cost3.过滤时间： where b.starttime > '2012-09-14' and b.starttime < '2012-09-15'4、过滤消费金额： where (m.memid = 0 and b.slots * f.guestcost > 30) or (m.memid != 0 and b.slots * f.membercost > 0)5. order by cost desc：按照金额降序排序 |
代码示例如下 |
select m.firstname || ' ' || m.surname as member,
f.name as facility,
case
when m.memid = 0 then
b.slots * f.guestcost
else
b.slots * f.membercost
end as cost
from cd.bookings as b
join cd.members as m
on b.memid = m.memid
join cd.facilities as f
on f.facid = b.facid
where starttime > '2012-09-14' and starttime < '2012-09-15'
and ((m.memid = 0 and b.slots * f.guestcost > 30) or (m.memid != 0 and b.slots * f.membercost > 30))
order by cost desc;
| 序号 | 实现功能 | 关键语法 | 实现步骤 | 完整语句 |
|---|---|---|---|---|
| 6 | 1.从cd.members中将用户的推荐人信息列出2.无推荐人可为空 3. firstname和lastname组成member，为用户信息，recommender也由 fristname和surname组成，为推荐人信息4.不可使用 join...on5.删除重复数据，按照 member排序 |
1.双重select2. order、distinct
|
1.筛选出用户信息:selct distinct m.firstname as member from cd.members as m order by member;2.在 select 中嵌套 select：selct rec.member from cd.members as rec where rec.memid = m.recommendedby |
示例如下 |
select distinct m.firstname || ' ' || m.surname as member,
(select rec.firstname || ' ' || rec.surname as recommender
from cd.members as rec
where rec.memid = m.recommendedby
)
from cd.members as m
order by member;
| 序号 | 实现功能 | 关键语法 | 实现步骤 | 完整语句 |
|---|---|---|---|---|
| 7 | 1.实现查询结果如示例5所示 2.使用双重 select简化语句 |
1.双重select
|
直接看示例 |
select member, facility, cost from
(select
m.firstname || ' ' || m.surname as member,
f.name as facility,
case when m.memid = 0
then b.slots * f.guestcost
else b.slots * f.membercost
end
as cost
from
cd.members as m
join cd.bookings as b on b.memid = m.memid
join cd.facilities as f on f.facid = b.facid
where b.starttime > '2012-09-14' and b.starttime < '2012-09-15'
) as bookings
where cost > 30
order by cost desc;
大部分内容主要来自PostgreSQL Exercises。
网友评论