查询实例

这些查询示例取自站点 PostgreSQL Exercises . 示例数据集可以在 getting started page .

以下是这些示例中使用的模式的可视化表示：

模型定义

为了开始处理数据，我们将定义对应于图中表的模型类。

备注

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

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'

架构创建

如果从PostgreSQL练习站点下载了SQL文件，那么可以使用以下命令将数据加载到PostgreSQL数据库中：

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

要使用peewee创建模式，而不加载示例数据，可以运行以下操作：

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

基础训练

此类别处理SQL的基础知识。它包括select和where子句、case表达式、union以及一些其他的零碎和结尾。

检索所有内容

从设施表中检索所有信息。

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

基本字符串搜索

你怎么能列出所有以“网球”为名的设施？

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的设施的详细信息？尝试在不使用或运算符的情况下执行此操作。

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

删除重复项并对结果排序

如何生成成员表中前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()

联接和子查询

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

本主题涵盖内部、外部和自联接，以及在子查询（查询中的查询）上花费一些时间。

检索成员预订的开始时间

你如何能列出“大卫法雷尔”会员预订的开始时间？

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

列出推荐其他成员的所有成员

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

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

不使用连接生成所有成员及其推荐者的列表。

如何在不使用任何联接的情况下输出所有成员的列表，包括推荐它们的个人（如果有）？确保列表中没有重复项，并且每个firstname+姓对都格式化为一列并按顺序排列。

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

使用子查询生成成本高昂的预订列表

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

修饰资料

查询数据是非常好的，但在某个时候，您可能希望将数据放入数据库中！本节讨论插入、更新和删除信息。像这样改变数据的操作统称为数据操作语言（DML）。

在前面的部分中，我们将您执行的查询的结果返回给您。由于像我们在本节中所做的修改不会返回任何查询结果，因此我们将向您显示您应该处理的表的更新内容。

在表中插入一些数据

俱乐部正在增加一个新的设施-水疗中心。我们需要将其添加到设施表中。使用以下值：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())

在表中插入多行数据

在上一个练习中，您学习了如何添加设备。现在，您将在一个命令中添加多个设施。使用以下值：

设施：9，名称：“SPA”，会员费：20，住宿费：30，首付费用：100000，月保养费：800。

门面：10，姓名：壁球场2，会员费：3.5，住宿费：17.5，首付费用：5000，月保养费：80。

-- see above --

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

将计算数据插入表

让我们再次尝试将SPA添加到设施表中。不过，这次我们希望自动生成下一个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())

删除所有预订

作为数据库清除的一部分，我们希望从“预订”表中删除所有预订。

DELETE FROM bookings;

nrows = Booking.delete().execute()

从cd.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和月份排序。在计算所有月份和所有Facid的聚合值时，在Month和Facid列中返回空值。

仅次于邮政。

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日之后每位会员的首次预订

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

按使用的小时（四舍五入）对成员进行排名

列出会员名单，以及他们在设施中预订的小时数，四舍五入到最接近的10小时。按这个四舍五入的数字对它们排序，产生名字、姓氏、四舍五入的小时数、排名。按等级、姓氏和名字排序。

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

递归

公共表表达式允许我们在查询期间有效地创建自己的临时表——它们在很大程度上是帮助我们生成更具可读性的SQL的便利。但是，使用WITH递归修饰符，我们可以创建递归查询。这对于使用树和图结构数据非常有利——例如，假设将图节点的所有关系检索到给定深度。

查找成员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()))

查询实例

模型定义

架构创建

基础训练

检索所有内容

从表中检索特定列

控制检索哪些行

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

基本字符串搜索

与多个可能值匹配

将结果分类为桶

使用日期

删除重复项并对结果排序

组合来自多个查询的结果

简单聚合

更多聚合

联接和子查询

检索成员预订的开始时间

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

列出推荐其他成员的所有成员

列出所有会员及其推荐人的名单

列出所有使用过网球场的成员

列出昂贵的预订

不使用连接生成所有成员及其推荐者的列表。

使用子查询生成成本高昂的预订列表

修饰资料

在表中插入一些数据

在表中插入多行数据

将计算数据插入表

更新一些现有数据

同时更新多个行和列

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

删除所有预订

从cd.members表中删除成员

基于子查询删除

聚集

计算设施数量

计算昂贵设施的数量

计算每个成员提出的建议数。

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

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

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

查找至少预订过一次的会员数

列出预订超过1000个插槽的设施

找到每个设施的总收入

查找总收入小于1000的设施

输出预订最多插槽的设备ID

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

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

列出2012年9月1日之后每位会员的首次预订

生成成员名称列表，其中每一行包含成员总数

生成成员的编号列表

再次输出预订最多插槽的设备ID

按使用的小时（四舍五入）对成员进行排名

找到三大创收设施

按价值分类设施

递归

查找成员ID 27的向上推荐链