如何在SQL中对含feat.的艺人记录分组并汇总积分？

要实现将带feat. xxx后缀的艺人记录与对应主艺人分组汇总积分，核心是提取Artist字段中feat.之前的主艺人名称，再分组求和。由于REPLACE不支持通配符匹配，我们可以利用各数据库的字符串截取函数来实现：

方案一：MySQL/MariaDB #

使用SUBSTRING_INDEX按指定分隔符截取字符串，一步完成分组求和：

SELECT
  TRIM(SUBSTRING_INDEX(artist, ' feat.', 1)) AS artist,
  SUM(points) AS total_points
FROM rankings
GROUP BY TRIM(SUBSTRING_INDEX(artist, ' feat.', 1));

也可以先创建视图再分组：

CREATE VIEW no_feat AS
SELECT
  TRIM(SUBSTRING_INDEX(artist, ' feat.', 1)) AS artist,
  points
FROM rankings;

SELECT artist, SUM(points) AS total_points
FROM no_feat
GROUP BY artist;

方案二：PostgreSQL #

用SPLIT_PART函数拆分字符串，简洁高效：

SELECT
  TRIM(SPLIT_PART(artist, ' feat.', 1)) AS artist,
  SUM(points) AS total_points
FROM rankings
GROUP BY TRIM(SPLIT_PART(artist, ' feat.', 1));

方案三：SQL Server #

结合CHARINDEX和SUBSTRING处理字符串，兼容无feat.的记录：

SELECT
  TRIM(SUBSTRING(artist, 1, CHARINDEX(' feat.', artist + ' feat.') - 1)) AS artist,
  SUM(points) AS total_points
FROM rankings
GROUP BY TRIM(SUBSTRING(artist, 1, CHARINDEX(' feat.', artist + ' feat.') - 1));

关键说明 #

• TRIM用于移除主艺人名称前后可能存在的多余空格，保证分组准确性。
• 各方案中，若Artist字段不含feat.，函数会直接返回原名称，无需额外判断。

内容的提问来源于stack exchange，提问作者Leon Hei