表定义
表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演示: sqlfiddle/#!9/5fd33/7
更多推荐
合并两个垂直表到一个水平表
发布评论