°ïæдһÌõsqlÓï¾ä

ÓÐÒÔ϶þÕÅ±í£º
Õþµ³±í£ºÕþµ³ID£¬Õþµ³Ãû³Æ
ÒéÔ±±í£ºÒéÔ±ID£¬ÒéÔ±Ãû³Æ£¬Õþµ³ID
ÒªÇó²éѯËùÓеÄÕþµ³ÐÅÏ¢£¬°üº¬£ºÕþµ³Ãû³Æ£¬ÒéÔ±ÈËÊý£¬²¢°´ÒéÔ±ÈËÊýµÄ½µÐòÅÅÁУ¨²»¿ÉÒÔÓÃ×Ó²éѯ£©¡£

---

select * from (select Õþµ³ID£¬Õþµ³Ãû³Æ,(select count(*) from ÒéÔ±±í where ÒéÔ±±í.Õþµ³ID=Õþµ³±í.Õþµ³ID) as ÒéÔ±ÈËÊý from Õþµ³±í) abc order by ÒéÔ±ÈËÊý

---

select * from (select Õþµ³ID£¬Õþµ³Ãû³Æ,(select count(*) from ÒéÔ±±í where ÒéÔ±±í.Õþµ³ID=Õþµ³±í.Õþµ³ID) as ÒéÔ±ÈËÊý from Õþµ³±í) abc order by ÒéÔ±ÈËÊý DESC

---

ÒýÓÃ

select * from (select Õþµ³ID£¬Õþµ³Ãû³Æ,(select count(*) from ÒéÔ±±í where ÒéÔ±±í.Õþµ³ID=Õþµ³±í.Õþµ³ID) as ÒéÔ±ÈËÊý from Õþµ³±í) abc order by ÒéÔ±ÈËÊý DESC

Right

---

SELECT a.Õþµ³Ãû³Æ,b.iCount
from Õþµ³±í a
LEFT JOIN (SELECT count(*) AS iCount,Õþµ³ID from ÒéÔ±±í group by Õþµ³ID) b on a.Õþµ³ID = b.Õþµ³ID
ORDER BY b.iCount DESC

---

SELECT Õþµ³Ãû³Æ,count(Õþµ³ID)
from Õþµ³±í,ÒéÔ±±í
WHERE Õþµ³±í.Õþµ³ID=ÒéÔ±±í.Õþµ³ID
GROUP BY Õþµ³±í.Õþµ³Ãû³Æ

---

SELECT a.Õþµ³Ãû³Æ,b.iCount
from Õþµ³±í a
INNER JOIN (
SELECT count(1) AS Count,Õþµ³ID from ÒéÔ±±í group by Õþµ³ID
) b on a.Õþµ³ID = b.Õþµ³ID
ORDER BY b.Count DESC

---

ÒýÓÃ

SELECT Õþµ³Ãû³Æ,count(Õþµ³ID)
from Õþµ³±í,ÒéÔ±±í
WHERE Õþµ³±í.Õþµ³ID=ÒéÔ±±í.Õþµ³ID
GROUP BY Õþµ³±í.Õþµ³Ãû³Æ

ÕýÈ·£¬³ýÁË5Â¥µÄÆäËû¶¼ÓÃÁË×Ó²éѯ£¬²»·ûÂ¥Ö÷ÒªÇ