具有多个帐户的和值

798qvoo8  于 2021-06-20  发布在  Mysql
关注(0)|答案(5)|浏览(309)

我在为我的销售额计算价值。但是,一个用户可以有多个帐户,每个帐户有不同的销售记录。下面是我的table。
用户表

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

如何计算一个用户的所有销售额?请帮帮我。谢谢

50pmv0ei

50pmv0ei1#

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)
vof42yt1

vof42yt12#

我建议这样做。

select a.username as User_Names ,case when sum(sales) is NULL then 0 else 
    sum(sales) end as Total_Sales 
    from stack_users a 
    left join  stack_account b 
    on a.userid= b.userid 
    left join stack_sales c 
    on c.account = b.account
    group by a.username

输出:

+------------+-------------+
| User_Names | Total_Sales |
+------------+-------------+
| Adam       |  60         |
+------------+-------------+
| David      |  0          |
+------------+-------------+
| siva       |  0          |
+------------+-------------+
ax6ht2ek

ax6ht2ek3#

试试这个

SELECT SUM(s.sales) AS total_sale 
FROM   sales s 
INNER JOIN account a ON a.account = s.account 
WHERE  a.user_id = 'USER_ID'
lbsnaicq

lbsnaicq4#

在此处使用子查询进行查询。请尝试以下操作

SELECT SUM(sales) FROM sales WHERE account IN (SELECT account FROM `account` where user_id = 1)
bcs8qyzn

bcs8qyzn5#

以下是查询:

SELECT Sum(sales) 
FROM   sales s 
       INNER JOIN account a 
               ON a.account = s.account 
       INNER JOIN user_table u 
               ON u.user_id = a.user_id 
WHERE  u.user_id = 1

相关问题