小小的选择问题
id parentid name adddate sort
14 7 dd 2006-10-22 0
15 7 ssd 2005-9-21 1
16 6 sdf 2004-3-22 5
17 5 owe 2007-1-22 9
18 5 wee 2006-12-2 0
19 5 swe 2001-8-2 3
20 7 asd 2006-4-8 1
21 7 swe 2003-7-3 2
注:parentid和ID没有父子关系
我想实现的是每个parentid选择出来两条数据，选择的根据这两条数据是先按照sort倒序排列，再按照adddate倒序排列的两条
比如parentid为7的选择出来就是
20 7 asd 2006-4-8 1
14 7 dd 2006-10-22 0
谢谢高手帮忙拉
[解决办法]
--try
select top 2 * from tbName
where parentid=7
order by sort desc, adddate desc
[解决办法]
select * from tablename b where id in(select top 2 id from tablename a where a.parentid=b.parentid order by sort desc,adddate desc)
[解决办法]
declare @t table(id int,parentid int,name char(10),adddate char(20),sort int)
insert into @t
select 14, 7, 'dd ', '2006-10-22 ', 0 union all
select 15, 7, 'ssd ', '2005-9-21 ', 1 union all
select 20, 7, 'asd ', '2006-4-8 ', 1 union all
select 21, 7, 'swe ', '2003-7-3 ', 2
select * from @t b where id in(select top 2 id from @t a where a.parentid=b.parentid order by sort ,adddate )
result:
id parentid name adddate sort
----------- ----------- ---------- -------------------- -----------
14 7 dd 2006-10-22 0
15 7 ssd 2005-9-21 1
（所影响的行数为 2 行）
[解决办法]
create table t(id int, parentid int, name varchar(10), adddate datetime, sort int)
insert t select 14, 7, 'dd ' , '2006-10-22 ', 0
union all select 15, 7, 'ssd ', '2005-9-21 ', 1
union all select 16, 6, 'sdf ', '2004-3-22 ', 5
union all select 17, 5, 'owe ', '2007-1-22 ', 9
union all select 18, 5, 'wee ', '2006-12-2 ', 0
union all select 19, 5, 'swe ', '2001-8-2 ', 3
union all select 20, 7, 'asd ', '2006-4-8 ', 1
union all select 21, 7, 'swe ', '2003-7-3 ', 2
select No=identity(int, 1, 1), * into #T from T
order by parentid, sort desc, adddate desc
select * from #T
select * from #T as A
where (select count(*) from #T where parentid=A.parentid and No <A.No) <2
--result
No id parentid name adddate sort
----------- ----------- ----------- ---------- ------------------------------------------------------ -----------
1 17 5 owe 2007-01-22 00:00:00.000 9
2 19 5 swe 2001-08-02 00:00:00.000 3
4 16 6 sdf 2004-03-22 00:00:00.000 5
5 21 7 swe 2003-07-03 00:00:00.000 2
6 20 7 asd 2006-04-08 00:00:00.000 1
(5 row(s) affected)
