mysql - 获取按两列分组的最小值/最大值
问题描述
我有 2 张桌子
零件表:
id | part_id | group_id
1 | | 1
2 | 1 | 1
3 | | 1
4 | | 2
每个部分都与组相关,并且可以与另一个部分相关(模拟)
优惠表:
offer_id | part_id | quantity
1 | 1 | 1
2 | 2 | 2
3 | 2 | 3
4 | 3 | 4
5 | 4 | 5
与零件相关的每个报价
我需要获得按部件或按最低/最高数量排序的相关部件分组的最佳报价。
对于 group_id 1,排序时 ASC 结果应该是
offer_id | part_id | quantity
1 | 1 | 1
4 | 3 | 4
当订购 DESC 结果应该是
offer_id | part_id | quantity
4 | 3 | 4
3 | 2 | 3
我试过这个查询
SELECT
pi.offer_id,
pi.part_id,
( SELECT pps.quantity
FROM offers AS pps
WHERE pps.offer_id = pi.offer_id
ORDER BY pps.quantity asc
LIMIT 1
) AS q
FROM offers AS pi
JOIN parts p ON p.id = pi.part_id
WHERE p.group_id = 1
GROUP BY part_id
ORDER BY q asc
结果:
offer_id | part_id | q
1 | 1 | 1
2 | 2 | 2
4 | 3 | 4
它不对部件和相关部件(1 和 2)进行分组,并返回 3 行而不是 2 行。我如何解决它?
更新
如果我将零件表数据更改为此,是否有可能?
id | part_id | group_id
1 | 1 | 1
2 | 1 | 1
3 | 3 | 1
4 | 4 | 2
我也试过这个查询,但它也没有按 part_id 分组
SELECT a.id, a.part_id, a.quantity, p.part_id AS pid
FROM supplier_offers a
INNER JOIN
(
SELECT part_id, Min(quantity) AS qty
FROM supplier_offers
GROUP BY part_id
) b ON a.part_id = b.part_id AND a.quantity = b.qty
JOIN parts p ON p.id = a.part_id
WHERE p.cross_group_uuid = '78242c22-c113-4258-806c-936de014ba10'
ORDER BY a.quantity ASC
更新 2
似乎唯一的方法是将part part_id保存在offers表中并按它分组
解决方案
我不认为这是可能的,因为只有订购才能实现这一点。正如您在小提琴示例中看到的,您还必须添加 a并且 p.part_id 为 NULL
CREATE TABLE offers (`offer_id` int, `part_id` int, `quantity` int) ; INSERT INTO offers (`offer_id`, `part_id`, `quantity`) VALUES (1, 1, 1), (2, 2, 2), (3, 2, 3), (4, 3, 4), (5, 4, 5) ;
✓ ✓
CREATE TABLE parts (`id` int, `part_id` int, `group_id` int) ; INSERT INTO parts (`id`, `part_id`, `group_id`) VALUES (1, NULL, 1), (2, 1, 1), (3, NULL, 1), (4, NULL, 2) ;
✓ ✓
SELECT o.offer_id,o.part_id, o.quantity FROM offers o inner JOIN parts p ON p.id = o.part_id WHERE group_id = 1 and p.part_id is NULL
报价 ID | part_id | 数量 --------: | ------: | --------: 1 | 1 | 1 4 | 3 | 4
SELECT * FROM (SELECT o.offer_id,o.part_id, o.quantity FROM offers o inner JOIN parts p ON p.id = o.part_id WHERE group_id = 1 ORDER by quantity LIMIT 1) t1 union ALL (SELECT o.offer_id,o.part_id, o.quantity FROM offers o inner JOIN parts p ON p.id = o.part_id WHERE group_id = 1 ORDER by quantity DESC LIMIT 1)
报价 ID | part_id | 数量 --------: | ------: | --------: 1 | 1 | 1 4 | 3 | 4
SELECT o.offer_id,o.part_id, o.quantity FROM offers o inner JOIN parts p ON p.id = o.part_id WHERE group_id = 1 ORDER BY o.offer_ID DESC LIMIT 2
报价 ID | part_id | 数量 --------: | ------: | --------: 4 | 3 | 4 3 | 2 | 3
SELECT o.offer_id,o.part_id, o.quantity FROM offers o inner JOIN parts p ON p.id = o.part_id WHERE group_id = 1 and p.part_id is NULL ORDER BY o.offer_ID ASC LIMIT 2
报价 ID | part_id | 数量 --------: | ------: | --------: 1 | 1 | 1 4 | 3 | 4
SELECT MIN(pi.offer_id), pi.part_id, MIN( ( SELECT pps.quantity FROM offers AS pps WHERE pps.offer_id = pi.offer_id ORDER BY pps.quantity DESC LIMIT 1 )) AS q FROM offers AS pi JOIN parts p ON p.id = pi.part_id WHERE p.group_id = 1 and p.part_id IS NULL GROUP BY part_id ORDER BY q asc
最小值(pi.offer_id)| part_id | q ---------------: | ------: | -: 1 | 1 | 1 4 | 3 | 4
db<>在这里摆弄
推荐阅读
- spring - PrincipalExtractor 和 AuthoritiesExtractor 没有命中
- react-native - 在组件中定义 NavBar 的右键,而不是在场景中(react-native-router-flux)
- python - 相当于Python中R的qqplot
- python-3.x - 如何使用flask检查每个文件夹是否有特定文件并使用jinja2显示结果?
- sql - 如何使用 SQL Server 创建算法
- python - Kolmogorov-Smirnov 解释两个信号之间的结果
- kubernetes - 在 Kubernetes 中,是否可以通过服务选择器中的元数据字段来引用 pod?
- css - Wordpress 主题更改样式表的顺序
- typo3 - 为什么我使用 TYPO3 V9.5 后会出现 404 错误
- maven - maven:存储库中的插件,但 mvn 没有找到它