我有3张桌子：
MySQL：我需要链接这些表，并提取结果表中每行的某一列的总和
tblRedemption
+---------------------+
| OrderID | OrderDesc |
+---------------------+
| 1000001 | aaaa |
| 1000002 | bbbb |
| 1000003 | cccc |
| 1000004 | dddd |
+---------------------+
链接
+------------------------+
| SubmissionID | OrderID |
+------------------------+
| 201 | 1000001 |
| 202 | 1000002 |
| 203 | 1000003 |
| 204 | 1000004 |
+------------------------+
tbl提交
+-------------------------------------------+
| SubmissionID | Name | Mobile | Amount |
+-------------------------------------------+
| 150 | Amy | 1111111111 | 10 |
| 200 | Bob | 2222222222 | 20 |
| 201 | Carl | 3333333333 | 30 |
| 202 | Dave | 4444444444 | 10 |
| 203 | Carl | 3333333333 | 25 |
| 204 | Fin | 5555555555 | 35 |
+-------------------------------------------+
预期成绩：
+---------------------------------------------------------------------+
| SubmissionID | Name | mobile | OrderDesc | Amount | TotalAmount |
+---------------------------------------------------------------------+
| 201 | Carl | 3333333333 | aaaa | 30 | 55 |
| 203 | Carl | 3333333333 | bbbb | 25 | 55 |
| 204 | Fin | 5555555555 | cccc | 35 | 35 |
| 202 | Dave | 4444444444 | dddd | 10 | 10 |
+---------------------------------------------------------------------+
tblSubmission的数量比tblredemption更大。
有些客户的移动设备标识了多个条目tblSubmission（请参阅Carl）。
表tbLink连接2个tblSubmission和tblRdemption。
结果表还需要一个新列，其中包含每个特定客户（通过移动设备）的总金额。
预期结果需要包括：
“ tblRedemption”表中的SubmissionID和OrderDesc。
tblSubmissions表中的名称，移动电话和金额。
我所有的查询都给了我一个很小的子集，或者只有一行，或者“返回了多行”。
有任何想法吗？
最佳答案
尝试这个：
select l.submissionid, s.name, s.mobile, s.amount, t.totalamount
from tbllink l
inner join tblsubmissions s on l.submissionid = s.submissionid
inner join (
select `name`, sum(amount) as totalamount
from tblsubmissions
group by `name`
) t on s.`name` = t.`name`
示例：http://sqlfiddle.com/#!9/2ea56a/5
同一查询按手机号码总计
select l.submissionid, s.name, s.mobile, s.amount, t.totalamount
from tbllink l
inner join tblsubmissions s on l.submissionid = s.submissionid
inner join (
select mobile, sum(amount) as totalamount
from tblsubmissions
group by mobile
) t on s.mobile = t.mobile
编辑：
包括命令desc也比较容易。让我们来看看：
select l.submissionid, s.name, s.mobile, r.orderdesc, s.amount, t.totalamount
from tbllink l
inner join tblredemption r on l.orderid = r.orderid
inner join tblsubmissions s on l.submissionid = s.submissionid
inner join (
select `name`, sum(amount) as totalamount
from tblsubmissions
group by `name`
) t on s.`name` = t.`name`
示例：http://sqlfiddle.com/#!9/63540/2
桌子
create table tblredemption (orderid int, orderdesc varchar(100));
insert into tblredemption values (1000001, 'aaaa'), (1000002, 'bbbb'), (1000003, 'cccc'), (1000004, 'dddd');
create table tbllink (submissionid int, orderid int);
insert into tbllink values (201,1000001), (202,1000002), (203,1000003), (204,1000004);
create table tblsubmissions (
submissionid int,
`name` varchar(20),
mobile varchar(20),
amount int
);
insert into tblsubmissions values
( 150 , 'Amy' , '1111111111', 10 ),
( 200 , 'Bob' , '2222222222' , 20 ),
( 201 , 'Carl' , '3333333333' , 30 ),
( 202 , 'Dave' , '4444444444' , 10 ),
( 203 , 'Carl' , '3333333333' , 25 ),
( 204 , 'Fin' , '5555555555' , 35 );
结果：
| submissionid | name | mobile | orderdesc | amount | totalamount |
|--------------|------|------------|-----------|--------|-------------|
| 201 | Carl | 3333333333 | aaaa | 30 | 55 |
| 203 | Carl | 3333333333 | cccc | 25 | 55 |
| 202 | Dave | 4444444444 | bbbb | 10 | 10 |
| 204 | Fin | 5555555555 | dddd | 35 | 35 |