Salut j'ai une variable de table et je veux find chaque nom d'étudiant avec le sujet maximum marqué.
declare @test1 table (name varchar(50), english int, math int, science int) insert into @test1(name, english, math, science) select 'A', 50, 90, 70 union all select 'B', 60, 80, 65 union all select 'C' , 80,65, 70 union all select 'D', 70, 75, 89
S'il vous plaît, si quelqu'un peut m'aider, ce serait appréciable.
Voici un truc pour le faire
SELECT TOP 1 WITH ties NAME, sub_name, mark FROM @test1 CROSS apply (VALUES(english,'english'), (math,'math'), (science,'science'))tc(mark, sub_name) ORDER BY Row_number()OVER(partition BY NAME ORDER BY mark DESC)
ou utilisez la déclaration CASE
laid mais cela fonctionnera mieux, puis Cross Apply
SELECT NAME, sub_name, CASE sub_name WHEN 'english' THEN english WHEN 'math' THEN math ELSE science END mark FROM (SELECT *, CASE WHEN english > math AND english > science THEN 'english' WHEN english < math AND math > science THEN 'math' ELSE 'science' END AS sub_name FROM @test1) a
Puisque vous ne voulez pas appliquer, peut-être UNPIVOT
Declare @test1 table (name varchar(50), english int, math int, science int) insert into @test1(name, english, math, science) select 'A', 50, 90, 70 union all select 'B', 60, 80, 65 union all select 'C' , 80,65, 70 union all select 'D', 70, 75, 89 Select Top 1 with Ties Name,SubjectName,Score From @test1 A unpivot ( Score for SubjectName in ( english, math, science) ) u Order by Row_number() Over (Partition By Name Order By Score Desc)
Retruns
Name SubjectName Score A math 90 B math 80 C english 80 D science 89
La Cross Apply
Prdp serait mon premier choix (+1). MAIS, si vous ne voulez pas spécifier tous les champs, voici une approche légèrement plus dynamic via XML
et une application CROSS APPLY
(ou deux)
Declare @test1 table (name varchar(50), english int, math int, science int) insert into @test1(name, english, math, science) select 'A', 50, 90, 70 union all select 'B', 60, 80, 65 union all select 'C' , 80,65, 70 union all select 'D', 70, 75, 89 Select Top 1 with Ties A.Name ,C.* From @test1 A Cross Apply (Select XMLData= cast((Select A.* for XML Raw) as xml)) B Cross Apply ( Select Item = attr.value('local-name(.)','varchar(100)') ,Value = attr.value('.','varchar(max)') From B.XMLData.nodes('/row') as A(r) Cross Apply Arnodes('./@*') AS B(attr) Where attr.value('local-name(.)','varchar(100)') not in ('name','otherfields') --<< field names are case sensitive ) C Order by Row_number() Over (Partition By Name Order By Value Desc)
Résultats
Name Item Value A math 90 B math 80 C english 80 D science 89