mysql - 根据对另一个表的选择结果更新表

Question

我有两个这样的表:

人:

id | name | sale | commission
1  | abc  | 0    |   0
2  | xyz  | 0    |   0

促销:

id | date       | person_id | sale | commission
1  | 2016-05-01 |     1     | 10   |     1
2  | 2016-05-02 |     1     | 10   |     1
3  | 2016-05-03 |     1     | 10   |     1
4  | 2016-05-01 |     2     | 20   |     2
5  | 2016-05-02 |     2     | 20   |     2
6  | 2016-05-01 |     2     | 20   |     2

我想用单个更新查询来更新 person 表，并像这样更改表:

人:

id | name | sale | commission
1  | abc  | 30   |   3
2  | xyz  | 60   |   6

我知道我可以像下面那样对销售进行求和，但是如何将下面的查询结果直接更新到人员表中。

SELECT person_id, SUM(sale), SUM(commission) 
FROM sale
GROUP BY person_id; 

Answer 1

正如 Strawberry 在您的问题下的评论中所说，在您保存此信息之前请三思而后行。它被非规范化，变得陈旧。相反，请考虑在报告生成期间使用它。否则，如前所述，您可能会遇到问题。

drop table if exists person;
create table person
(   personId int auto_increment primary key,
    name varchar(100) not null,
    totSales decimal(9,2) not null,
    totComm decimal(9,2)
);
insert person(name,totSales,totComm) values
('Joe',0,0),
('Sally',0,0);
-- just added persons 1 and 2 (auto_inc)

drop table if exists sale;
create table sale
(   saleId int auto_increment primary key,
    saleDate date not null,
    personId int not null,
    sale decimal(9,2) not null,
    commission decimal(9,2) not null,
    index(personId), -- facilitate a snappier "group by" later
    foreign key (personId) references person(personId) -- Ref Integrity
);

insert sale(saleDate,personId,sale,commission) values
('2016-05-01',2,10,1),
('2016-05-01',1,40,4),
('2016-05-02',1,30,3),
('2016-05-07',2,10,1),
('2016-05-07',2,90,9);

-- the following dies on referential integrity, FK, error 1452 as expected
insert sale(saleDate,personId,sale,commission) values ('2016-05-01',4,10,1);

更新语句

update person p 
join  
(   select personId,sum(sale) totSales, sum(commission) totComm 
    from sale 
    group by personId 
) xDerived 
on xDerived.personId=p.personId 
set p.totSales=xDerived.totSales,p.totComm=xDerived.totComm;

结果

select * from person;
+----------+-------+----------+---------+
| personId | name  | totSales | totComm |
+----------+-------+----------+---------+
|        1 | Joe   |    70.00 |    7.00 |
|        2 | Sally |   110.00 |   11.00 |
+----------+-------+----------+---------+
2 rows in set (0.00 sec)

xDerived 只是一个别名。所有派生表都需要一个别名，无论您是否明确使用别名。