ÈÕÆÚÇ°Ò»ÌìµÄsqlÓï¾ä

Êý¾Ý¿â±íAAA ×Ö¶ÎÈçÏÂ
a  b    c

A 10 2009-01-01

A 11 2009-01-02

A 12 2009-01-03

B 23 2009-01-01

B 24 2009-01-02

B 25 2009-01-03

C 17 2009-01-01

C 19 2009-01-02

C 25 2009-01-03


ÒªÇóËÑË÷³ö×î´óµÄÒ»ÌìÓë×î´óµÄÒ»Ìì֮ǰһÌ죬bµÄ±ÈÖµ£¬½á¹ûÈçÏÂ

A 12/11

B 25/24

C 25/19

---

SQL code:

sql server 2005:
select *
from (select id=row_number() over(order by c desc),* from tb) K
where id<=2

---

SQL code:
select m.a , cast(m.b as varchar) + '/' + cast(n.b as varchar) from
(select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where a = t.a and c > t.c) + 1 from tb t) n
where m.a = n.a and m.px = 1 and n.px = 2

---

SQL code:
create table tb(a varchar(10), b int, c datetime)
insert into tb values('A', 10 ,'2009-01-01')
insert into tb values('A', 11 ,'2009-01-02')
insert into tb values('A', 12 ,'2009-01-03')
insert