mysql - 如何仅使用一个 SELECT 进行此 SQL 查询

Question

我在创建特定 SQL 查询时遇到困难，仅在一个查询中(我不能两次访问数据库，对于设计架构，请相信我)以下是语句:

我有四张表:问题，地点，国家、地区

这是他们的一些字段:

Questions
    id
    description
Locations
    id
    type (could be 'country' or 'region')
    question_id
    country_or_region_id (an id, that holds either the country or the region id)
Countries
    id
    name
Regions
    id
    name

我想要得到的是这样的:

示例:

1       What is your name?        Venezuela, Colombia              South America

格式:

question id,      question description,       countries,        regions

<小时/>

编辑:对于那些询问的人，我正在使用 MySQL

编辑:对于那些说这是一个糟糕设计的人:我没有创建它，我无法更改设计，我只能这样做，就像现在一样。

Answer 1

如果这是 MySQL:

SELECT  q.ID,
        q.Description,
        GROUP_CONCAT(DISTINCT c.name) AS countries,
        GROUP_CONCAT(DISTINCT r.name) AS regions
FROM    Questions q
        INNER JOIN Locations l
            ON l.question_id = q.id
        LEFT JOIN Countries c
            ON c.id = country_or_region_id
            AND l.type = 'country'
        LEFT JOIN Regions R
            ON R.id = country_or_region_id
            AND l.type = 'region'
GROUP BY q.ID, q.Description;

如果这是 SQL-Server:

SELECT  q.ID,
        q.Description,
        countries = STUFF(( SELECT ', ' + c.name
                            FROM    Locations l
                                    INNER JOIN Countries c
                                        ON c.id = country_or_region_id
                                        AND l.type = 'country'
                            WHERE   l.question_id = q.id
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''),
        regions = STUFF((   SELECT ', ' + r.name
                            FROM    Locations l
                                    INNER JOIN Regions r
                                        ON r.id = country_or_region_id
                                        AND l.type = 'region'
                            WHERE   l.question_id = q.id
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM    Questions q;