mysql内部连接并求两列的和

n9vozmp4 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(325)

我有下列表格
表：约会

ID | PRICE | PAID
48 |  100  | 180

表：产品

ID | APPOINTMENT_ID | PRODUCT_ID | TOTAL
10 |       48       |      1     | 30
11 |       48       |      9     | 30
12 |       48       |      6     | 30

我想运行一个mysql查询，它将：
a）将两个表连接起来，对每个预约标识的预约产品的“总计”求和，如果“已付”不等于价格（来自预约表）+总计（来自预约产品表），则显示它。
这就是我目前所做的：

select a.*, b.appointment_id as AppId, b.total as ProdTotal 
from appointments a 
INNER JOIN appointments_products b ON a.id = b.appointment_id

但此查询不会对每个约会id的总数求和

mysql sum inner-join

来源：https://stackoverflow.com/questions/50676463/mysql-inner-join-and-sum-two-columns

3条答案

按热度按时间

oyt4ldly1#

选择b.appointment\u id、a.price、a.paid、a.id、sum（b.total）作为totalprod from appoints\u products as b inner join appoints as a on appointment\u id=a.id group by appointment\u id、a.price、a.paid、a.id having a.paid！=(a、价格+总额（b.total））

赞(0）回复(0）举报 2021-06-20

yeotifhr2#

使用where检查价格是否等于paid，并使用group by to group with appointment\u id。

赞(0）回复(0）举报 2021-06-20

juzqafwq3#

select a.ID,a.PRICE,a.PAID,a.id as AppId,
       sum(b.total) as ProdTotal 
from appointments a 
INNER JOIN appointments_products b ON a.id = b.appointment_id
group by a.ID,a.PRICE,a.PAID;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql内部连接并求两列的和

3条答案

相关问题

热门标签

最新问答