ÇóÒ»ÌõSQL - MS-SQL Server / »ù´¡Àà

±ít
¿¨ºÅ id
ÉÏÏßʱ¼ä onlinetime
ÀëÏßʱ¼ä offlinetime

ÈçºÎ²éµ½Ê±¼äÔçÓÚÖ¸¶¨ÈÕÆÚµÄ×îºóÒ»´ÎÉÏÏßʱ¼ä¶Î
ÀýÈ磬Ҫ²é2001-5-23ÈÕËùÓÐÊÖ»ú×îºóÒ»´ÎÉÏÏßʱ¼ä
ÏëµÃµ½ÈçϽá¹û
1111 2001-5-20 2001-5-21
1166 2001-4-30 2001-5-22
4433 2001-5-1 2001-5-5  
1688 2001-1-1 NULL //Õâ¸öÊÖ»ú´Ó2001-1-1µ½ÏÖÔÚÒ»Ö±ÔÚÏߣ¬Òò´ËÀëÏßʱ¼äΪ¿Õ

---

SQL code:
select id,onlinetime,offlinetime from (
select idd=row_number()over(partition by id order by onlinetime desc),* from t )tt
where idd=1

---

ÒýÓÃ

SQL code
select id,onlinetime,offlinetime from (
select idd=row_number()over(partition by id order by onlinetime desc),* from t )tt
where idd=1

he OVER SQL construct or statement is not supported.
Ϊʲô²»Ö§³Ö£¬ÎÒµÄÊÇSQL2000

---

˵ʵ»° ²âÊÔÊý¾Ý¸øµÃ²»¹»

---

SQL code:
select id,onlinetime,offlinetime from TB t
where not exists(select 1 from TB where t.id=id and t.onlinetime<onlinetime)
and isnull(offlinetime,getdate())<='2001-5-23'

---

ÒýÓÃ

˵ʵ»° ²âÊÔÊý¾Ý¸øµÃ²»¹»

ÉÏÃæûÓиø³ö²âÊÔÊý¾Ý£¬ÎÒÖ»ÊǸø³öÁËÏëµÃµ½µÄ½á¹û

---

Òý