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 谢谢高手帮忙拉
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 谢谢高手帮忙拉
where parentid=7
order by sort desc, adddate desc
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 行)
中where a.parentid=b.parentid 的意思和作用?这样是不是效率高一些?
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', 1union 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', 3union all select 20, 7, 'asd', '2006-4-8', 1
union all select 21, 7, 'swe', '2003-7-3', 2select No=identity(int, 1, 1), * into #T from T
order by parentid, sort desc, adddate descselect * from #Tselect * 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)