电商系统数据查询

条件： 共涉及以下五张表

1.店铺表（shop）：
id,
name(店铺名称)
plateform(平台：淘宝，抖音) 

2.商品表（item）：
 id,
 title(商品名称，比如：SKY豆沙粉亚麻套装 )
 color(颜色，比如：粉色)
 size（尺码，比如：S）
 sku(商品sku，比如SKY-P0247M)
 shop_id(所属店铺)
 price(商品价格，299)

3.用户表（user）：
 id
 nickname（昵称，如：张三）
 type（类型：买家，卖家）

4.订单表（order）：
 id
 seller_id（用户表里面的卖家）
 buyer_id（买家）

5.订单明细（order_detail）：
 id
 tid（所属交易）
 item_id(商品的id)

业务逻辑：

一个店铺表多个商品

一个用户可以下单多次，每个单里面可以有多个商品

一个交易有多个订单，比如买家一单买了3件衣服

实现目标：

1.需要实现一个已卖出的订单接口，把每个订单返回：
店铺名称，订单编号，商品明细（返回订单商品的全部信息），商品数量，买家名称
店铺名称：可筛选，可分组
商品数量：可排序，可筛选（a<=数量<=b）
支持分页

要实现这样一个已卖出的订单接口，需要编写一个SQL查询来连接上述提到的所有相关表，并根据需求进行筛选、排序及分页。这里我将给出一个示例SQL查询语句，假设我们使用的是MySQL数据库。请注意，根据实际使用的数据库系统（如PostgreSQL、Oracle等），语法可能略有不同。

1SELECT 
2    s.name AS 店铺名称,
3    o.id AS 订单编号,
4    CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
5        'itemId', od.item_id,
6        'itemName', i.title,
7        'color', i.color,
8        'size', i.size,
9        'sku', i.sku,
10        'price', i.price
11    ) SEPARATOR ','), ']') AS 商品明细,
12    COUNT(od.id) OVER(PARTITION BY o.id) AS 商品数量,
13    u.nickname AS 买家名称
14FROM 
15    `order` o
16JOIN 
17    order_detail od ON o.id = od.tid
18JOIN 
19    item i ON od.item_id = i.id
20JOIN 
21    shop s ON i.shop_id = s.id
22JOIN 
23    `user` u ON o.buyer_id = u.id
24WHERE 
25    o.seller_id != o.buyer_id -- 确保订单是卖出的
26GROUP BY 
27    o.id
28HAVING 
29    商品数量 BETWEEN a AND b -- 假设a和b是传入的筛选条件
30ORDER BY 
31    商品数量 DESC -- 可按需调整排序字段和方向
32LIMIT 
33    m OFFSET n; -- 分页，m是每页数量，n是偏移量，需根据实际传入值替换

在这个查询中，首先通过连接order、order_detail、item、shop和user表来获取所有相关信息。通过WHERE子句确保只选择卖出的订单（即卖家ID不等于买家ID）。使用GROUP BY对每个订单进行分组，并使用窗口函数COUNT()计算每个订单的商品数量。HAVING子句允许你根据商品数量进行筛选。ORDER BY用于排序，而LIMIT和OFFSET则用于实现分页功能。

2.需要实现：

商品明细：可筛选（查看拥有某种商品的订单），可分组（按单件商品分组，比如买A商品的有哪些，B商品的有哪些，一个订单有多个商品的，显示到两个多个商品分组条件下面）

要实现这样的查询接口，可能需要相对复杂的SQL查询来联合这些表，并根据需求进行分组和筛选。假设使用的是MySQL数据库，它会返回每个订单的店铺名称、订单编号、商品明细（包括筛选的商品）、商品数量以及买家名称，并按商品分组展示：

1SELECT
2    s.name AS 店铺名称,
3    o.id AS 订单编号,
4    JSON_ARRAYAGG(JSON_OBJECT(
5        'itemId', od.item_id,
6        'itemName', i.title,
7        'color', i.color,
8        'size', i.size,
9        'sku', i.sku,
10        'price', i.price
11    )) AS 商品明细,
12    COUNT(od.id) AS 商品数量,
13    u.nickname AS 买家名称
14FROM
15    `order` o
16JOIN
17    order_detail od ON o.id = od.tid
18JOIN
19    item i ON od.item_id = i.id
20JOIN
21    shop s ON i.shop_id = s.id
22JOIN
23    `user` u ON o.buyer_id = u.id
24WHERE
25    o.seller_id != o.buyer_id AND i.title IN ('商品A', '商品B') -- 这里根据需要筛选的商品名称进行调整
26GROUP BY
27    o.id
28WITH ROLLUP -- 用于生成每个商品的分组汇总行
29ORDER BY
30    o.id, i.title;

这个查询做了以下几点：

• 联合了所有相关的表以获取所需信息。
• 使用JSON_ARRAYAGG函数聚合每个订单中的商品详情为JSON数组，方便客户端解析。
• 使用WHERE子句对订单进行了筛选，确保是已卖出的订单，并提供了商品名称的筛选条件。
• 通过GROUP BY o.id按订单分组，并使用WITH ROLLUP来生成额外的分组行，以显示每个商品的汇总信息。
• ORDER BY语句确保结果按订单ID和商品名称排序。

需要注意的是，根据具体数据库系统（如Oracle、SQL Server等），语法可能需要调整，特别是聚合函数JSON_ARRAYAGG和JSON_OBJECT，因为不是所有数据库系统都支持这些函数。在不支持JSON函数的数据库中，可能需要使用其他方式构造商品明细输出。