合并两个垂直表到一个水平表

合并两个垂直表到一个水平表

本文介绍了合并两个垂直表到一个水平表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

表定义

表1(水平)这是用户的一个表

Table 1 (horizontal) This is a table of users

| id | name | phone |
---------------------
| 1  | Bob  | 800   |
| 2  | Phil | 800   |

表2(垂直表)这是球队的一个表

Table 2 (Vertical Table) This is a table of teams

| id | name      |
------------------
| 1  | Donkey    |
| 2  | Cat       |

表3(垂直表)此表是连接前两个

Table 3 (Vertical Table) This table is connecting the first two

| id | user_id | team_id |
--------------------------
| 1  |    1    |   1     |
| 2  |    1    |   2     |
| 3  |    2    |   1     |


我的目标

我希望能够查询以这样的方式,我得到以下后面的数据:

I would like to be able to query the data in such a way that i get the following back:

| id | name | phone | Donkey | Cat  |
-------------------------------------
| 1  | Bob  | 800   | 1      | 1    |
| 2  | Phil | 800   | 1      | Null |

此表将有我的水平表数据,那么其它两个垂直的表的组合,以创建所附列。其中,表2最终被列名的标题。和行衣被合计从表三成一个布尔拉。

This table would have my horizontal table data, then a combination of the other two vertical tables to create the appended columns. Where table 2 ends up being the column name headings. And the row valus are pulled from table three as a boolean.

推荐答案

您在追逐一个数据透视表:

You're chasing a pivot table:

select u.*,
  sum(case when t1.name = 'Donkey' then 1 else 0 end) Donkey,
  sum(case when t1.name = 'Cat' then 1 else 0 end) Cat
  from users u
    inner join user_team ut1
      on u.id = ut1.user_id
    inner join teams t1
      on ut1.team_id = t1.id
  group by name

演示: http://sqlfiddle.com/#!9/5fd33/7

这篇关于合并两个垂直表到一个水平表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-31 05:51