mariadb 如何从查询中排除所有行,其中是成本小于10的单个项目

zaqlnxep  于 2022-11-08  发布在  其他
关注(0)|答案(2)|浏览(113)

对于我的评估,我必须从mysql数据库获得这样的结果:

+---------------------------------------+---------------------------------------+-------+
| name                                  | name                                  | item  |
+---------------------------------------+---------------------------------------+-------+
| Krispy Kreme - Edinburgh Lothian Road | 6 Assorted Doughnuts                  | 12.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed Dozen                 | 14.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed Double Dozen          | 23.95 |
| Krispy Kreme - Edinburgh Lothian Road | Sharer Dozen                          | 17.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed & Sharer Double Dozen | 24.95 |
| Krispy Kreme - Edinburgh Lothian Road | Sharer Double Dozen                   | 27.95 |
+---------------------------------------+---------------------------------------+-------+

显示每样东西都超过10英镑的餐厅的名称和外送菜单项。

  • 这就是问题所在。

我试过了

SELECT restaurant.name AS "restaurant name", food_item.name AS "item name", 
       food_item.price AS "item price"
FROM restaurant 
JOIN food_item ON restaurant.id = food_item.restaurant_id AND 
     food_item.price 
WHERE food_item.price > 10;

但是我需要接收上面提到的答案,而不指定名称。我必须排除菜单中的位置价格低于10美元的所有餐馆。
已附加实体关系图

nbnkbykc

nbnkbykc1#

如果我没理解错的话,你想得到所有餐馆的所有价格,但是如果一家餐馆的菜单上有价格低于10美元的菜,那么这家餐馆的任何东西都不会被退回。
在这种情况下,您必须添加一个子查询,就像下面的例子一样。请注意,它未经测试,但它应该会给您一个想法:

SELECT
        restaurant.NAME AS "restaurant name",
        food_item.NAME AS "item name",
        food_item.price AS "item price" 
    FROM
        restaurant
        JOIN food_item ON restaurant.id = food_item.restaurant_id 
        AND food_item.price 
    WHERE
        NOT EXISTS (select * from restaurant AS sub JOIN food_item AS food_item_sub ON sub.id = food_item_sub.restaurant_id WHERE food_item_sub.price <= 10 and sub.id = restaurant.id)
0ve6wy6x

0ve6wy6x2#

如果您需要排除某个餐馆,如果它的菜单选项的 any 成本为10或更低,则可以将其表示为 not exists 半连接。
另请注意,下面提供一些简短而有意义的别名有助于使查询更简洁、更易读:

select 
  r.name  as restaurant_name, 
  f.name  as item_name, 
  f.price as item_price
from restaurant r
join food_item f on f.restaurant_id = r.id
where not exists (
  select * from food_item f
  where f.restaurant_id = r.id and f.item_price <= 10
);

相关问题