获取所有产品，然后将特定订单的产品放在第一位 [英] Get all the products, and putting a specific order's product first

问题描述

我有3个表:产品，订单和 orderLines (order_id，product_id).

I have 3 tables: products, orders and orderLines(order_id, product_id).

我有一个sql查询来找出在only one query中似乎几乎不可能做到的事情.
是否可以只在一个查询中使用

I have an sql query to figure out which seems nearly impossible to do in only one query.
Is there a way to have in only one query:

• 所有产品，但首先显示特定订单的产品； 这意味着:对于order A:显示product1，首先显示orderA's orderLines中的product2.，然后显示following products(未排序).

• All the products but showning a specific order's products first; which means that: for an order A: show product1, product2.. present in orderA's orderLines first, than the following products (not ordered) are shown next.

PS: 我知道可以通过两个查询的union来实现，但是最好在only one query中完成.

PS: I know it's possible to achieve this with a union of two queries, but it would be better to have it done in only one query.

推荐答案

您可以在order by子句中放置一个子查询.在这种情况下，您需要一个exists子查询:

You can put a subquery in the order by clause. In this case, an exists subquery is what you need:

select p.*
from products p
order by (exists (select 1
                  from orderlines ol
                  where p.productid = ol.productid and o.orderid = ORDERA
                 )
         ) desc;

这篇关于获取所有产品，然后将特定订单的产品放在第一位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！