php - MySQL:使用每行提取的列总和链接表

Question

我有 3 张 table :

MySQL:我需要链接这些表并提取结果表中每行一列的总和

tbl兑换

+---------------------+
| OrderID | OrderDesc | 
+---------------------+
| 1000001 | aaaa      | 
| 1000002 | bbbb      | 
| 1000003 | cccc      |
| 1000004 | dddd      |
+---------------------+

tblLink

+------------------------+
| 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          |
+---------------------------------------------------------------------+

1. tblSubmission 的数量比 tblredemption 的数量更大。
2. 有些客户通过手机识别了多个条目 tblSubmission(请参阅 Carl)。
3. 表 tbLink 连接 2 个 tblSubmission 和 tblRdemption。
4. 生成的表格还需要一个新列，用于保存每个特定客户(通过移动设备)的总金额。

5. 预期结果需要包括:

   • “tblRedemption”表中的 SubmissionID 和 OrderDesc。
   • tblSubmissions 表中的姓名、手机号码和金额。

我的所有查询都会给我一小部分或仅一行，或“返回多行”。

有什么想法吗？

Answer 1

试试这个:

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

编辑:

包含 order 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 |