如何用SQLite/peewee实现多车型匹配车主并按总马力排序？

背景信息 #

现有一个cars表，对应的Peewee模型如下：

class Car(Model):
    owner_id = IntegerField()
    name = CharField()
    power = IntegerField()

需求 #

输入1至5个不重复的车型名称（需精确匹配，不允许部分匹配），需获取拥有所有这些车型的车主（最多返回5位），并按这些车型的总马力降序排序。

数据示例 #

create table cars(owner_id integer, name varchar(21), power integer);
insert into cars values (101,'bmw',300);
insert into cars values (101,'audi',200);
insert into cars values (101,'mercedes',100);
insert into cars values (102,'bmw',250);
insert into cars values (102,'mercedes',400);
insert into cars values (103,'bmw',200);
insert into cars values (103,'audi',100);
insert into cars values (103,'mercedes',190);

示例效果 #

• 输入车型：bmw、mercedes

  owner_id|total_power
  --------|-----------
       102|        650
       101|        400
       103|        390
  

• 输入车型：bmw、mercedes、audi

  owner_id|total_power
  --------|-----------
       101|        600
       103|        490
  

尝试的SQL（未正常工作） #

select owner_id, sum(power) as total_power
from cars c1
inner join cars c2 on c1.owner_id = c2.owner_id
where c1.name = 'bmw' and c2.name = 'audi'
group by owner_id
order by total_power desc

疑问 #

1. 针对5个车型，是否有更优写法，还是必须使用5次自连接？
2. 如何正确计算所有指定车型的总马力？
3. 如何用Peewee实现该查询？

注：非Peewee实现需基于SQLite。

解答 #

1. 多车型查询的更优写法（无需多次自连接） #

不需要多次自连接，用GROUP BY + HAVING的方式更简洁高效，适合任意数量的车型：

-- 假设输入的车型为 'bmw', 'mercedes', 'audi'
SELECT owner_id, SUM(power) AS total_power
FROM cars
WHERE name IN ('bmw', 'mercedes', 'audi')
GROUP BY owner_id
-- 确保分组内的车型数量等于输入的车型总数（即该车主拥有所有指定车型）
HAVING COUNT(DISTINCT name) = 3
ORDER BY total_power DESC
LIMIT 5;

逻辑说明：

• 先筛选出所有属于指定车型的记录
• 按owner_id分组
• 通过HAVING COUNT(DISTINCT name) = N（N为输入车型的数量），确保该车主拥有所有N个车型
• 最后求和、排序并限制返回数量

2. 正确计算总马力的方法 #

你之前的自连接写法会导致重复计算：因为每个c1的记录会和c2的记录一一匹配，SUM(power)会重复累加同一车型的马力。

正确的方式是直接对筛选后的指定车型记录求和：

• 先过滤出属于目标车型的记录
• 按owner_id分组后直接SUM(power)，这样得到的就是该车主所有指定车型的总马力（和示例结果一致）

3. Peewee实现代码 #

from peewee import *

# 假设已初始化数据库连接，比如：
# db = SqliteDatabase('your_db.db')

class Car(Model):
    owner_id = IntegerField()
    name = CharField()
    power = IntegerField()

    class Meta:
        database = db
        table_name = 'cars'

def get_owners_with_all_cars(target_cars):
    n = len(target_cars)
    query = (Car
             .select(Car.owner_id, fn.SUM(Car.power).alias('total_power'))
             .where(Car.name.in_(target_cars))
             .group_by(Car.owner_id)
             .having(fn.COUNT(fn.DISTINCT(Car.name)) == n)
             .order_by(fn.SUM(Car.power).desc())
             .limit(5))
    
    # 执行查询并返回结果，转为字典列表
    return [{'owner_id': row.owner_id, 'total_power': row.total_power} for row in query]

# 示例调用
print(get_owners_with_all_cars(['bmw', 'mercedes']))
print(get_owners_with_all_cars(['bmw', 'mercedes', 'audi']))

代码说明：

• 使用name.in_(target_cars)筛选指定车型
• group_by(Car.owner_id)按车主分组
• having(fn.COUNT(fn.DISTINCT(Car.name)) == n)确保车主拥有所有目标车型
• fn.SUM(Car.power)计算总马力，按其降序排序，最后限制返回5条结果

内容的提问来源于stack exchange，提问作者vault