如何在SQLite中关联三张表并找出订单量最高的客户

问题1：如何在SQLite中将三张表合并为一张表？ #

如果是要整合三张关联表的数据生成新表，可使用 CREATE TABLE ... AS 语句结合关联查询实现，示例代码如下：

CREATE TABLE merged_table AS
SELECT 
    a."order ID" AS order_id,
    a."customer ID" AS customer_id,
    b."order quantity" AS order_quantity,
    c."customer name" AS customer_name
FROM "table a" a
JOIN "table b" b ON a."order ID" = b."Order ID"
JOIN "table c" c ON a."customer ID" = c."customer ID";

• 表名和字段名含空格时，必须用双引号包裹避免语法错误；
• 表别名（a/b/c）可简化代码书写；
• 执行后会生成名为 merged_table 的新表，包含三张表的关联数据。

如果是合并结构完全相同的多张表（如同类型订单分表），则用 UNION ALL（保留重复数据）或 UNION（去重）：

CREATE TABLE combined_table AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3;

问题2：如何关联三张表找出订单量最高的客户？ #

你的尝试代码存在语法问题：表名/字段名含空格未用双引号包裹、FROM 后多了多余的点。以下是修正后的代码：

1. 获取所有客户的订单量（匹配预期结果） #

SELECT 
    c."customer name" AS "Customer name",
    b."order quantity" AS "order quantity"
FROM "table b" b
JOIN "table a" a ON a."order ID" = b."Order ID"
JOIN "table c" c ON a."customer ID" = c."customer ID"
ORDER BY "order quantity" DESC;

2. 仅获取订单量最高的客户 #

若只需显示订单量最高的一位客户，添加 LIMIT 1 即可：

SELECT 
    c."customer name" AS "Customer name",
    b."order quantity" AS "order quantity"
FROM "table b" b
JOIN "table a" a ON a."order ID" = b."Order ID"
JOIN "table c" c ON a."customer ID" = c."customer ID"
ORDER BY "order quantity" DESC
LIMIT 1;

• JOIN 确保只获取存在关联关系的订单与客户数据；
• ORDER BY ... DESC 按订单量降序排序，最高值排在首位；
• LIMIT 1 限制仅返回第一条结果，即订单量最高的客户。

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