SQLite 按另一个表中的条件查询顺序？

Question

我有一个 SQLite 数据库表“产品”，其中包含一个项目列表，每个项目都有一个类别和估计的利润。就像是

|id | product  | category_id  | profit | customer_id |
|---|----------|--------------|--------|-------------|
| 1 | product1 | 1            | 15     | 0           |
| 2 | product2 | 2            | 10     | 0           |
| 3 | product3 | 1            |  9     | 0           |
| 4 | product4 | 2            | 12     | 0           |

我还有一张“客户”表，可以选择产品，每人一个。客户有一个他们想要选择的首选类别和一个选择顺序:

|id | name        | category_id  | order |
|---|-------------|--------------|-------|
| 1 | customer1   | 2            | 1     |
| 2 | customer2   | 1            | 2     |
| 3 | customer3   | 1            | 3     |

每个产品都是不同的，因此只有一个其他客户可以选择它，这就是客户订单很重要的原因。我想要一个基于客户的首选类别和订单的产品订购列表，假设客户将始终选择该类别中剩余的最高利润产品。就像是:

|name        | product      | profit |
|------------|--------------|--------|
|customer1   | product4     | 12     |
|customer2   | product1     | 15     |
|customer3   | product3     |  9     |

我知道我可以加入类别列上的表格，并且可以按客户订单订购列表，但我不确定如何强制限制每个产品只能选择一次。

例如

SELECT customers.name, products.name, products.profit
  FROM customers INNER JOIN products ON customers.category_id = products.category_id
  ORDER BY customers.order, products.profit

只是给我列出了与所有产品交叉的所有客户的列表:

|name        | product      | profit |
|------------|--------------|--------|
|customer1   | product4     | 12     |
|customer1   | product2     | 10     |
|customer2   | product1     | 15     |
|customer2   | product3     |  9     |
|customer3   | product1     | 15     |
|customer3   | product3     |  9     |

你能帮我写正确的查询吗？

更新:编辑了上面的表格以结合答案中的一些想法。假设 products.customer_id = 0 表示未选择的产品，我可以编写以下查询。

UPDATE products SET customer_id = customers.id 
WHERE products.customer_id = 0 AND products.category_id = customers.category_id

我不希望它完全按照我想要的方式工作，因为它还没有解决利润列，而且我不确定这个查询实际上是如何处理 wrt customer_id 的。我会测试一下并回复。

Answer 1

啊，我想我一开始误解了这个问题。让我再试一次...

如何在产品中添加一个“user_id”列来指定哪个用户选择了它。

如果给定产品只能由单个用户选择，您需要通过某种方式知道它已被选择，以及由谁选择。我可能会误解您在这里所做的事情，但听起来您正在尝试将产品选择(取决于首选类别、最大利润以及已选择的内容)与查询结果的附加步骤结合起来。您可能需要将此步骤分为两个步骤:

在您的代码中，根据您的标准找出谁获得了哪种产品，并在数据库中将它们标记为此类。

运行查询以获取结果。

我不知道你是否可以在 SQL 查询中执行产品选择步骤，除非你将它与一些疯狂的复杂子查询结合起来，即使那样我也不确定它是否可能。如果你把它分成两个步骤，你的代码会很简单，结果查询也会很简单。