SQL²»Ã÷°× - MS-SQL Server / Ó¦ÓÃʵÀý

²éѯѧÉúƽ¾ù³É¼¨¼°ÆäÃû´Î 
  SELECT 1+(SELECT COUNT( distinct ƽ¾ù³É¼¨) 
  from (SELECT S#,AVG(score) AS ƽ¾ù³É¼¨ 
  from SC 
  GROUP BY S# 
  ) AS T1 
  WHERE ƽ¾ù³É¼¨ > T2.ƽ¾ù³É¼¨) as Ãû´Î, 
  S# as ѧÉúѧºÅ,ƽ¾ù³É¼¨ 
  from (SELECT S#,AVG(score) ƽ¾ù³É¼¨ 
  from SC 
  GROUP BY S# 
  ) AS T2 
  ORDER BY ƽ¾ù³É¼¨ desc; 
Óï¾ä²»ÊǺÜÀí½â°¡¡£°ïÏÂÎÒ°É

---

ÓïÑÔ²»Ì«ºÃ±í´ï£¬°ï¶¥°É

---

SQL code:

--¼ÆËã¾ù·Ö
SELECT S#,AVG(score) ƽ¾ù³É¼¨
from SC
GROUP BY S#

--°´ÕÕѧºÅ¼ÆËãƽ¾ù·Ö£¬²¢È¡µÃ¸ßÓÚ¾ù·ÖµÄͬѧµÄ¸öÊý
SELECT COUNT( distinct ƽ¾ù³É¼¨)
from (SELECT S#,AVG(score) AS ƽ¾ù³É¼¨
from SC
GROUP BY S#
) AS T1
WHERE ƽ¾ù³É¼¨ > T2.ƽ¾ù³É¼¨

--²»¹ý²»ÖªµÀΪºÎ+1£¿

---

SQL code:

--Ŷ£¬ÕâÑùһĿÁËÈ»ÁË£¬ÆäʵÄã¾ÍÊÇÔÚ¸øѧÉúÅÅÃû´Î¡£
create view view_acg_scor as select s# ѧºÅ,avg(score) ƽ¾ù³É¼¨ from SC group by s#
SELECT 1+(SELECT COUNT( distinct ƽ¾ù³É¼¨)
from view_acg_score AS T1