問題描述
表格定義
Table 1 (horizo??ntal) 這是一個用戶表
Table 1 (horizontal) This is a table of users
| id | name | phone |
---------------------
| 1 | Bob | 800 |
| 2 | Phil | 800 |
Table 2 (Vertical Table) 這是一個團隊表
Table 2 (Vertical Table) This is a table of teams
| id | name |
------------------
| 1 | Donkey |
| 2 | Cat |
Table 3 (Vertical Table) 此表連接前兩個
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
這篇關于將兩個垂直表合并到一個水平表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！