文章目录
- 14.4 连接和子查询 Joins and Subqueries
- 14.4.1 检索会员预订的开始时间 Retrieve the start times of members’ bookings
- 14.4.2 计算网球场预订的开始时间 Work out the start times of bookings for tennis courts
- 14.4.3 生成推荐过其他成员的所有成员的列表 Produce a list of all members who have recommended another member
- 14.4.4 生成所有成员的列表,以及他们的推荐人 Produce a list of all members, along with their recommender
- 14.4.5 制作所有使用过网球场的成员的名单 Produce a list of all members who have used a tennis court
- 14.4.6 制作一份昂贵的预订清单 Produce a list of costly bookings
- 14.4.7 生成所有成员及其推荐人的列表,不使用连接。Produce a list of all members, along with their recommender, using no joins.
- 14.4.8 使用子查询生成昂贵的预订列表 Produce a list of costly bookings, using a subquery
14.4 连接和子查询 Joins and Subqueries
此类别主要涉及关系数据库系统中的一个基本概念:连接。加入允许您组合来自多个表的相关信息来回答问题。这不仅有利于便于查询:缺乏连接功能会鼓励数据的非规范化,这增加了保持数据内部一致性的复杂性。
本主题涵盖内部连接、外部连接和自连接,以及在子查询(查询中的查询)上花费一些时间。
14.4.1 检索会员预订的开始时间 Retrieve the start times of members’ bookings
您如何生成名为“David Farrell”的成员的预订开始时间列表?
SELECT starttime FROM bookings
INNER JOIN members ON (bookings.memid = members.memid)
WHERE surname = 'Farrell' AND firstname = 'David';
query = (Booking
.select(Booking.starttime)
.join(Member)
.where((Member.surname == 'Farrell') &
(Member.firstname == 'David')))
14.4.2 计算网球场预订的开始时间 Work out the start times of bookings for tennis courts
您如何生成日期为“2012-09-21”的网球场预订开始时间列表?返回按时间排序的开始时间和设施名称配对列表。
SELECT starttime, name
FROM bookings
INNER JOIN facilities ON (bookings.facid = facilities.facid)
WHERE date_trunc('day', starttime) = '2012-09-21':: date
AND name ILIKE 'tennis%'
ORDER BY starttime, name;
query = (Booking
.select(Booking.starttime, Facility.name)
.join(Facility)
.where(
(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 21)) &
Facility.name.startswith('Tennis'))
.order_by(Booking.starttime, Facility.name))
# To retrieve the joined facility's name when iterating:
for booking in query:
print(booking.starttime, booking.facility.name)
14.4.3 生成推荐过其他成员的所有成员的列表 Produce a list of all members who have recommended another member
如何输出推荐过其他成员的所有成员的列表?确保列表中没有重复项,并且结果按(姓、名)排序。
SELECT DISTINCT m.firstname, m.surname
FROM members AS m2
INNER JOIN members AS m ON (m.memid = m2.recommendedby)
ORDER BY m.surname, m.firstname;
MA = Member.alias()
query = (Member
.select(Member.firstname, Member.surname)
.join(MA, on=(MA.recommendedby == Member.memid))
.order_by(Member.surname, Member.firstname))
14.4.4 生成所有成员的列表,以及他们的推荐人 Produce a list of all members, along with their recommender
您如何输出所有成员的列表,包括推荐他们的个人(如果有)?确保结果按(姓、名)排序。
SELECT m.firstname, m.surname, r.firstname, r.surname
FROM members AS m
LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid)
ORDER BY m.surname, m.firstname
MA = Member.alias()
query = (Member
.select(Member.firstname, Member.surname, MA.firstname, MA.surname)
.join(MA, JOIN.LEFT_OUTER, on=(Member.recommendedby == MA.memid))
.order_by(Member.surname, Member.firstname))
# To display the recommender's name when iterating:
for m in query:
print(m.firstname, m.surname)
if m.recommendedby:
print(' ', m.recommendedby.firstname, m.recommendedby.surname)
14.4.5 制作所有使用过网球场的成员的名单 Produce a list of all members who have used a tennis court
您如何制作所有使用过网球场的会员的名单?在您的输出中包含法院的名称,以及格式化为单列的成员名称。确保没有重复数据,并按成员名称排序。
SELECT DISTINCT m.firstname || ' ' || m.surname AS member, f.name AS facility
FROM members AS m
INNER JOIN bookings AS b ON (m.memid = b.memid)
INNER JOIN facilities AS f ON (b.facid = f.facid)
WHERE f.name LIKE 'Tennis%'
ORDER BY member, facility;
fullname = Member.firstname + ' ' + Member.surname
query = (Member
.select(fullname.alias('member'), Facility.name.alias('facility'))
.join(Booking)
.join(Facility)
.where(Facility.name.startswith('Tennis'))
.order_by(fullname, Facility.name)
.distinct())
14.4.6 制作一份昂贵的预订清单 Produce a list of costly bookings
您如何制作一份 2012 年 9 月 14 日当天将花费会员(或客人)超过 30 美元的预订清单?请记住,客人对会员有不同的费用(列出的费用是每半小时的“时段”),客人用户的 ID 始终为 0。在您的输出中包括设施的名称,格式为单个的成员名称列和成本。按成本降序排列,不要使用任何子查询。
SELECT m.firstname || ' ' || m.surname AS member,
f.name AS facility,
(CASE WHEN m.memid = 0 THEN f.guestcost * b.slots
ELSE f.membercost * b.slots END) AS cost
FROM members AS m
INNER JOIN bookings AS b ON (m.memid = b.memid)
INNER JOIN facilities AS f ON (b.facid = f.facid)
WHERE (date_trunc('day', b.starttime) = '2012-09-14') AND
((m.memid = 0 AND b.slots * f.guestcost > 30) OR
(m.memid > 0 AND b.slots * f.membercost > 30))
ORDER BY cost DESC;
cost = Case(Member.memid, (
(0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost))
fullname = Member.firstname + ' ' + Member.surname
query = (Member
.select(fullname.alias('member'), Facility.name.alias('facility'),
cost.alias('cost'))
.join(Booking)
.join(Facility)
.where(
(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)) &
(cost > 30))
.order_by(SQL('cost').desc()))
# To iterate over the results, it might be easiest to use namedtuples:
for row in query.namedtuples():
print(row.member, row.facility, row.cost)
14.4.7 生成所有成员及其推荐人的列表,不使用连接。Produce a list of all members, along with their recommender, using no joins.
如何在不使用任何连接的情况下输出所有成员的列表,包括推荐他们的个人(如果有)?确保列表中没有重复项,并且每个名字 + 姓氏配对都被格式化为一列并按顺序排列。
SELECT DISTINCT m.firstname || ' ' || m.surname AS member,
(SELECT r.firstname || ' ' || r.surname
FROM cd.members AS r
WHERE m.recommendedby = r.memid) AS recommended
FROM members AS m ORDER BY member;
MA = Member.alias()
subq = (MA
.select(MA.firstname + ' ' + MA.surname)
.where(Member.recommendedby == MA.memid))
query = (Member
.select(fullname.alias('member'), subq.alias('recommended'))
.order_by(fullname))
14.4.8 使用子查询生成昂贵的预订列表 Produce a list of costly bookings, using a subquery
“生成昂贵的预订清单”练习包含一些混乱的逻辑:我们必须在 WHERE 子句和 CASE 语句中计算预订成本。尝试使用子查询来简化这个计算。
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 members AS m
INNER JOIN bookings AS b ON m.memid = b.memid
INNER JOIN facilities AS f ON b.facid = f.facid
WHERE date_trunc('day', b.starttime) = '2012-09-14'
) as bookings
WHERE cost > 30
ORDER BY cost DESC;
cost = Case(Member.memid, (
(0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost))
iq = (Member
.select(fullname.alias('member'), Facility.name.alias('facility'),
cost.alias('cost'))
.join(Booking)
.join(Facility)
.where(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)))
query = (Member
.select(iq.c.member, iq.c.facility, iq.c.cost)
.from_(iq)
.where(iq.c.cost > 30)
.order_by(SQL('cost').desc()))
# To iterate, try using dicts:
for row in query.dicts():
print(row['member'], row['facility'], row['cost'])