我在努力为我的销售额加总。但是,一个用户可以有多个帐户,每个帐户有不同的销售记录。下面是我的桌子。
用户表
user_id username
----------------
1 adam
2 david
3 siva
解释
account user_id
-------------
001 1
002 1
003 1
出售
account sales
-------------
001 20
002 30
003 10
如何计算一个用户的所有销售额?请帮帮我。谢谢
最佳答案
mysql> create table user_table(user_id int, username varchar(20));
Query OK, 0 rows affected (0.45 sec)
mysql> insert into user_table values
-> (1,'adam'),
-> (2,'david'),
-> (3,'siva');
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> create table account(account varchar(20), user_id int);
Query OK, 0 rows affected (0.34 sec)
mysql> insert into account values
-> ('001',1),
-> ('002',1),
-> ('003',1);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> create table sales(account varchar(20),sales int);
Query OK, 0 rows affected (0.26 sec)
mysql> insert into sales values
-> ('001',20),
-> ('002',30),
-> ('003',10);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select u.user_id,sum(sales) sales
-> from user_table u
-> natural join account
-> natural join sales
-> where u.user_id = 1;
+---------+-------+
| user_id | sales |
+---------+-------+
| 1 | 60 |
+---------+-------+
1 row in set (0.00 sec)
关于mysql - 将多个帐户的值相加,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50673625/