我有一张产品表和另一张这些产品的具体 list .
CREATE TABLE products (id INT AUTOINCREMENT);
CREATE TABLE listings (
id INT AUTOINCREMENT,
product INT REFERENCES products(id),
vendor INT
)
我想 Select 一组产品的上市，这样他们是由不同的供应商的最小计数出售.例如:
| id | 产品 | 供应商 |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 2 | 1 |
| 5 | 3 | 4 |
对于产品(1,2,3)，我希望获得ID (1,4,5).有没有一种只使用SQL就能实现这一点的方法？或者，我应该简单地使用多个查询并在其他地方组合结果吗？
不幸的是，正如我在 comments 中解释的那样，您试图解决的问题看起来像是一个NP完全问题，您已经正确地将其标识为set-cover problem.
这意味着您不能在多项式时间内解决一般情况.
Conclusion:
Let alone maintainabilty of complex SQL queries, you probably do not want to run anything with exponantial complexity on a database; I recommend you compute the solution elsewhere, using a language and an algorithm more suitable for the task than SQL can do.
Finding an approximation of the solution can be done in polynomial time (explained in the link above). That could theoretically be done using not-too-complex SQL queries (possibly using one of the queries posted as answers, I have not checked) but even then, I would not recommend that path; better doing it outside of the database.