1
votes

obtenir le GPA le plus élevé obtenu par les étudiants et étudiantes de chaque école

J'ai des tableaux

 WITH cte
    AS (
        SELECT D.SCHOOL_NAME
            ,A.SYMBOL_NO
            ,B.STUDENT_NAME         
            ,B.GENDER
            ,A.AVG_GPA
        FROM TBL_STD_MARKS A
        INNER JOIN TBL_STD_INFO B ON A.SYMBOL_NO = B.SYMBOL_NO
        INNER JOIN TBL_SCHOOL D ON A.SCHOOL_ID = D.SCHOOL_ID
        )
    SELECT SCHOOL_NAME
            ,SYMBOL_NO
            ,STUDENT_NAME       
        ,MAX(DISTINCT CASE 
                WHEN GENDER = 'M'
                    THEN AVG_GPA
                END) AS MALE_GPA
        ,MAX(DISTINCT CASE 
                WHEN GENDER = 'F'
                    THEN AVG_GPA
                END) AS FEMALE_GPA
    FROM CTE
    GROUP BY SCHOOL_NAME,SYMBOL_NO
            ,STUDENT_NAME

J'essaie d'écrire une requête pour obtenir le meilleur score de AVG_GPA par les étudiants et étudiantes de chaque école. Mais ma requête donne toutes les lignes du tableau.

TBL_STD_MARKS 
SCHOOL_ID   SYMBOL_NO       AVG_GPA
1               211             3.8
1               255             3.5
1               212             2.90
2               311             3.85
2               312             3.75
3               411             2.98
3               412             3.00


TBL_STD_INFO
SCHOOL_ID   SYMBOL_NO   STUDENT_NAME    GENDER
1               211         S1              M   
1               255         S2              F
1               212         S3              M
2               311         S22             M
2               312         S23             M   
3               411         S31             M
3               412         S32             F   


TBL_SCHOOL
SCHOOL_ID       SCHOOL_NAME
1               SCHOOL11
2               SCHOOL22
3               SCHOOL33

sql sql-server

0 commentaires

6 Réponses :

0
votes

select D.SCHOOL_NAME
        ,A.SYMBOL_NO
        ,B.STUDENT_NAME         
        ,B.GENDER
        ,A.AVG_GPA
    FROM TBL_STD_MARKS A
    INNER JOIN TBL_STD_INFO B ON A.SYMBOL_NO = B.SYMBOL_NO
    INNER JOIN TBL_SCHOOL D ON A.SCHOOL_ID = D.SCHOOL_ID
where B.GENDER='M' OR B.GENDER='F' and A.AVG_GPA=(select max(AVG_GPA) from TBL_STD_MARKS)

0 commentaires

0
votes

Essayez ceci

SELECT tbs.SCHOOL_ID,
CASE WHEN GENDER = 'F' THEN
      MAX(AVG_GPA) END AS HighestFemaleGPA,
CASE WHEN GENDER = 'M' THEN
      MAX(AVG_GPA) END AS HighestMaleGPA
FROM @TBL_STD_MARKS stdM
INNER JOIN @TBL_SCHOOL tbs
ON  tbs.SCHOOL_ID = stdM.SCHOOL_ID
INNER JOIN @TBL_STD_INFO stdI
ON stdI.SYMBOL_NO = stdM.SYMBOL_NO
GROUP BY tbs.SCHOOL_ID,GENDER

0 commentaires

2
votes

utilisez la fonction de fenêtre row_number ()

     with cte as (  
          SELECT D.SCHOOL_NAME
        ,A.SYMBOL_NO
        ,B.STUDENT_NAME         
        ,B.GENDER
        ,A.AVG_GPA,row_number()over(partition by D.SCHOOL_NAME, B.GENDER order by A.AVG_GPA desc) rn
    FROM TBL_STD_MARKS A
    INNER JOIN TBL_STD_INFO B ON A.SYMBOL_NO = B.SYMBOL_NO
    INNER JOIN TBL_SCHOOL D ON A.SCHOOL_ID = D.SCHOOL_ID
    ) select * from cte where rn=1

4 commentaires

Que faire si je veux commander par AVG_GPA, je veux dire que l'étudiant masculin ou féminin avec le GPA le plus élevé devrait être en haut, puis l'étudiant masculin ou féminin correspondant de la même école. J'ai essayé en rejoignant select * from cte où rn = 1 order by AVG_GPA desc mais c'est en passant commande par AVG_GPA uniquement. Veuillez aider !!!

@nischalinn je ne comprends pas ce que vous avez demandé, pouvez-vous s'il vous plaît poser une nouvelle question avec un exemple de données approprié car je pense que ma réponse est déjà résolue votre problème actuel

cette requête affiche le résultat par ordre par nom_école. mais que faire si je veux montrer l'école avec le GPA le plus élevé en plus.

utiliser order by AVG_GPA desc

0
votes

SELECT std_marks.SCHOOL_ID,school.SCHOOL_NAME, std_info.GENDER, MAX(std_marks.AVG_GPA) 
as MAX_AVG_GPA
FROM TBL_STD_MARKS std_marks
INNER JOIN TBL_STD_INFO std_info ON std_info.SYMBOL_NO = std_marks.SYMBOL_NO
INNER JOIN TBL_SCHOOL school ON school.SCHOOL_ID = std_marks.SCHOOL_ID
GROUP BY std_info.GENDER, std_marks.SCHOOL_ID, school.SCHOOL_NAME
ORDER BY std_marks.SCHOOL_ID

0 commentaires

0
votes

sélectionnez ts.School_Id, ts.School_Name, tsi.Gender, max (tsm.Avg_GPA) comme Avg_GPA à partir de Tbl_School ts join Tbl_Std_Info tsi sur ts.School_Id = tsi.School_Id joindre Tbl_Std_Marks tsm sur tsi.Symbol_No = groupe tsm.Symbol_No par ts.School_Id, ts.School_Name, tsi.Gender

0 commentaires

0
votes

Supposons que ce soit notre tableau. La requête ci-dessous vous aidera:

Select a.SchoolId, a.Gender,
  (Select max(GPA) From [Test].[guest].[School] where SchoolId = a.SchoolId and Gender = 
  a.Gender) as GPA
  from [Test].[guest].[School] a
  Group by SchoolId, Gender

0 commentaires