sql - 按成分名称查找食谱，并尽可能排序

Question

我在为我的数据库编写查询时遇到问题。我在这里找到了许多类似的例子，几个小时以来我一直在努力思考它，但没有一个例子能帮助我弄清楚如何得到我想要的结果。我正在构建一个食谱应用程序，但我对所有与数据库有关的东西都很陌生。
我想选择1 种或多种 成分并获得匹配的食谱标题，如果可能，按最佳结果排序(包含最多成分的食谱).我尝试进行内部连接，尝试将食谱名称与匹配的成分放在一起。

我尝试像这个人在这个问题上做的那样，因为我们有相同的设计: Recipe Database, search by ingredient

但即使我尝试像他/她那样进行连接，只是为了测试(而不是像我想要的那样)，我得到:

ERROR:  missing FROM-clause entry for table

这是我的表格:

Recipe_Ingredient

+-----------+---------------+
| recipe_id | ingredient_id |
+-----------+---------------+
|         1 |             1 |
|         1 |             2 |
|         1 |             3 |
|         1 |             4 |
|         1 |             5 |
|         1 |             6 |
|         1 |             7 |   
|         2 |             1 |
|         2 |             8 |
|         2 |             9 |
|         2 |            10 |
|         2 |            11 |
+-----------+---------------+

食谱

+-----------+-----------------------+--------------+
|       id  | name                  | instructions |
+-----------+-----------------------+--------------+
|         1 | Guacamole             | sample text  |
|         2 | Grilled avocado toast | sample text  |
+-----------+-----------------------+--------------+

成分

+------------------+
| id | name        |
+----+-------------+
|  1 | avocado     |
|  2 | tomato      |
|  3 | chili       |
|  4 | garlic      |
|  5 | lemon       |
|  6 | salt        |
|  7 | black pepper|
|  8 | pesto       |
|  9 | spinach     |
| 10 | hard cheese |
| 11 | bread       |
+------------------+

数据库

create table Recipe (
id SERIAL PRIMARY KEY,
name CHAR(50),
prep_time CHAR(10),
instructions VARCHAR(2000));

create table Ingredient (
id SERIAL PRIMARY KEY,
name CHAR(50));

create table Recipe_Ingredient (
recipe_id INT NOT NULL,
ingredient_id INT NOT NULL,
FOREIGN KEY(recipe_id) REFERENCES Recipe(id),
FOREIGN KEY(ingredient_id) REFERENCES Ingredient(id));

Answer 1

您是否正在寻找这样的查询:

with cte as (
select recipe_id, count(*) as cnt from Recipe_Ingredient
group by recipe_id
) select r.id as Recipeid, r.name, c.cnt from Recipe r join cte c
    on r.id = c.recipe_id
    order by c.cnt desc