查询示例库¶

这些查询示例取自 Postgresql Exercises 网站。示例数据集可在入门页面找到。

直接下载: clubdata.sql

本页内容

模型定义
Schema 创建
基础练习
连接和子查询
修改数据
聚合
日期和时间
字符串
递归

模型定义 ¶

以下是这些示例中使用的 schema 的可视化表示

为了开始使用数据，我们将定义与图中表格对应的模型类。

注意

在某些情况下，我们为特定字段显式指定列名。这是为了使我们的模型与 Postgres 练习中使用的数据库 schema 兼容。

from functools import partial
from peewee import *


db = PostgresqlDatabase('peewee_test')

class BaseModel(Model):
    class Meta:
        database = db

class Member(BaseModel):
    memid = AutoField()  # Auto-incrementing primary key.
    surname = CharField()
    firstname = CharField()
    address = CharField(max_length=300)
    zipcode = IntegerField()
    telephone = CharField()
    recommendedby = ForeignKeyField('self', backref='recommended',
                                    column_name='recommendedby', null=True)
    joindate = DateTimeField()

    class Meta:
        table_name = 'members'


# Conveniently declare decimal fields suitable for storing currency.
MoneyField = partial(DecimalField, decimal_places=2)


class Facility(BaseModel):
    facid = AutoField()
    name = CharField()
    membercost = MoneyField()
    guestcost = MoneyField()
    initialoutlay = MoneyField()
    monthlymaintenance = MoneyField()

    class Meta:
        table_name = 'facilities'


class Booking(BaseModel):
    bookid = AutoField()
    facility = ForeignKeyField(Facility, column_name='facid')
    member = ForeignKeyField(Member, column_name='memid')
    starttime = DateTimeField()
    slots = IntegerField()

    class Meta:
        table_name = 'bookings'

Schema 创建 ¶

如果您从 Postgresql Exercises 网站下载了 SQL 文件，则可以使用以下命令将数据加载到 Postgresql 数据库中

createdb peewee_test
psql -U postgres -f clubdata.sql -d peewee_test -x -q

要使用 Peewee 创建 schema，而不加载示例数据，您可以运行以下命令

# Assumes you have created the database "peewee_test" already.
db.create_tables([Member, Facility, Booking])

基础练习 ¶

此类别涉及 SQL 的基础知识。它涵盖了 select 和 where 子句、case 表达式、union 以及其他一些零碎内容。

检索所有内容¶

从 facilities 表中检索所有信息。

SELECT * FROM facilities

# By default, when no fields are explicitly passed to select(), all fields
# will be selected.
query = Facility.select()

从表中检索特定列¶

检索设施名称和会员费用。

SELECT name, membercost FROM facilities;

query = Facility.select(Facility.name, Facility.membercost)

# To iterate:
for facility in query:
    print(facility.name)

控制检索哪些行¶

检索对会员收费的设施列表。

SELECT * FROM facilities WHERE membercost > 0

query = Facility.select().where(Facility.membercost > 0)

控制检索哪些行 - 第2部分¶

检索对会员收费，且费用低于每月维护成本的1/50的设施列表。返回 id、名称、成本和每月维护费。

SELECT facid, name, membercost, monthlymaintenance
FROM facilities
WHERE membercost > 0 AND membercost < (monthlymaintenance / 50)

query = (Facility
         .select(Facility.facid, Facility.name, Facility.membercost,
                 Facility.monthlymaintenance)
         .where(
             (Facility.membercost > 0) &
             (Facility.membercost < (Facility.monthlymaintenance / 50))))

基本字符串搜索¶

如何生成一份名称中包含“Tennis”字样的所有设施列表？

SELECT * FROM facilities WHERE name ILIKE '%tennis%';

query = Facility.select().where(Facility.name.contains('tennis'))

# OR use the exponent operator. Note: you must include wildcards here:
query = Facility.select().where(Facility.name ** '%tennis%')

匹配多个可能的值¶

如何检索 ID 为 1 和 5 的设施的详细信息？尝试不使用 OR 运算符来完成。

SELECT * FROM facilities WHERE facid IN (1, 5);

query = Facility.select().where(Facility.facid.in_([1, 5]))

# OR:
query = Facility.select().where((Facility.facid == 1) |
                                (Facility.facid == 5))

将结果分类到桶中¶

如何生成一份设施列表，根据其每月维护成本是否超过 $100 将每个设施标记为“便宜”或“昂贵”？返回相关设施的名称和每月维护费。

SELECT name,
CASE WHEN monthlymaintenance > 100 THEN 'expensive' ELSE 'cheap' END
FROM facilities;

cost = Case(None, [(Facility.monthlymaintenance > 100, 'expensive')], 'cheap')
query = Facility.select(Facility.name, cost.alias('cost'))

注意

有关更多示例，请参见文档 Case。

处理日期¶

如何生成一份在 2012 年 9 月初之后加入的会员列表？返回相关会员的 memid、姓氏、名字和加入日期。

SELECT memid, surname, firstname, joindate FROM members
WHERE joindate >= '2012-09-01';

query = (Member
         .select(Member.memid, Member.surname, Member.firstname, Member.joindate)
         .where(Member.joindate >= datetime.date(2012, 9, 1)))

删除重复项并排序结果¶

如何生成一份 members 表中前 10 个姓氏的有序列表？该列表不得包含重复项。

SELECT DISTINCT surname FROM members ORDER BY surname LIMIT 10;

query = (Member
         .select(Member.surname)
         .order_by(Member.surname)
         .limit(10)
         .distinct())

合并来自多个查询的结果¶

由于某种原因，您需要一份所有姓氏和所有设施名称的组合列表。

SELECT surname FROM members UNION SELECT name FROM facilities;

lhs = Member.select(Member.surname)
rhs = Facility.select(Facility.name)
query = lhs | rhs

查询可以使用以下运算符组合

| - UNION
+ - UNION ALL
& - INTERSECT
- - EXCEPT

简单聚合¶

您想获取最后一位会员的注册日期。如何检索此信息？

SELECT MAX(join_date) FROM members;

query = Member.select(fn.MAX(Member.joindate))
# To conveniently obtain a single scalar value, use "scalar()":
# max_join_date = query.scalar()

连接和子查询 ¶

此类别主要处理关系数据库系统中的一个基础概念：连接。连接允许您组合来自多个表的相关信息来回答问题。这不仅有利于查询的便捷性：缺乏连接能力会鼓励数据非规范化，从而增加保持数据内部一致性的复杂性。

本主题涵盖内连接、外连接和自连接，并简要介绍了子查询（查询中的查询）。

检索会员预订的开始时间¶

如何生成一份名为“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')))

计算网球场预订的开始时间¶

如何生成一份 2012 年 9 月 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)

生成一份推荐过其他会员的所有会员列表¶

如何输出所有推荐过其他会员的会员列表？确保列表中没有重复项，并且结果按（姓氏、名字）排序。

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))

生成一份所有会员及其推荐人列表¶

如何输出所有会员（包括推荐人，如果有的话）的列表？确保结果按（姓氏、名字）排序。

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)

生成一份所有使用过网球场的会员列表¶

如何生成一份所有使用过网球场的会员列表？输出中应包含网球场名称以及格式化为单列的会员姓名。确保没有重复数据，并按会员姓名排序。

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())

生成一份昂贵预订列表¶

如何生成一份 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)

生成一份所有会员及其推荐人列表，不使用任何连接。¶

如何输出所有会员（包括推荐人，如果有的话）的列表，而不使用任何连接？确保列表中没有重复项，并且每个名字 + 姓氏配对都格式化为一列并排序。

SELECT DISTINCT m.firstname || ' ' || m.surname AS member,
   (SELECT r.firstname || ' ' || r.surname
    FROM 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))

使用子查询生成一份昂贵预订列表¶

“生成一份昂贵预订列表”练习包含一些混乱的逻辑：我们必须在 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'])

修改数据 ¶

查询数据固然很好，但在某个时候，您可能需要将数据放入数据库中！本节处理信息的插入、更新和删除。像这样修改数据的操作统称为数据操纵语言（Data Manipulation Language），简称 DML。

在前面的部分中，我们向您返回了所执行查询的结果。由于本节中进行的修改不会返回任何查询结果，因此我们转而向您展示您正在操作的表的更新内容。

向表中插入数据¶

俱乐部正在新增一项设施——一个水疗中心。我们需要将其添加到 facilities 表中。使用以下值：facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800

INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
"initialoutlay", "monthlymaintenance") VALUES (9, 'Spa', 20, 30, 100000, 800)

res = Facility.insert({
    Facility.facid: 9,
    Facility.name: 'Spa',
    Facility.membercost: 20,
    Facility.guestcost: 30,
    Facility.initialoutlay: 100000,
    Facility.monthlymaintenance: 800}).execute()

# OR:
res = (Facility
       .insert(facid=9, name='Spa', membercost=20, guestcost=30,
               initialoutlay=100000, monthlymaintenance=800)
       .execute())

向表中插入多行数据¶

在之前的练习中，您学习了如何添加一个设施。现在您将通过一条命令添加多个设施。使用以下值

facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800。

facid: 10, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80。

INSERT INTO "facilities" (...)
VALUES (9, ...), (10, ...);

data = [
    {'facid': 9, 'name': 'Spa', 'membercost': 20, 'guestcost': 30,
     'initialoutlay': 100000, 'monthlymaintenance': 800},
    {'facid': 10, 'name': 'Squash Court 2', 'membercost': 3.5,
     'guestcost': 17.5, 'initialoutlay': 5000, 'monthlymaintenance': 80}]
res = Facility.insert_many(data).execute()

向表中插入计算数据¶

让我们再次尝试将水疗中心添加到 facilities 表中。不过这次，我们希望自动生成下一个 facid 的值，而不是将其指定为常量。其余使用以下值：Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800。

INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
  "initialoutlay", "monthlymaintenance")
SELECT (SELECT (MAX("facid") + 1) FROM "facilities") AS _,
        'Spa', 20, 30, 100000, 800;

maxq = Facility.select(fn.MAX(Facility.facid) + 1)
subq = Select(columns=(maxq, 'Spa', 20, 30, 100000, 800))
res = Facility.insert_from(subq, Facility._meta.sorted_fields).execute()

更新现有数据¶

我们在输入第二个网球场数据时犯了一个错误。初始支出是 10000 而不是 8000：您需要修改数据来修复错误。

UPDATE facilities SET initialoutlay = 10000 WHERE name = 'Tennis Court 2';

res = (Facility
       .update({Facility.initialoutlay: 10000})
       .where(Facility.name == 'Tennis Court 2')
       .execute())

# OR:
res = (Facility
       .update(initialoutlay=10000)
       .where(Facility.name == 'Tennis Court 2')
       .execute())

同时更新多行和多列¶

我们希望提高网球场对会员和访客的价格。将会员费用更新为 6，访客费用更新为 30。

UPDATE facilities SET membercost=6, guestcost=30 WHERE name ILIKE 'Tennis%';

nrows = (Facility
         .update(membercost=6, guestcost=30)
         .where(Facility.name.startswith('Tennis'))
         .execute())

根据另一行的内容更新行¶

我们想修改第二个网球场的价格，使其比第一个贵 10%。尝试不使用价格的常量值来完成此操作，以便我们可以在需要时重用该语句。

UPDATE facilities SET
membercost = (SELECT membercost * 1.1 FROM facilities WHERE facid = 0),
guestcost = (SELECT guestcost * 1.1 FROM facilities WHERE facid = 0)
WHERE facid = 1;

-- OR --
WITH new_prices (nmc, ngc) AS (
  SELECT membercost * 1.1, guestcost * 1.1
  FROM facilities WHERE name = 'Tennis Court 1')
UPDATE facilities
SET membercost = new_prices.nmc, guestcost = new_prices.ngc
FROM new_prices
WHERE name = 'Tennis Court 2'

sq1 = Facility.select(Facility.membercost * 1.1).where(Facility.facid == 0)
sq2 = Facility.select(Facility.guestcost * 1.1).where(Facility.facid == 0)

res = (Facility
       .update(membercost=sq1, guestcost=sq2)
       .where(Facility.facid == 1)
       .execute())

# OR:
cte = (Facility
       .select(Facility.membercost * 1.1, Facility.guestcost * 1.1)
       .where(Facility.name == 'Tennis Court 1')
       .cte('new_prices', columns=('nmc', 'ngc')))
res = (Facility
       .update(membercost=SQL('new_prices.nmc'), guestcost=SQL('new_prices.ngc'))
       .with_cte(cte)
       .from_(cte)
       .where(Facility.name == 'Tennis Court 2')
       .execute())

删除所有预订¶

作为我们数据库清理的一部分，我们想从 bookings 表中删除所有预订。

DELETE FROM bookings;

nrows = Booking.delete().execute()

从 members 表中删除会员¶

我们想从数据库中删除从未进行过预订的会员 37。

DELETE FROM members WHERE memid = 37;

nrows = Member.delete().where(Member.memid == 37).execute()

基于子查询删除¶

我们如何使其更通用，以删除所有从未进行过预订的会员？

DELETE FROM members WHERE NOT EXISTS (
  SELECT * FROM bookings WHERE bookings.memid = members.memid);

subq = Booking.select().where(Booking.member == Member.memid)
nrows = Member.delete().where(~fn.EXISTS(subq)).execute()

聚合 ¶

聚合是真正让您体会到关系数据库系统强大功能的能力之一。它使您能够超越仅仅持久化数据，进入提出真正有趣问题并用于辅助决策的领域。此类别详细介绍了聚合，利用了标准分组以及较新的窗口函数。

计算设施数量¶

对于我们第一次尝试聚合，我们将坚持简单。我们想知道有多少设施存在 - 只需生成一个总计数。

SELECT COUNT(facid) FROM facilities;

query = Facility.select(fn.COUNT(Facility.facid))
count = query.scalar()

# OR:
count = Facility.select().count()

计算昂贵设施的数量¶

生成对访客收费 10 或更多的设施数量计数。

SELECT COUNT(facid) FROM facilities WHERE guestcost >= 10

query = Facility.select(fn.COUNT(Facility.facid)).where(Facility.guestcost >= 10)
count = query.scalar()

# OR:
# count = Facility.select().where(Facility.guestcost >= 10).count()

计算每个会员的推荐数量。¶

生成每个会员所做推荐的数量计数。按会员 ID 排序。

SELECT recommendedby, COUNT(memid) FROM members
WHERE recommendedby IS NOT NULL
GROUP BY recommendedby
ORDER BY recommendedby

query = (Member
         .select(Member.recommendedby, fn.COUNT(Member.memid))
         .where(Member.recommendedby.is_null(False))
         .group_by(Member.recommendedby)
         .order_by(Member.recommendedby))

列出每个设施预订的总时段¶

生成每个设施预订的总时段列表。目前，只需生成一个包含设施 ID 和时段的输出表，按设施 ID 排序。

SELECT facid, SUM(slots) FROM bookings GROUP BY facid ORDER BY facid;

query = (Booking
         .select(Booking.facid, fn.SUM(Booking.slots))
         .group_by(Booking.facid)
         .order_by(Booking.facid))

列出给定月份每个设施预订的总时段¶

生成 2012 年 9 月份每个设施预订的总时段列表。生成一个包含设施 ID 和时段的输出表，按时段数量排序。

SELECT facid, SUM(slots)
FROM bookings
WHERE (date_trunc('month', starttime) = '2012-09-01'::dates)
GROUP BY facid
ORDER BY SUM(slots)

query = (Booking
         .select(Booking.facility, fn.SUM(Booking.slots))
         .where(fn.date_trunc('month', Booking.starttime) == datetime.date(2012, 9, 1))
         .group_by(Booking.facility)
         .order_by(fn.SUM(Booking.slots)))

列出每个设施每月预订的总时段¶

生成 2012 年每个设施每月预订的总时段列表。生成一个包含设施 ID 和时段的输出表，按 ID 和月份排序。

SELECT facid, date_part('month', starttime), SUM(slots)
FROM bookings
WHERE date_part('year', starttime) = 2012
GROUP BY facid, date_part('month', starttime)
ORDER BY facid, date_part('month', starttime)

month = fn.date_part('month', Booking.starttime)
query = (Booking
         .select(Booking.facility, month, fn.SUM(Booking.slots))
         .where(fn.date_part('year', Booking.starttime) == 2012)
         .group_by(Booking.facility, month)
         .order_by(Booking.facility, month))

查找至少进行过一次预订的会员数量¶

查找至少进行过一次预订的会员总数。

SELECT COUNT(DISTINCT memid) FROM bookings

-- OR --
SELECT COUNT(1) FROM (SELECT DISTINCT memid FROM bookings) AS _

query = Booking.select(fn.COUNT(Booking.member.distinct()))

# OR:
query = Booking.select(Booking.member).distinct()
count = query.count()  # count() wraps in SELECT COUNT(1) FROM (...)

列出预订时段超过 1000 的设施¶

生成一个预订时段超过 1000 的设施列表。生成一个包含设施 ID 和小时数的输出表，按设施 ID 排序。

SELECT facid, SUM(slots) FROM bookings
GROUP BY facid
HAVING SUM(slots) > 1000
ORDER BY facid;

query = (Booking
         .select(Booking.facility, fn.SUM(Booking.slots))
         .group_by(Booking.facility)
         .having(fn.SUM(Booking.slots) > 1000)
         .order_by(Booking.facility))

查找每个设施的总收入¶

生成设施及其总收入列表。输出表应包含设施名称和收入，按收入排序。请记住，访客和会员的费用不同！

SELECT f.name, SUM(b.slots * (
CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
FROM bookings AS b
INNER JOIN facilities AS f ON b.facid = f.facid
GROUP BY f.name
ORDER BY revenue;

revenue = fn.SUM(Booking.slots * Case(None, (
    (Booking.member == 0, Facility.guestcost),
), Facility.membercost))

query = (Facility
         .select(Facility.name, revenue.alias('revenue'))
         .join(Booking)
         .group_by(Facility.name)
         .order_by(SQL('revenue')))

查找总收入少于 1000 的设施¶

生成一个总收入少于 1000 的设施列表。生成一个包含设施名称和收入的输出表，按收入排序。请记住，访客和会员的费用不同！

SELECT f.name, SUM(b.slots * (
CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
FROM bookings AS b
INNER JOIN facilities AS f ON b.facid = f.facid
GROUP BY f.name
HAVING SUM(b.slots * ...) < 1000
ORDER BY revenue;

# Same definition as previous example.
revenue = fn.SUM(Booking.slots * Case(None, (
    (Booking.member == 0, Facility.guestcost),
), Facility.membercost))

query = (Facility
         .select(Facility.name, revenue.alias('revenue'))
         .join(Booking)
         .group_by(Facility.name)
         .having(revenue < 1000)
         .order_by(SQL('revenue')))

输出预订时段数量最多的设施 ID¶

输出预订时段数量最多的设施 ID。

SELECT facid, SUM(slots) FROM bookings
GROUP BY facid
ORDER BY SUM(slots) DESC
LIMIT 1

query = (Booking
         .select(Booking.facility, fn.SUM(Booking.slots))
         .group_by(Booking.facility)
         .order_by(fn.SUM(Booking.slots).desc())
         .limit(1))

# Retrieve multiple scalar values by calling scalar() with as_tuple=True.
facid, nslots = query.scalar(as_tuple=True)

列出每个设施每月预订的总时段，第 2 部分¶

生成 2012 年每个设施每月预订总时段的列表。在此版本中，包括包含每个设施所有月份总计的输出行，以及所有设施所有月份的总计。输出表应包含设施 ID、月份和时段，按 ID 和月份排序。当计算所有月份和所有设施 ID 的聚合值时，在月份和设施 ID 列中返回空值。

仅限 Postgres。

SELECT facid, date_part('month', starttime), SUM(slots)
FROM booking
WHERE date_part('year', starttime) = 2012
GROUP BY ROLLUP(facid, date_part('month', starttime))
ORDER BY facid, date_part('month', starttime)

month = fn.date_part('month', Booking.starttime)
query = (Booking
         .select(Booking.facility,
                 month.alias('month'),
                 fn.SUM(Booking.slots))
         .where(fn.date_part('year', Booking.starttime) == 2012)
         .group_by(fn.ROLLUP(Booking.facility, month))
         .order_by(Booking.facility, month))

列出每个指定设施预订的总小时数¶

生成每个设施预订总小时数的列表，请记住一个时段持续半小时。输出表应包含设施 ID、名称和预订小时数，按设施 ID 排序。

SELECT f.facid, f.name, SUM(b.slots) * .5
FROM facilities AS f
INNER JOIN bookings AS b ON (f.facid = b.facid)
GROUP BY f.facid, f.name
ORDER BY f.facid

query = (Facility
         .select(Facility.facid, Facility.name, fn.SUM(Booking.slots) * .5)
         .join(Booking)
         .group_by(Facility.facid, Facility.name)
         .order_by(Facility.facid))

列出每个会员在 2012 年 9 月 1 日之后的首次预订¶

生成每个会员的姓名、ID 以及他们在 2012 年 9 月 1 日之后的首次预订列表。按会员 ID 排序。

SELECT m.surname, m.firstname, m.memid, min(b.starttime) as starttime
FROM members AS m
INNER JOIN bookings AS b ON b.memid = m.memid
WHERE starttime >= '2012-09-01'
GROUP BY m.surname, m.firstname, m.memid
ORDER BY m.memid;

query = (Member
         .select(Member.surname, Member.firstname, Member.memid,
                 fn.MIN(Booking.starttime).alias('starttime'))
         .join(Booking)
         .where(Booking.starttime >= datetime.date(2012, 9, 1))
         .group_by(Member.surname, Member.firstname, Member.memid)
         .order_by(Member.memid))

生成会员姓名列表，每行包含会员总数¶

生成会员姓名列表，每行包含会员总数。按加入日期排序。

仅限 Postgres（按所示写法）。

SELECT COUNT(*) OVER(), firstname, surname
FROM members ORDER BY joindate

query = (Member
         .select(fn.COUNT(Member.memid).over(), Member.firstname,
                 Member.surname)
         .order_by(Member.joindate))

生成带编号的会员列表¶

生成一个单调递增的带编号会员列表，按加入日期排序。请记住，会员 ID 不保证是连续的。

仅限 Postgres（按所示写法）。

SELECT row_number() OVER (ORDER BY joindate), firstname, surname
FROM members ORDER BY joindate;

query = (Member
         .select(fn.row_number().over(order_by=[Member.joindate]),
                 Member.firstname, Member.surname)
         .order_by(Member.joindate))

再次输出预订时段数量最多的设施 ID¶

输出预订时段数量最多的设施 ID。确保在出现并列情况时，所有并列结果都被输出。

仅限 Postgres（按所示写法）。

SELECT facid, total FROM (
  SELECT facid, SUM(slots) AS total,
         rank() OVER (order by SUM(slots) DESC) AS rank
  FROM bookings
  GROUP BY facid
) AS ranked WHERE rank = 1

rank = fn.rank().over(order_by=[fn.SUM(Booking.slots).desc()])

subq = (Booking
        .select(Booking.facility, fn.SUM(Booking.slots).alias('total'),
                rank.alias('rank'))
        .group_by(Booking.facility))

# Here we use a plain Select() to create our query.
query = (Select(columns=[subq.c.facid, subq.c.total])
         .from_(subq)
         .where(subq.c.rank == 1)
         .bind(db))  # We must bind() it to the database.

# To iterate over the query results:
for facid, total in query.tuples():
    print(facid, total)

按（四舍五入的）使用小时数对会员进行排名¶

生成会员列表，以及他们在设施中预订的小时数（四舍五入到最接近的十小时）。按此四舍五入的数字对他们进行排名，输出名字、姓氏、四舍五入后的小时数、排名。按排名、姓氏和名字排序。

仅限 Postgres（按所示写法）。

SELECT firstname, surname,
((SUM(bks.slots)+10)/20)*10 as hours,
rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
FROM members AS mems
INNER JOIN bookings AS bks ON mems.memid = bks.memid
GROUP BY mems.memid
ORDER BY rank, surname, firstname;

hours = ((fn.SUM(Booking.slots) + 10) / 20) * 10
query = (Member
         .select(Member.firstname, Member.surname, hours.alias('hours'),
                 fn.rank().over(order_by=[hours.desc()]).alias('rank'))
         .join(Booking)
         .group_by(Member.memid)
         .order_by(SQL('rank'), Member.surname, Member.firstname))

查找收入最高的前三个设施¶

生成收入最高的前三个设施列表（包括并列）。输出设施名称和排名，按排名和设施名称排序。

仅限 Postgres（按所示写法）。

SELECT name, rank FROM (
    SELECT f.name, RANK() OVER (ORDER BY SUM(
        CASE WHEN memid = 0 THEN slots * f.guestcost
        ELSE slots * f.membercost END) DESC) AS rank
    FROM bookings
    INNER JOIN facilities AS f ON bookings.facid = f.facid
    GROUP BY f.name) AS subq
WHERE rank <= 3
ORDER BY rank;

total_cost = fn.SUM(Case(None, (
    (Booking.member == 0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost)))

subq = (Facility
        .select(Facility.name,
                fn.RANK().over(order_by=[total_cost.desc()]).alias('rank'))
        .join(Booking)
        .group_by(Facility.name))

query = (Select(columns=[subq.c.name, subq.c.rank])
         .from_(subq)
         .where(subq.c.rank <= 3)
         .order_by(subq.c.rank)
         .bind(db))  # Here again we used plain Select, and call bind().

按价值对设施进行分类¶

根据设施的收入将其分为高、中、低三个大小相等的组。按分类和设施名称排序。

仅限 Postgres（按所示写法）。

SELECT name,
  CASE class WHEN 1 THEN 'high' WHEN 2 THEN 'average' ELSE 'low' END
FROM (
  SELECT f.name, ntile(3) OVER (ORDER BY SUM(
    CASE WHEN memid = 0 THEN slots * f.guestcost ELSE slots * f.membercost
    END) DESC) AS class
  FROM bookings INNER JOIN facilities AS f ON bookings.facid = f.facid
  GROUP BY f.name
) AS subq
ORDER BY class, name;

cost = fn.SUM(Case(None, (
    (Booking.member == 0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost)))
subq = (Facility
        .select(Facility.name,
                fn.NTILE(3).over(order_by=[cost.desc()]).alias('klass'))
        .join(Booking)
        .group_by(Facility.name))

klass_case = Case(subq.c.klass, [(1, 'high'), (2, 'average')], 'low')
query = (Select(columns=[subq.c.name, klass_case])
         .from_(subq)
         .order_by(subq.c.klass, subq.c.name)
         .bind(db))

计算每个设施的投资回收期¶

根据目前 3 个完整月份的数据，计算每个设施偿还其拥有成本所需的时间。请记住要考虑持续的每月维护费用。输出设施名称和以月为单位的投资回收期，按设施名称排序。不必担心月份长度的差异，我们这里只寻求一个粗略的值！

SELECT f.name,
       f.initialoutlay / ((SUM(CASE
           WHEN b.memid = 0 THEN b.slots * f.guestcost
           ELSE b.slots * f.membercost END) / 3) - f.monthlymaintenance)
           AS months
FROM facilities AS f
INNER JOIN bookings AS b on (b.facid = f.facid)
GROUP BY f.facid
ORDER BY f.name

# How much money has this facility produced from its bookings?
revenue = fn.SUM(Case(None, (
    (Booking.member == 0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost)))

# Subtract monthly maintenance from average monthly revenue.
revenue_less_maintenance = (revenue / 3) - Facility.monthlymaintenance

# Determine how many months needed to pay off initial outlay.
payback_time = Facility.initialoutlay / revenue_less_maintenance

query = (Facility
         .select(Facility.name,
                 payback_time.alias('months'))
         .join(Booking)
         .group_by(Facility.facid)
         .order_by(Facility.name))

但是，我听到你问，这的自动化版本会是什么样子？一个不需要硬编码月份数量的版本？那会更复杂一些，并且涉及一些日期算术。我已将其分解为一个 CTE，使其更清晰。

with monthdata as (
   select mincompletemonth,
          maxcompletemonth,
          ((extract(year from maxcompletemonth)*12) +
           extract(month from maxcompletemonth) -
           (extract(year from mincompletemonth)*12) -
           extract(month from mincompletemonth)) as nummonths
   from (
      select
         date_trunc('month',
            (select max(starttime) from bookings)) as maxcompletemonth,
         date_trunc('month',
            (select min(starttime) from bookings)) as mincompletemonth
   ) as subq)
select name,
   initialoutlay / (monthlyrevenue - monthlymaintenance) as repaytime
   from
      (select f.name as name,
         f.initialoutlay as initialoutlay,
         f.monthlymaintenance as monthlymaintenance,
         sum(case
            when memid = 0 then slots * f.guestcost
            else slots * membercost
         end)/(select nummonths from monthdata) as monthlyrevenue

         from bookings as b
         inner join facilities as f
            on b.facid = f.facid
         where b.starttime < (select maxcompletemonth from monthdata)
         group by f.facid
      ) as subq
order by name;

# First calculate the min and max ranges of bookings.
BA = Booking.alias()
bounds = BA.select(
    fn.date_trunc('month', fn.MIN(BA.starttime)).alias('minmonth'),
    fn.date_trunc('month', fn.MAX(BA.starttime)).alias('maxmonth')
).alias('bounds')

# Calculate how many months the range of bookings covers.
extract = db.extract_date  # Helper for generating EXTRACT .. FROM.
q = bounds.select_from(
    bounds.c.minmonth,
    bounds.c.maxmonth,
    ((extract('year', bounds.c.maxmonth) * 12) +
     extract('month', bounds.c.maxmonth) -
     (extract('year', bounds.c.minmonth) * 12) -
     extract('month', bounds.c.minmonth)).alias('nmonths'))

# Indicate that we will be using this as a CTE.
monthdata = q.cte('monthdata')

# Subqueries to retrieve total & max month data from the CTE.
nmonths = Select((monthdata,), (monthdata.c.nmonths,))
maxmonth = Select((monthdata,), (monthdata.c.maxmonth,))

# Our familiar revenue calculation.
revenue = fn.SUM(Case(None, (
    (Booking.member == 0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost)))
revenue_less_maintenance = (revenue / nmonths) - Facility.monthlymaintenance

payback_time = Facility.initialoutlay / revenue_less_maintenance

q = (Facility
     .select(
         Facility.name,
         payback_time.alias('payback_time'))
     .join(Booking)
     .where(Booking.starttime < maxmonth)
     .group_by(Facility.facid)
     .order_by(Facility.name)
     .with_cte(monthdata))

日期和时间 ¶

计算预订的结束时间¶

返回系统中最后 10 个预订的开始时间和结束时间列表（按结束时间排序，然后按开始时间排序）。

SELECT starttime, starttime + slots*(interval '30 minutes') AS endtime
     FROM bookings
     ORDER BY endtime DESC, starttime DESC
     LIMIT 10

endtime = Booking.starttime + (Booking.slots * db.interval('30 minutes'))
query = (Booking
         .select(Booking.starttime, endtime.alias('endtime'))
         .order_by(endtime.desc(), Booking.starttime.desc())
         .limit(10))

返回每个月的预订计数¶

返回每个月的预订计数，按月份排序。

SELECT date_trunc('month', starttime) as month, COUNT(*)
     FROM bookings
     GROUP BY month
     ORDER BY month

month = db.truncate_date('month', Booking.starttime)
query = (Booking
         .select(
             month.alias('month'),
             fn.COUNT(Booking.bookid).alias('count'))
         .group_by(month)
         .order_by(month))

计算每个设施每月的利用率百分比¶

计算每个设施每月的利用率百分比，按名称和月份排序，四舍五入到 1 位小数。开放时间是上午 8 点，关闭时间是晚上 8:30。您可以将每个月视为完整月份，无论俱乐部是否在某些日期未开放。

SELECT name, month,
   round((100*slots)/
      cast(
         25*(cast((month + interval '1 month') as date)
         - cast (month as date)) as numeric),1) as utilisation
FROM (
   SELECT facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
   FROM bookings bks
   INNER JOIN facilities AS facs
      ON bks.facid = facs.facid
   GROUP BY facs.facid, month
) as _
ORDER BY name, month

# Create the inner query first.
month = db.truncate_date('month', Booking.starttime)
subq = (Booking
        .select(
            Facility.name,
            month.alias('month'),
            fn.SUM(Booking.slots).alias('slots'))
        .join(Facility)
        .group_by(Facility.facid, month))

# Expression representing the utilization.
utilization = fn.ROUND(
    (100 * subq.c.slots) /
    Cast(25 * (
        Cast(subq.c.month + db.interval('1 month'), 'date') -
        Cast(subq.c.month, 'date')), 'numeric'), 1)

query = (subq
         .select_from(
            subq.c.name,
            subq.c.month,
            utilization.alias('utilization'))
         .order_by(subq.c.name, subq.c.month))

字符串 ¶

格式化会员姓名¶

输出所有会员的姓名，格式为“姓氏, 名字”

SELECT surname || ', ' || firstname as name FROM members

query = Member.select(
    (Member.surname + ', ' + Member.firstname).alias('name'))

按名称前缀查找设施¶

查找所有名称以“Tennis”开头的设施。检索所有列。

SELECT * FROM facilities WHERE name LIKE 'Tennis%';

# `startswith()` uses ILIKE (case-insensitive):
query = Facility.select().where(Facility.name.startswith('Tennis'))

# For case-sensitive search use LIKE explicitly:
query = Facility.select().where(Facility.name.like('Tennis%'))

执行不区分大小写的搜索¶

执行不区分大小写的搜索，查找所有名称以“tennis”开头的设施。检索所有列。

SELECT * FROM facilities WHERE name ILIKE 'tennis%';

-- OR --
SELECT * FROM facilities WHERE UPPER(name) LIKE 'TENNIS%';

# `startswith()` uses ILIKE (case-insensitive):
query = Facility.select().where(Facility.name.startswith('tennis'))

# For case-sensitive search use ILIKE explicitly:
query = Facility.select().where(Facility.name.ilike('tennis%'))

# Or convert to upper:
query = Facility.select().where(
   fn.upper(Facility.name).like('TENNIS%'))

查找带有括号的电话号码¶

您注意到俱乐部的会员表中的电话号码格式非常不一致。您想查找所有包含括号的电话号码，返回会员 ID 和电话号码，按会员 ID 排序。

SELECT memid, telephone FROM members WHERE telephone ~ '[()]';

query = (Member
         .select(Member.memid, Member.telephone)
         .where(Member.telephone.regexp('[()]')))

用前导零填充邮政编码¶

我们示例数据集中的邮政编码由于存储为数字类型而移除了前导零。从 members 表中检索所有邮政编码，对任何长度小于 5 个字符的邮政编码用前导零填充。按新邮政编码排序。

SELECT lpad(cast(zipcode as char(5)),5,'0') AS zip
FROM members
ORDER BY zip

# Because we're wrapping an integer field, Peewee will still want to try and
# coerce the LPAD() output to an integer, so we need to inform Peewee to
# leave the LPAD result as-is.
zipcode = fn.lpad(Cast(Member.zipcode, 'char(5)'), 5, '0', coerce=False)
query = Member.select(zipcode.alias('zipcode')).order_by(zipcode)

按姓氏首字母聚合¶

您想统计有多少会员的姓氏以字母表中的每个字母开头。按字母排序，并且如果计数为 0 则无需打印出该字母。

SELECT substr(surname, 1, 1) as letter, count(*) as count
FROM members
GROUP BY letter
ORDER BY letter

initial = fn.SUBSTR(Member.surname, 1, 1)
q = (Member
     .select(initial, fn.COUNT(Member.memid))
     .group_by(initial)
     .order_by(initial))

清理电话号码¶

数据库中的电话号码格式非常不一致。您想打印一份已移除‘-‘、‘(‘、‘)’和‘ ’字符的会员 ID 和电话号码列表。按会员 ID 排序。

SELECT memid, translate(telephone, '-() ', '') as telephone
FROM members
ORDER BY memid;

clean = fn.translate(Member.telephone, '-() ', '')
q = (Member
     .select(Member.memid, clean.alias('telephone'))
     .order_by(Member.memid))

递归 ¶

公用表表达式（Common Table Expressions）允许我们有效地在查询期间创建自己的临时表——它们主要是为了帮助我们编写更易读的 SQL。然而，使用 WITH RECURSIVE 修饰符，我们可以创建递归查询。这对于处理树形和图结构数据非常有利——例如，想象一下检索图节点到给定深度的所有关系。

查找会员 ID 27 的向上推荐链¶

查找会员 ID 27 的向上推荐链：即推荐他们的人，以及推荐该会员的人，依此类推。返回会员 ID、名字和姓氏。按会员 ID 降序排序。

WITH RECURSIVE recommenders(recommender) as (
  SELECT recommendedby FROM members WHERE memid = 27
  UNION ALL
  SELECT mems.recommendedby
  FROM recommenders recs
  INNER JOIN members AS mems ON mems.memid = recs.recommender
)
SELECT recs.recommender, mems.firstname, mems.surname
FROM recommenders AS recs
INNER JOIN members AS mems ON recs.recommender = mems.memid
ORDER By memid DESC;

# Base-case of recursive CTE. Get member recommender where memid=27.
base = (Member
        .select(Member.recommendedby)
        .where(Member.memid == 27)
        .cte('recommenders', recursive=True, columns=('recommender',)))

# Recursive term of CTE. Get recommender of previous recommender.
MA = Member.alias()
recursive = (MA
             .select(MA.recommendedby)
             .join(base, on=(MA.memid == base.c.recommender)))

# Combine the base-case with the recursive term.
cte = base.union_all(recursive)

# Select from the recursive CTE, joining on member to get name info.
query = (cte
         .select_from(cte.c.recommender, Member.firstname, Member.surname)
         .join(Member, on=(cte.c.recommender == Member.memid))
         .order_by(Member.memid.desc()))

查找会员 ID 1 的向下推荐链¶

查找会员 ID 1 的向下推荐链：即他们推荐的会员，这些会员又推荐的会员，依此类推。返回会员 ID 和姓名，按会员 ID 升序排序。

WITH RECURSIVE recommendeds(memid) AS (
   SELECT memid FROM members WHERE recommendedby = 1
   UNION ALL
   SELECT mems.memid
   FROM recommendeds recs
   INNER JOIN members mems ON mems.recommendedby = recs.memid
)
SELECT recs.memid, mems.firstname, mems.surname
FROM recommendeds recs
INNER JOIN members mems
   ON recs.memid = mems.memid
ORDER BY memid

# Base-case of recursive CTE. Get members recommended by memid=1.
base = (Member
       .select(Member.memid)
       .where(Member.recommendedby == 1)
       .cte('recommenders', recursive=True, columns=('memid',)))

# Recursive term of CTE. Get recommended by previous recommender.
MA = Member.alias()
recursive = (MA
            .select(MA.memid)
            .join(base, on=(MA.recommendedby == base.c.memid)))

# Combine the base-case with the recursive term.
cte = base.union_all(recursive)

# Select from the recursive CTE, joining on member to get name info.
query = (cte
        .select_from(cte.c.memid, Member.firstname, Member.surname)
        .join(Member, on=(cte.c.memid == Member.memid))
        .order_by(Member.memid))
for row in query:
    print(row.memid, row.firstname, row.surname)

为任意会员生成向上推荐链¶

生成一个 CTE，可以返回任何会员的向上推荐链。您应该能够从 recommenders 中选择 recommender，其中 member=x。通过获取会员 12 和 22 的推荐链来演示。结果表应包含 member 和 recommender，按 member 升序、recommender 降序排序。

WITH RECURSIVE recommenders(recommender, member) AS (
   SELECT recommendedby, memid
   FROM members
   UNION ALL
   SELECT mems.recommendedby, recs.member
   FROM recommenders recs
   INNER JOIN members mems
   ON mems.memid = recs.recommender
)
SELECT recs.member member, recs.recommender, mems.firstname, mems.surname
FROM recommenders recs
INNER JOIN members mems
   ON recs.recommender = mems.memid
WHERE recs.member = 22 or recs.member = 12
ORDER BY recs.member ASC, recs.recommender DESC

# Base-case of recursive CTE. Get member recommender where memid=27.
base = (Member
       .select(Member.recommendedby, Member.memid)
       .cte('recommenders', recursive=True, columns=('recommender', 'member')))

# Recursive term of CTE. Get recommender of previous recommender.
MA = Member.alias()
recursive = (MA
            .select(MA.recommendedby, base.c.member)
            .join(base, on=(MA.memid == base.c.recommender)))

# Combine the base-case with the recursive term.
cte = base.union_all(recursive)

# Select from the recursive CTE, joining on member to get name info.
query = (cte
        .select_from(
            cte.c.member,
            cte.c.recommender,
            Member.firstname,
            Member.surname)
        .join(Member, on=(cte.c.recommender == Member.memid))
        .where((cte.c.member == 22) | (cte.c.member == 12))
        .order_by(cte.c.member, cte.c.recommender.desc()))

查询示例库¶

检索所有内容¶

从表中检索特定列¶

控制检索哪些行¶

控制检索哪些行 - 第2部分¶

基本字符串搜索¶

匹配多个可能的值¶

将结果分类到桶中¶

处理日期¶

删除重复项并排序结果¶

合并来自多个查询的结果¶

简单聚合¶

更多聚合¶

检索会员预订的开始时间¶

计算网球场预订的开始时间¶

生成一份推荐过其他会员的所有会员列表¶

生成一份所有会员及其推荐人列表¶

生成一份所有使用过网球场的会员列表¶

生成一份昂贵预订列表¶

生成一份所有会员及其推荐人列表，不使用任何连接。¶

使用子查询生成一份昂贵预订列表¶

向表中插入数据¶

向表中插入多行数据¶

向表中插入计算数据¶

更新现有数据¶

同时更新多行和多列¶

根据另一行的内容更新行¶

删除所有预订¶

从 members 表中删除会员¶

基于子查询删除¶

计算设施数量¶

计算昂贵设施的数量¶

计算每个会员的推荐数量。¶

列出每个设施预订的总时段¶

列出给定月份每个设施预订的总时段¶

列出每个设施每月预订的总时段¶

查找至少进行过一次预订的会员数量¶

列出预订时段超过 1000 的设施¶

查找每个设施的总收入¶

查找总收入少于 1000 的设施¶

输出预订时段数量最多的设施 ID¶

列出每个设施每月预订的总时段，第 2 部分¶

列出每个指定设施预订的总小时数¶

列出每个会员在 2012 年 9 月 1 日之后的首次预订¶

生成会员姓名列表，每行包含会员总数¶

生成带编号的会员列表¶

再次输出预订时段数量最多的设施 ID¶

按（四舍五入的）使用小时数对会员进行排名¶

查找收入最高的前三个设施¶

按价值对设施进行分类¶

计算每个设施的投资回收期¶

计算预订的结束时间¶

返回每个月的预订计数¶

计算每个设施每月的利用率百分比¶

格式化会员姓名¶

按名称前缀查找设施¶

执行不区分大小写的搜索¶

查找带有括号的电话号码¶

用前导零填充邮政编码¶

按姓氏首字母聚合¶

清理电话号码¶

查找会员 ID 27 的向上推荐链¶

查找会员 ID 1 的向下推荐链¶

为任意会员生成向上推荐链¶