mysql - SQL Order By via Junction 表
问题描述
我有一个简单的一对多相关产品设置。我的数据库有两个表:
product
带有列的表和带有id, name
列的连接表。product_assignments
product_id, related_id, order
我需要获取所有相关产品,按order
列排序
SELECT * FROM `products` LEFT JOIN
`related_assignments` `ra` ON `products`.`id` =
`ra`.`product_id` WHERE `id` IN (412, 1663, 1928) ORDER BY
`ra`.`order` DESC
我没有看到任何错误,我得到了所有三个记录(412、1663、1928),但按它们的 ID 排序,而不是按顺序字段。
如何使用一个 SQL 请求对它们进行排序?
产品
+------+-------+
| id | name |
+------+-------+
| 412 | Watch |
| 1663 | Book |
| 1928 | Phone |
| 2000 | Cup |
+------+-------+
有关的
+------------+------------+-------+
| product_id | related_id | order |
+------------+------------+-------+
| 2000 | 412 | 1 |
| 2000 | 1663 | 2 |
| 2000 | 1928 | 0 |
+------------+------------+-------+
解决方案
从您的样本数据和预期结果来看,您似乎需要使用ra.related_id
连接条件而不是 ra.product_id
,
当您使用它Left join
时products.id =ra.product_id
,只返回id = 2000
行数据。但是您的 where 子句使用id IN (412, 1663, 1928)
,那将让ra.order
一切都成为null
,因此order by ra.order
将什么也不做。
create table products(
id int,
name varchar(50)
);
insert into products values ( 412, 'Watch');
insert into products values (1663, 'Book ');
insert into products values (1928, 'Phone');
insert into products values (2000, 'Cup ');
create table Related(
product_id int,
related_id int,
`order` int
);
insert into Related values (2000, 412 ,1 );
insert into Related values (2000, 1663 ,2 );
insert into Related values (2000, 1928 ,0 );
查询 1:
SELECT *
FROM `products` LEFT JOIN
`Related` `ra` ON `products`.`id` =
`ra`.`related_id`
WHERE `id` IN (412, 1663, 1928)
ORDER BY `ra`.`order` DESC
结果:
| id | name | product_id | related_id | order |
|------|-------|------------|------------|-------|
| 1663 | Book | 2000 | 1663 | 2 |
| 412 | Watch | 2000 | 412 | 1 |
| 1928 | Phone | 2000 | 1928 | 0 |