MySQL的group_concat与内部选择 [英] MySQL group_concat with select inside select

问题描述

我有一个我无法解决的故障，请让我详细说明...

I have a glitch which i cannot solve,let me elaborate...

这些是我的MySQL表...

These are my MySQL tables...

治疗师表

id  therapist_name
1   Therapist 1
2   Therapist 2 

位置表

+-----+------------+--+
| id  |    name    |  |
+-----+------------+--+
|  1  | Location 1 |  |
|  2  | Location 2 |  |
|  3  | Location 3 |  |
+-----+------------+--+

Days_location表

Days_location table

+-----+-----------+--------------+-------------+--+
| id  |    day    | therapist_id | location_id |  |
+-----+-----------+--------------+-------------+--+
|  1  | monday    |            1 |           1 |  |
|  2  | monday    |            1 |           2 |  |
|   3 | wednesday |            1 |           3 |  |
|   4 | wednesday |            2 |           1 |  |
|   5 | tuesday   |            2 |           2 |  |
|   6 | friday    |            2 |           1 |  |
|   7 | friday    |            2 |           2 |  |
|   8 | friday    |            1 |           1 |  |
+-----+-----------+--------------+-------------+--+

现在，我想让每个治疗师每天都有位置，例如:

Now i want to get every therapist with locations for every day,for example something like this:

therapist_name => Therapist 1，day_locations => monday(Location1，Location2)，friday(Location1)

therapist_name=>Therapist 1,day_locations=>monday(Location1,Location2),friday(Location1)

我需要将其作为选择变量，这是我的查询，但是我被困在那里:

I need it to be as a select variable,this was my query but i got stuck there:

SELECT t.*,GROUP_CONCAT(
    SELECT CONCAT(dl2.day,GROUP_CONCAT(dl2.location_id)) as concated 
    FROM days_location dl2 
    WHERE therapist_id=85 
    GROUP BY dl2.day
) as day_location 
FROM therapists t  
LEFT JOIN days_location dl 
ON dl.therapist_id=t.id

这当然是行不通的，我在做什么错...我应该尝试其他方法还是让表有所不同?

This of course doesn't work,what am i doing wrong...should i try a different approach or make my tables different?

推荐答案

我相信这是您正在寻找的东西，或者可以使您入门:

I believe this is what you're looking for, or could get you started:

SELECT
    t.therapist_name,
    dl.day,
    GROUP_CONCAT(DISTINCT dl.name SEPARATOR ',') AS locations
FROM
    therapists t 
    LEFT JOIN days_location dl ON dl.therapist_id = t.id
    LEFT JOIN location l ON dl.location_id = l.id
GROUP BY t.therapist_name, dl.day

对于therapists.id = 1，这应该为您提供结果:

For therapists.id = 1 this should give you results:

+----------------+-----------+-----------------------+
| therapist_name |    day    |       locations       |
+----------------+-----------+-----------------------+
| Therapist 1    | monday    | Location 1,Location 2 |
| Therapist 1    | wednesday | Location 3            |
| Therapist 1    | friday    | Location 1            |
+----------------+-----------+-----------------------+

如果需要将day与locations列连接，则使用简单的CONCAT():

If you need to concatenate day with locations column then use a simple CONCAT():

SELECT
    therapist_name,
    CONCAT(day, '(', locations, ')') AS locations
FROM (  
    SELECT
        t.therapist_name,
        dl.day,
        GROUP_CONCAT(DISTINCT dl.name SEPARATOR ',') AS locations
    FROM
        therapists t 
        LEFT JOIN days_location dl ON dl.therapist_id = t.id
        LEFT JOIN location l ON dl.location_id = l.id
    GROUP BY t.therapist_name, dl.day
    ) t
GROUP BY therapist_name, locations

输出应如下所示:

+----------------+-------------------------------+
| therapist_name |           locations           |
+----------------+-------------------------------+
| Therapist 1    | monday(Location 1,Location 2) |
| Therapist 1    | wednesday(Location 3)         |
| Therapist 1    | friday(Location 1)            |
+----------------+-------------------------------+

如果需要将每个治疗师的所有内容分组为一行，则可以再次GROUP_CONCAT().

If you need to group it all into one row for each therapist, then you could GROUP_CONCAT() again.

评论后编辑:

SELECT
    therapist_name,
    GROUP_CONCAT( CONCAT(day, '(', locations, ')') SEPARATOR ',' ) AS locations
FROM (      
    SELECT
            t.therapist_name,
            dl.day,
            GROUP_CONCAT(DISTINCT dl.name SEPARATOR ',') AS locations
    FROM
            therapists t 
            LEFT JOIN days_location dl ON dl.therapist_id = t.id
            LEFT JOIN location l ON dl.location_id = l.id
    GROUP BY t.therapist_name, dl.day
    ) t
GROUP BY therapist_name

我尚未测试代码，因此可能有一些小错误需要调整.无法测试atm.

I haven't tested the code so there may be some minor mistakes to tweak. No way of testing it atm.

这篇关于MySQL的group_concat与内部选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！