数据库开发之子查询案例的详细解析

 1.5 案例

基于之前设计的多表案例的表结构，我们来完成今天的多表查询案例需求。

准备环境

将资料中准备好的多表查询的数据准备的SQL脚本导入数据库中。

编辑

• 分类表：category
• 菜品表：dish
• 套餐表：setmeal
• 套餐菜品关系表：setmeal_dish
• 编辑

需求实现

1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称

登录后复制

/*查询技巧：
     明确1：查询需要用到哪些字段
        菜品名称、菜品价格 、 菜品分类名
     明确2：查询的字段分别归属于哪张表
        菜品表：[菜品名称、菜品价格]
        分类表：[分类名]
     明确3：如查多表，建立表与表之间的关联
        菜品表.caategory_id = 分类表.id
     其他：（其他条件、其他要求）
        价格 < 10
*/
select d.name , d.price , c.name
from dish AS d , category AS c
where d.category_id = c.id
      and d.price < 10;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.

编辑

1. 查询所有价格在 10元(含)到50元(含)之间 且 状态为"起售"的菜品名称、价格及其分类名称 (即使菜品没有分类 , 也要将菜品查询出来)

登录后复制

select d.name , d.price, c.name
from dish AS d left join category AS c on d.category_id = c.id
where d.price between 10 and 50
      and d.status = 1;

• 1.
• 2.
• 3.
• 4.

编辑

1. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格

登录后复制

select c.name , max(d.price)
from dish AS d , category AS c
where d.category_id = c.id
group by c.name;

• 1.
• 2.
• 3.
• 4.

编辑

1. 查询各个分类下 菜品状态为 "起售" , 并且 该分类下菜品总数量大于等于3 的 分类名称

登录后复制

/*查询技巧：
     明确1：查询需要用到哪些字段
        分类名称、菜品总数量
     明确2：查询用到的字段分别归属于哪张表
        分类表：[分类名]
        菜品表：[菜品状态]
     明确3：如查多表，建立表与表之间的关联
        菜品表.caategory_id = 分类表.id
     其他：（其他条件、其他要求）
        条件：菜品状态 = 1 (1表示起售)
        分组：分类名
        分组后条件： 总数量 >= 3
*/
select c.name , count(*)
from dish AS d , category AS c
where d.category_id = c.id
      and d.status = 1 -- 起售状态
group by c.name  -- 按照分类名分组
having count(*)>=3; -- 各组后筛选菜品总数据>=3

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

编辑

1. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）

登录后复制

select s.name, s.price, d.name, d.price, sd.copies
from setmeal AS s , setmeal_dish AS sd , dish AS d
where s.id = sd.setmeal_id and sd.dish_id = d.id
      and s.name='商务套餐A';

• 1.
• 2.
• 3.
• 4.

编辑

1. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)

登录后复制

-- 1.计算菜品平均价格
select avg(price) from dish;    -- 查询结果：37.736842
-- 2.查询出低于菜品平均价格的菜品信息
select * from dish where price < 37.736842;

-- 合并以上两条SQL语句
select * from dish where price < (select avg(price) from dish);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

编辑

原创作者: u_16265376 转载于: https://blog.51cto.com/u_16265376/11536689