你可以尝试使用 OPENJSON 和 CROSS APPLY 来实现它。SELECT col1, UserID FROM T t1 CROSS APPLY OPENJSON(t1.Roles) WITH ( col1 varchar(50) N'$' ) AS a sqlfiddle 的意思是程序相关的。
如果您无法使用OPENJSON,这里有一个替代方案。Select aRole, COUNT(*) as cnt From ( Select a.ID ,b.Items as aRole From (Select * ,replace(replace(replace(Roles,'[',''),']',''),'"','') as Rolesx From JSONgroup) a Cross Apply dbo.Split(a.Rolesx, ',') b ) c group by aRole