开发者

Mysql rows into columns, optimization query using IF

I have this following query:

SELECT a.rank AS rank, concat( m.prenom, ' ', m.nom ) AS name, d.department
FROM `0_area` AS a
LEFT JOIN 0_member AS m ON a.user_id = m.id
LEFT JOIN 0_depart AS d ON a.user_id = d.user_id
WHERE a.sec_id = 2
AND (a.rank = 'mod' OR a.rank = 'adm')
AND d.department IN ( 75, 92 )

The above query is returning me three columns, naming rank | name | department and it's working, with a separate row for each rank against a department.

rank | name | department

mod  |  Steven Smith   |  75   
mod  |  Jeremy Roy     |  92  
adm  |  Vincent Jones  |  75  

What I need, is to get all those information in one row, becau开发者_高级运维se I need to have it as a part of another bigger query, that is returning one row only.

I thought of group_concat, and to have two columns, one for mod and one for adm. In case there are multiple mods (in 75, 92 for the given example), they will be comma separated.

mod  | adm   

Steven Smith, Jeremy Roy   |  Vincent Jones

Have I been clear? Thanks friends.


Something like:

SELECT
    a.rank AS rank,
    concat( m.prenom, ' ', m.nom ) AS name,
    GROUP_CONCAT(d.department ORDER BY d.department ASC SEPARATOR '|')
FROM
    `0_area` AS a
LEFT JOIN
    0_member AS m
ON
    a.user_id = m.id
LEFT JOIN
    0_depart AS d
ON
    a.user_id = d.user_id
WHERE
    a.sec_id = 2
AND
    (a.rank = 'mod' OR a.rank = 'adm')
AND
    d.department IN ( 75, 92 )
GROUP BY
    a.rank


Some googling told me that I need this sql:

SELECT 
GROUP_CONCAT( if( a.rank='mod', concat( m.prenom, ' ', m.nom ), '' ) ) AS 'mod', 
GROUP_CONCAT( if( a.rank='adm', concat( m.prenom, ' ', m.nom ), '' ) ) AS 'adm'
FROM `0_area` AS a
LEFT JOIN 0_member AS m ON a.user_id = m.id
LEFT JOIN 0_depart AS d ON a.user_id = d.user_id
WHERE a.sec_id =2
AND (a.rank = 'mod' OR a.rank = 'adm' )
AND d.department IN ( 75, 92 )
GROUP BY a.sec_id

@ToonMariner This solves the problem for this query, however, I will still need to put two queries together. Probably I will create a new post for this one.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜