sql语句问题，输出每个人最高分、最低分以及对应的科目，还有每个人的平均分

像下面这样，出来的结果应该是
张三 60 90 80 最低分科目 最高分科目
李四 50 100 80 最低分科目 最高分科目

 select name,max(最低分) 最低分,max(最高分) 最高分,max(平均分) 平均分
,max(最低分科目) 最低分科目,max(最高分科目) 最高分科目
from(
select name
,case flg when 'min' then score else 0 end as 最低分
,case flg when 'max' then score else 0 end as 最高分
,case flg when 'avg' then score else 0 end as 平均分
,case flg when 'min' then course else null end as 最低分科目
,case flg when 'max' then course else null end as 最高分科目
from
(
select name,course,score,'min' as flg from userscore u,
(select name,min(score) minscore from userscore group by name) mu
where u.name = mu.name and u.score=mu.score
union 
select name,course,score,'max' as flg from userscore u,
(select name,max(score) minscore from userscore group by name) mu
where u.name = mu.name and u.score=mu.score
union
select name,null as course,avg(score) score,'avg' flg from userscore group by name
)

avg(score)

DECLARE @tab TABLE
(
s_name NVARCHAR(MAX) ,
s_subject NVARCHAR(MAX) ,
s_score INT
)
INSERT INTO @tab
( s_name, s_subject, s_score )
VALUES ( N'张三', N'语文', 80 )
INSERT INTO @tab
( s_name, s_subject, s_score )
VALUES ( N'张三', N'数学', 90 )
INSERT INTO @tab
( s_name, s_subject, s_score )
VALUES ( N'张三', N'外语', 100 )
INSERT INTO @tab
( s_name, s_subject, s_score )
VALUES ( N'李四', N'语文', 70 )
INSERT INTO @tab
( s_name, s_subject, s_score )
VALUES ( N'李四', N'数学', 90 )
INSERT INTO @tab
( s_name, s_subject, s_score )
VALUES ( N'李四', N'外语', 60 )

    --
    ;

WITH tbSource
AS ( SELECT *
FROM @tab
),
tbMaxMin
AS ( SELECT s_name ,
MIN(s_score) AS [最低分] ,
MAX(s_score) AS [最高分] ,
AVG(s_score) AS [平均分]
FROM tbSource
GROUP BY s_name
),
tbScore
AS ( SELECT s_name ,
MAX(CASE WHEN s_subject = '语文' THEN s_score
ELSE 0
END) AS '语文' ,
MAX(CASE WHEN s_subject = '数学' THEN s_score
ELSE 0
END) AS '数学' ,
MAX(CASE WHEN s_subject = '外语' THEN s_score
ELSE 0
END) AS '外语'
FROM tbsource
GROUP BY s_name
)
SELECT a.* ,
b.[最低分] ,
b.[最高分] ,
b.[平均分]
FROM tbScore a
LEFT JOIN tbMaxMin b ON a.s_name = b.s_name

                    没玩过mysql 这是sql Server的， 看你能不能改（我用到了静态行转列，当然也可以改成动态行转列）