时间戳

首页 > 解决方案 > JOIN 两个表并得到结果

mysql - JOIN 两个表并得到结果

问题描述

我有两个表如下：

tbl_main
----------------------------------
id | bucket | amount 
1  |  1     | 10
2  |  2     | 10
3  |  1     | 20
4  |  4     | 10
5  |  5     | 20
6  |  6     | 30
7  |  6+    | 50
8  |  3     | 30
9  |  5     | 50

另一个表格如下

tbl_sub
----------------------------------
id | bucket | amount | status
1  |  1     | 10     |  1
2  |  1     | 10     |  1
3  |  1     | 20     |  1
4  |  4     | 10     |  2
5  |  5     | 20     |  1

我想使用存储桶加入这两个表，并在表tbl_sub列状态为 1的情况下获得两个表中每个存储桶的总和

预期结果是

bucket | main_amount | sub_amount
-----------------------------------
 1     |      30     |     40       
 2     |      10     |      0       
 3     |      30     |      0      
 4     |      10     |      0      
 5     |      70     |     20      
 6     |      30     |      0     
 6+    |      50     |      0

我不明白。我哪里错了这是我的查询

SELECT cl.bucket
     , SUM(cl.amount) as main_total
FROM `tbl_main` cl
    JOIN(SELECT bucket, SUM(amount)
         FROM `tbl_sub`
         WHERE status = '1'
         GROUP BY bucket) fl on cl.bucket = fl.bucket
 GROUP BY cl.bucket

这是相同的架构和Db Fiddle链接

CREATE TABLE `tbl_main` ( `id` INT ,  `bucket` VARCHAR(5) ,  `amount` INT ,    PRIMARY KEY  (`id`));     

CREATE TABLE `tbl_sub` ( `id` INT  ,  `bucket` VARCHAR(5) ,  `amount` INT ,  `status` INT,    PRIMARY KEY  (`id`));


INSERT INTO `tbl_main` (`id`, `bucket`, `amount`) VALUES ('1', '1', '10'), ('2', '2', '10'), ('3','1', '20'), ('4', '4', '10'), ( '5','5', '20'), ( '6','6', '30'), ( '7','6+', '50'), ('8','3', '30'), ('9', '5', '50');


INSERT INTO `tbl_sub` (`id`, `bucket`, `amount`, `status`) VALUES ( '1','1', '10', '1'), ('2','1', '10', '1'), ('3','1', '20', '1'), ('4','4', '10', '2'), ('5','5', '20', '1');

标签： mysqlsqlgroup-bysubqueryjointable

解决方案

尝试这个：

使用主查询获取 main_amount 并使用子查询计算链接到主存储桶的 sub_amount，如下所示：

SELECT cl.bucket,SUM(cl.amount) as main_total,
COALESCE((SELECT SUM(s.amount) FROM `tbl_sub` s WHERE s.status='1' AND s.bucket = 
cl.bucket), 0)
FROM `tbl_main` cl
GROUP BY cl.bucket

SQL小提琴

推荐阅读