MySQL连接两个表的总和，其中和分组依据 [英] Mysql join two tables sum, where and group by

问题描述

我具有以下结构的两个表:

I have two tables in the following structure:

sales
 |date       |time     | name   | total |
 |2017-04-01 |10:23:59 | aaa    |  100  |
 |2017-04-01 |10:23:59 | aaa    |  150  |
 |2017-04-01 |11:33:30 | bbb    |  200  |
 |2017-04-01 |11:33:30 | bbb    |  120  |
 |2017-04-02 |10:50:59 | aaa    |  70   |
 |2017-04-02 |10:30:59 | bbb    |  35   |

payment
 |date       |time     | name   | amount |
 |2017-04-01 |10:23:59 | aaa    |  300   |
 |2017-04-01 |11:33:30 | bbb    |  400   |
 |2017-04-02 |10:50:59 | aaa    |  425   |
 |2017-04-02 |10:30:59 | bbb    |  600   |

条款

1. sales.time =付款时间
2. 日期= 2017-04-01
3. 总和(总销售额)和总和(付款金额)
4. 按时间分组

我想要这个结果

 |date       |time     | name   | sum(total) | sum(amount)|
 |2017-04-01 |13:23:59 | aaa    |    250     |    300     |
 |2017-04-01 |12:33:30 | bbb    |    320     |    400     |

表结构

CREATE TABLE `payment` (`id` int(5) NOT NULL,`date` date NOT NULL,`time` time NOT NULL,`name` varchar(10) NOT NULL,`amount` varchar(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
INSERT INTO `payment` (`id`, `date`, `time`, `name`, `amount`) VALUES(3, '2017-04-01', '10:23:59', 'aaa', '300'),(4, '2017-04-01', '11:33:30', 'bbb', '400'),(5, '2017-04-02', '10:50:59', 'aaa', '425'),(6, '2017-04-02', '10:30:59', 'bbb', '600');
CREATE TABLE `sales` ( `id` int(5) NOT NULL,`date` date NOT NULL,`time` time NOT NULL,`name` varchar(10) NOT NULL,`total` int(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `sales` (`id`, `date`, `time`, `name`, `total`) VALUES(1, '2017-04-01', '10:23:59', 'aaa', 100),(2, '2017-04-01', '10:23:59', 'aaa', 150),(3, '2017-04-01', '11:33:30', 'bbb', 200),(4, '2017-04-01', '11:33:30', 'bbb', 120),(5, '2017-04-02', '10:50:59', 'aaa', 70),(6, '2017-04-02', '10:50:59', 'bbb', 35);
ALTER TABLE `payment` ADD PRIMARY KEY (`id`);
ALTER TABLE `sales` ADD PRIMARY KEY (`id`);
ALTER TABLE `payment` MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
ALTER TABLE `sales` MODIFY `id` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

查询

SELECT sales.time,sales.name,  
SUM(sales.total),SUM(payment.amount)
FROM sales,payment  
WHERE sales.time=payment.time
and sales.date like '2017-04-01%'
GROUP BY sales.time
ORDER BY sales.time;

结果是

10:23:59    aaa 250 600
11:33:30    bbb 320 800

推荐答案

您正在将付款金额与销售记录的数量相乘，因为您在将所有付款记录与所有销售记录合并之前才对金额进行求和.

You are multiplying the payments amount with the number of sales records, because you are joining all payments records with all sales records before summing up the amounts.

先聚合，然后才加入.

如果每个日期，时间和姓名始终只有一个付款记录:

In case there can always only be one payments record per date, time and name:

select p.name, p.time, p.name, s.sales_total, p.amount
from payments p
join
(
  select date, time, name, sum(total) as total
  from sales
  group by date, time, name
) s
 on s.date = p.date and s.time = p.time and s.name = p.name
where p.date = date '2017-04-01';

否则:

select p.name, p.time, p.name, s.total, p.amount
(
  select date, time, name, sum(amount) as amount
  from payments
  group by date, time, name
) p
join
(
  select date, time, name, sum(total) as total
  from sales
  group by date, time, name
) s
 on s.date = p.date and s.time = p.time and s.name = p.name
where p.date = date '2017-04-01';

这篇关于MySQL连接两个表的总和，其中和分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！