连接多表并按列多分组得到结果

6yoyoihd 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(324)

我想从三个多列groupby表的结果
表1

| Id| pr_id| c_id| 
|---|------|-----|
| 1 |  33  |  18 |
| 2 |  33  |  19 |
| 3 |  34  |  18 |

表2

|Id| pr_id| qty| 
|--|------|----|
|1 | 33   |  22|
|2 | 33   |  10| 
|3 | 34   |  12|

表3

|c_id| l_id| 
|----|---- |
| 18 |  1  |
| 19 |  2  |

我想要这样的结果

|total_qty| 1(l_id)| 2(l_id)|pr_id| 
|---------|--------|--------|-----|
| 32      |  22    |   10   |  33 |
| 12      |  12    |   0    |  34 |

sql mysql

来源：https://stackoverflow.com/questions/48075863/join-multi-table-and-get-result-by-mutli-bdoup-by-column

1条答案

按热度按时间

azpvetkf1#

使用条件聚合

select t.one + t.two as total_qty, 
       t.*
from
(
  select sum(case when t3.l_id = 1 then t2.qty end) as one,
          sum(case when t3.l_id = 2 then t2.qty end) as two,
          t1.pr_id
   from table3 t3
   join table1 t1 on t1.c_id = t3.c_id
   join table2 t2 on t1.id= t2.id and 
                     t1.pr_id = t2.pr_id
   group by t1.pr_id
) t

赞(0）回复(0）举报 2021-06-21

我来回答

连接多表并按列多分组得到结果

1条答案

相关问题

热门标签

最新问答