查询通过数据值以JSON从PHP显示列的数据行组 [英] Query to display column data row group by data value in JSON from php
本文介绍了查询通过数据值以JSON从PHP显示列的数据行组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想这显示数据的客户端的页面。这里是我的网页,我想:
I want a page that displaying data client. Here's my page that I want:
----------------------------------
name | skill A | skill B
----------------------------------
John | PHP | MySQL, Javascript
Doe | Javascript | MySQL
Richard | PHP | Javascript
----------------------------------
和这里是我的MySQL表:
and here are my MySQL tables:
客户端表:
-----------------------------------------
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
-----------------------------
client_skill
client_skill
---------------------------------
skill | client | status
---------------------------------
1 | 1 | A
2 | 1 | B
3 | 2 | A
2 | 2 | B
1 | 3 | A
3 | 3 | B
---------------------------------
从这些我想diplaying通过id_client加入三个表组数据和每一个客户都有技能,通过技能的客户价值组A和B.
From these I want to diplaying data that join three tables group by id_client and each client has skill that group by value of skill client that A and B.
所以,我想在一行中显示客户端的功力和客户端的技能B。应该使用什么查询,以及如何在JSON呼应这样我就可以在angularjs显示呢?
So I want to display client skill A and client Skill B in one row. What query should use and how to echoing in JSON so I can display it in 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
这篇关于查询通过数据值以JSON从PHP显示列的数据行组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文