SQLÓï¾ä¾«¼ò

Çó¸ßÈË¡£
¾«¼òÒ²ºÃ¡£»»¸ö˼·ҲºÃ¡£Ö»ÒªÄܽâ¾öÎÊÌâ¡£
ÏÈллÁË¡£


±í½á¹¹ÈçÏ£º
³É¼¨±í£º
RID  ³É¼¨±àºÅ
SID  ѧÉú±àºÅ
TID  ¿¼ÊÔ±àºÅ
Class °à¼¶±àºÅ
Subject  ¿ÆÄ¿±àºÅ
Score    ·ÖÊý

°à¼¶±í£º
CID  °à¼¶±àºÅ
Name °à¼¶Ãû³Æ
Grade Ä꼶Ãû³Æ

Ä꼶¾ÍÊǸßÒ»ºÍ¸ß¶þ


SELECT [Class],[Subject],Ó¦¿¼ÈËÊý,ʵ¿¼ÈËÊý,AÀà,BÀà,CÀà,DÀà,×î¸ß·Ö,×îµÍ·Ö,°àƽ¾ù,¼¶Æ½¾ù,BÖµ,DENSE_RANK() OVER(PARTITION BY BÖµ ORDER BY Ä꼶,[Subject] ASC) as BÐò
from (
SELECT [Class],[Subject],(SELECT [Grade] from [Classes] WHERE [Name] = '¸ß¶þÒ»°à') AS Ä꼶,
(SELECT COUNT(ID) from [Student] AS A WHERE A.[Class] = [Class]) AS Ó¦¿¼ÈËÊý,
(SELECT COUNT(ID) from [Student] AS A WHERE A.[Class] = [Class] AND [Score] > 0 ) AS ʵ¿¼ÈËÊý,
(SELECT COUNT(DISTINCT  [SID]) from [Results] WHERE [Class] = '¸ß¶þÒ»°à' AND [Subject] = 'ÓïÎÄ'
AND [Score] >= (SELECT AVG([Score]) from (SELECT TOP 30 PERCENT [Score] from [Results] ORDER BY [Score] DESC) T)) AS AÀà,

(SELECT COUNT(DISTINCT  [SID]) from [Results] WHERE [Class] = '¸ß¶þÒ»°à' AND [Subject] = 'ÓïÎÄ'
AND [Score] < (SELECT AVG([Score]) from (SELECT TOP 30 PERCENT [Score] from [Results] ORDER BY [Score] DESC) T)
AND [Score] >= (SELECT AVG([Score]) from (SELECT TOP