艺术家 | |
---|---|
Notorious BIG | 34 |
臭名昭著的大壮举。等等 | 42 |
2pac | 20 |
2pac壮举。Dr.Dre | 30 |
考虑到以相同字符串开头的Artist
需要分组在一起,以下方法可能有效:
Select SUBSTRING(Artist, 0,CHARINDEX(' ', Artist, 0)), SUM(Points)
From Artist
Group by SUBSTRING(Artist, 0,CHARINDEX(' ', Artist,0))
您可以在这种情况下使用substring_index。
这将获得feat.
之前的所有值,如果feat丢失,则返回整个字符串。
SELECT SUBSTRING_INDEX(artist, 'feat.', 1) as Artist,
sum(points) as Points
FROM my_table
GROUP BY SUBSTRING_INDEX(artist, 'feat.', 1);
但是,如果字符串包含空格,则应按如下方式使用TRIM,
SELECT TRIM(SUBSTRING_INDEX(artist, 'feat.', 1)) as Artist,
sum(points) as Points
FROM my_table
GROUP BY TRIM(SUBSTRING_INDEX(artist, 'feat.', 1));
结果:
Artist Points Notorious BIG 76 2pac 50
https://dbfiddle.uk/QpaO9bVZ