sql查询一条记录中 最高值 的列名

Question

原表  是这样的：姓名   语文   数学     外语
A      70       80      90
B      80       90      90 查询每个人  最高分数 的科目名称 和分数   最高分数相同的科目 用逗号隔开  
  经过查询之后 要演变成 如下形式：
姓名   最高分数科目名称   分数 
A         外语             90
B      数学，外语          90
  

Answer 1

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([姓名] varchar(1),[语文] int,[数学] int,[外语] int)
insert [tbl]
select 'A',70,80,90 union all
select 'B',80,90,90GO
IF OBJECT_ID('dbo.f_str')IS NOT NULL
DROP FUNCTION dbo.f_str
GO
CREATE FUNCTION dbo.f_str(@姓名 VARCHAR)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + [科目]
    FROM T 
    WHERE [姓名]=@姓名
    RETURN STUFF(@r, 1, 1, '')
ENDCREATE TABLE T
(
[姓名] VARCHAR(10),
[科目] VARCHAR(10),
[成绩] INT
)
INSERT T
select [姓名],[科目],[成绩] from (
select [姓名],'[语文]' as [科目],[语文] as [成绩] from tbl
union all
select [姓名],'[数学]',[数学] from tbl
union all
select [姓名],'[外语]',[外语] from tbl
) a 
where [成绩]=(select MAX([成绩]) from (
select [姓名],'[语文]' as [科目],[语文] as [成绩] from tbl
union all
select [姓名],'[数学]',[数学] from tbl
union all
select [姓名],'[外语]',[外语] from tbl
) b where a.姓名=b.姓名)SELECT [姓名], [科目]=dbo.f_str([姓名]),[成绩]
FROM T
GROUP BY [姓名],[成绩]
/*
姓名 科目 成绩
A [外语] 90
B [数学],[外语] 90
*/

Answer 2

DECLARE @TB TABLE([姓名] VARCHAR(1), [语文] INT, [数学] INT, [外语] INT)
INSERT @TB 
SELECT 'A', 70, 80, 90 UNION ALL 
SELECT 'B', 80, 90, 90SELECT [姓名],
STUFF(
(CASE WHEN [语文]=SC THEN ',[语文]' ELSE '' END
+CASE WHEN [数学]=SC THEN ',[数学]' ELSE '' END
+CASE WHEN [外语]=SC THEN ',[外语]' ELSE '' END), 1,1, '') COL,SC
FROM (
SELECT *,(
SELECT MAX(SC) 
FROM (
SELECT [语文] SC UNION ALL 
SELECT [数学] UNION ALL 
SELECT [外语]) T
) SC
FROM @TB
) TT

Answer 3

with a as (
select 姓名,科目='语文',分数=语文 from tb
union all
select 姓名,科目='数学',分数=数学 from tb
union all
select 姓名,科目='外语',分数=外语 from tb
),
b as (
select a.姓名,a.科目,a.分数 from 
a
where 分数 in (select max(分数)分数 from a  group by 姓名)
)
select 姓名,
   stuff((select ','+ 科目 from b where 姓名=t.姓名 for xml path('')),1,1,'') 科目,max(分数)分数
from b t 
group by 姓名