我想要一个显示数据客户端的页面。这是我想要的页面:
----------------------------------
name | skill A | skill B
----------------------------------
John | PHP | MySQL, Javascript
Doe | Javascript | MySQL
Richard | PHP | Javascript
----------------------------------
下面是我的MySQL表:
客户端表:
-----------------------------------------
id_client | name_client | email_client
-----------------------------------------
1 | John | john@email.com
2 | Doe | doe@email.com
3 | Richard | richard@email.com
------------------------------------------
技能表
-----------------------------
id_skill | name_skill
-----------------------------
1 | PHP
2 | MySQL
3 | Javascript
-----------------------------
客户端_杀死
---------------------------------
skill | client | status
---------------------------------
1 | 1 | A
2 | 1 | B
3 | 2 | A
2 | 2 | B
1 | 3 | A
3 | 3 | B
---------------------------------
从这些数据中,我想通过id_client来挖掘连接三个表组的数据,并且每个客户端都有技能,该组由技能值客户端A和B组成。
所以我想在一行中显示客户端技能A和客户端技能B。应该使用什么查询,以及如何在JSON中回显,以便在angularjs中显示它?
这是您的查询。
SELECT a.name_client,
MAX(CASE WHEN b.status = 'A' THEN c.name_skill END) 'SKILL A',
MAX(CASE WHEN b.status = 'B' THEN c.name_skill END) 'SKILL B'
FROM client a
LEFT JOIN client_skill b ON b.client = a.id_client
LEFT JOIN skill c ON c.id_skill = b.skill
GROUP BY a.id_client
ORDER BY id_client