SQL 查询仅适用于 1 条记录

Question

我是 sql 的新手 - 从 1 周开始，我的查询仅适用于 1 条记录

Select m.meal_id, m.name, m.usp, m.description, m.worktime, m.proprietor, m.img, m.url, m.servings, d.name, c.name, s.name, sum(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id
and m.meal_id = 1

此查询适用于一条记录，但是如果我想吃所有的饭菜(没有"and"函数(，我仍然只得到一条记录，但总餐价格是针对添加的所有记录的，我如何为所有餐食获取此查询？

我喜欢每顿饭一顿记录，总总餐价格

我的表格：

create table meals
(
meal_id int unsigned not null auto_increment primary key,
name varchar(100) not null,
usp varchar(100) not null,
description varchar(500) not null,
worktime varchar(100) not null,
proprietor varchar(100) not null,
img varchar(100) not null,
url text not null,
servings int not null,
difficulty_id int unsigned not null,
cat_id int unsigned not null,
social_id int unsigned not null,
foreign key (difficulty_id) references difficulty (difficulty_id),
foreign key (cat_id) references cat (cat_id),
foreign key (social_id) references social (social_id)
);
create table ingredients
(
ingredient_id   int unsigned not null auto_increment primary key,
name varchar(45) not null,
minamount float not null,
price float not null
);
create table recipe
(
meal_id int unsigned not null,
ingredient_id int unsigned not null,
quantity float,
primary key (meal_id, ingredient_id),
foreign key (meal_id) references meals (meal_id),
foreign key (ingredient_id) references ingredients (ingredient_id)
);

Answer 1

编辑：基于 OP 最近的编辑;您只需执行GROUP BY即可。

尝试：

Select m.meal_id, 
m.name, 
m.usp, 
m.description, 
m.worktime, 
m.proprietor, 
m.img, 
m.url, 
m.servings, 
d.name, 
c.name, 
s.name, 
SUM(i.price / i.minamount * r.quantity) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 
GROUP BY m.meal_id, 
m.name, 
m.usp, 
m.description, 
m.worktime, 
m.proprietor, 
m.img, 
m.url, 
m.servings, 
d.name, 
c.name, 
s.name 

---

基于以前版本的OP问题：

这是一个窗口函数问题。在较新版本的MySQL(>= 8.0(中，您可以轻松做到这一点。在旧版本(您的版本是 5.5(中，我们可以使用会话变量来解决它。

尝试：

Select m.meal_id, 
m.name, 
m.usp, 
m.description, 
m.worktime, 
m.proprietor, 
m.img, 
m.url, 
m.servings, 
d.name, 
c.name, 
s.name, 
(@sum := @sum + (i.price / i.minamount * r.quantity)) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 
CROSS JOIN (select @sum := 0) AS init 

---

在 MySQL 版本>= 8.0 中，我们可以在完整结果集的分区上使用SUM()作为窗口函数。在这种情况下，查询将是：

Select m.meal_id, 
m.name, 
m.usp, 
m.description, 
m.worktime, 
m.proprietor, 
m.img, 
m.url, 
m.servings, 
d.name, 
c.name, 
s.name, 
SUM(i.price / i.minamount * r.quantity) OVER() as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 

Answer 2

您需要下面的查询，交叉连接可以帮助您获得所需的结果

Select m.meal_id, 
m.name, 
m.usp, 
m.description, 
m.worktime, 
m.proprietor, 
m.img, 
m.url, 
m.servings, 
d.name, 
c.name, 
s.name, 
(@s := @s + (i.price / i.minamount * r.quantity)) as mealPrice
from meals m
join recipe r on m.meal_id = r.meal_id
join ingredients i on i.ingredient_id = r.ingredient_id
join difficulty d on d.difficulty_id = m.difficulty_id
join cat c on c.cat_id = m.cat_id
join social s on s.social_id = m.social_id 
CROSS JOIN ( select @s:= 0) AS t