ÇóÒ»ÌõSQLÓï¾ä

ÃèÊöÒ»ÏÂÐèÇ󣺾ÍÊDzéÕÒÓû§·ÃÎÊ×î¶àµÄ³ÇÊÐ

×ֶΠimei , cityid , cnt (·Ö±ð±íʾ£¬Óû§ID£¬³ÇÊÐID£¬·ÃÎÊ´ÎÊý)

¾ÙÀý 1234  0001    33
    1234  0002    32
    1222  0001    20
    1222  0003    22

ÏÔʾ½á¹û

1234 0001 33
1222 0003 22

Õâ¾äSQLÔõôд°¡

лÀ²
Oracle 10G

---

select  max(cnt) ,imei from tab_name
group by imei;

---

cityid Õâ¸ö¹æÂÉÊÇʲô£¿

---

ÒýÓÃ

cityid Õâ¸ö¹æÂÉÊÇʲô£¿

ûÓйæÂÉ ¾ÍÊÇÓû§·ÃÎʵijÇÊÐ ¿ÉÒÔ½ñÌìÔÚ±±¾©£¬Ã÷ÌìÔÙÉϺ££¬Õâ¸öcityID¾ÍÓÐÒ»¸ö ±±¾©µÄID ºÍÉϺ£µÄID

---

ÒýÓÃ

select  max(cnt) ,imei from tab_name
group by imei;

Ҫͬʱ²é´¦imeiºÍcityid

---

û¹æÂÉÔõô²é¡£Äã1234  ÓÐ2¸ö³ÇÊС£µ«Äã½á¹ûÖ»ÓÐÒ»¸ö¡£ÄÇÎÒµ½µ×Ҫȡ£¬0001    »¹ÊÇ0002£¬»òÕß2¸ö¶¼Òª

---

ÒýÓÃ

û¹æÂÉÔõô²é¡£Äã1234   ÓÐ2¸ö³ÇÊС£µ«Äã½á¹ûÖ»ÓÐÒ»¸ö¡£ÄÇÎÒµ½µ×Ҫȡ£¬0001    »¹ÊÇ0002£¬»òÕß2¸ö¶¼Òª

Èç¹û0001·ÃÎÊ10´Î
0002·ÃÎÊ9´Î
¾ÍÈ¡0001³öÀ´°¡
Óû§·ÃÎÊ´ÎÊý×î¶àµÄ³ÇÊÐ

---

select imei,cityid,cnt  from T1 a
where cnt=(select max(cnt) from T1 b where a.imei=b.imei and a.cityid=b.cityid)

---

select&