有很多方法可以做到这一点。对当前查询的最小更改就是简单地更改几个引用：

SELECT  g.name AS genre, 
        SUM(l.month_start_value) AS SumStartValue, 
        GROUP_CONCAT(l.artist_id) AS ArtistIDs, 
        GROUP_CONCAT(l.month_start_value) AS MonthStartValues
FROM    genre g
        LEFT JOIN genre_artist AS ga 
            ON g.id = ga.genre_id
        LEFT JOIN artist AS a 
            ON ga.artist_id = a.id
        LEFT JOIN 
        (   SELECT  pia.artist_id,
                    (   SELECT  CAST(pia2.value as SIGNED)
                        FROM    platform_information_artist AS pia2
                        WHERE   pia2.platform_information_id = (SELECT id 
                                                                FROM platform_information 
                                                                WHERE platform = 'spotify' 
                                                                AND information = 'monthly_listeners')
                        AND     pia2.artist_id = pia.artist_id 
                                             --  ^^ Updated from `a.id` to `pia.artist_id`
                        AND     DATE(pia2.date) >= DATE(NOW()) - INTERVAL 30 DAY
                        ORDER BY pia2.date ASC
                        LIMIT 1
                    ) as month_start_value
            FROM    platform_information_artist AS pia 
            GROUP BY pia.artist_id
        ) l 
            ON a.id = l.artist_id
GROUP BY g.id
ORDER BY g.id ASC;

此版本应在大多数MySQL版本中工作。
如果你使用的是较新版本的MySql，那么你有更多的选择，它们都比上面的方法更有效。第一个是使用横向连接：

SELECT  g.name AS genre, 
        SUM(l.month_start_value) AS SumStartValue, 
        GROUP_CONCAT(a.id) AS ArtistIDs, 
        GROUP_CONCAT(l.month_start_value) AS MonthStartValues
FROM    genre g
        LEFT JOIN genre_artist AS ga 
            ON g.id = ga.genre_id
        LEFT JOIN artist AS a 
            ON ga.artist_id = a.id
        LEFT JOIN LATERAL 
        (   SELECT  CAST(pia.value as SIGNED) AS month_start_value
            FROM    platform_information_artist AS pia
                    INNER JOIN platform_information AS i
                        ON i.id = pia.platform_information_id
            WHERE   i.platform = 'spotify' 
            AND     i.information = 'monthly_listeners'
            AND     pia.artist_id = a.id
            AND     DATE(pia.date) >= DATE(NOW()) - INTERVAL 30 DAY
            ORDER BY pia.date ASC
            LIMIT 1
        ) AS l
           ON 1= 1
GROUP BY g.id
ORDER BY g.id ASC;

另一种方法是使用ROW_NUMBER()而不是限制为每个艺术家选择一个值：

SELECT  g.name AS genre, 
        SUM(l.month_start_value) AS SumStartValue, 
        GROUP_CONCAT(l.artist_id) AS ArtistIDs, 
        GROUP_CONCAT(l.month_start_value) AS MonthStartValues
FROM    genre g
        LEFT JOIN genre_artist AS ga 
            ON g.id = ga.genre_id
        LEFT JOIN artist AS a 
            ON ga.artist_id = a.id
        LEFT JOIN 
        (   SELECT  pia.artist_id,
                    CAST(pia.value as SIGNED) AS month_start_value,
                    ROW_NUMBER() OVER(PARTITION BY pia.artist_id ORDER BY pia.Date) AS RowNum
            FROM    platform_information_artist AS pia
                    INNER JOIN platform_information AS i
                        ON i.id = pia.platform_information_id
            WHERE   i.platform = 'spotify' 
            AND     i.information = 'monthly_listeners'
            AND     DATE(pia.date) >= DATE(NOW()) - INTERVAL 30 DAY
        ) AS l
            ON l.artist_id = a.id
            AND l.RowNum = 1
GROUP BY g.id
ORDER BY g.id ASC;

型

**一个