MySQL内部连接并加总两列 [英] MySQL Inner join and sum two columns

问题描述

我有下表

表格:约会

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)加入两个表，将每个约会ID的约会产品的总数"相加，如果"PAID"不等于PRICE(来自约会表)+总数(来自约会产品)中的PRICE，则显示它./p>

这是我到目前为止所做的:

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的总数

解决方案

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;

I have the following tables

TABLE: appointments

ID | PRICE | PAID
48 |  100  | 180

TABLE: appointments_products

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

I Would like to somehow run a MySQL query that will:

a) join the two tables, SUM the "TOTAL" of appointments_products for each appointment_id and if the "PAID" is not equal of the PRICE (from appointments table) + TOTAL (from appointments_products table) then to show it.

This is what I have done so far:

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

But this query does not sum the total for each appointment_id

解决方案

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;

这篇关于MySQL内部连接并加总两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！