我现在有张表A如下
category prodID date
1 1 20050608 ---
1 2 20050607 ---
1 3 20050604
2 1 20050601
2 1 20050603 ---
2 1 20050602
2 1 20050604 --
3 5 20050604 ---
想要得到各个category 的最新的两条数据.
比如上表的数据中有1,和2俩种category
想要的结果是
1 1 20050608
1 2 20050607
2 1 20050604
2 1 20050603
3 5 20050604
请问一条语句怎么写..能实现嘛>?
category prodID date
1 1 20050608 ---
1 2 20050607 ---
1 3 20050604
2 1 20050601
2 1 20050603 ---
2 1 20050602
2 1 20050604 --
3 5 20050604 ---
想要得到各个category 的最新的两条数据.
比如上表的数据中有1,和2俩种category
想要的结果是
1 1 20050608
1 2 20050607
2 1 20050604
2 1 20050603
3 5 20050604
请问一条语句怎么写..能实现嘛>?
比如上表的数据中有1,和2俩种category
----->比如上表的数据中有1,和2,3 三种category
from tablename a
where 2>(select count(1) from tablename where a.category=category and date>a.date)
GO
SET ANSI_NULLS ON
GO--exec test_dt
ALTER PROCEDURE test_dtAS
set nocount on
begin transaction declare @orders int
declare @matsyscode varchar(20)
declare @digit int
declare @docdate datetime
declare @matsyscode1 varchar(20)declare cur2 cursor for
select matsyscode,digit,docdate from kktt group by matsyscode,digit,docdate order by matsyscode desc,docdate desc
select matsyscode,digit,docdate into #test_dt from kktt where matsyscode=''
set @matsyscode1=''
open cur2
fetch next from cur2 into @matsyscode,@digit,@docdate
while @@fetch_status=0
Begin
if @matsyscode1<>@matsyscode
begin
set @matsyscode1=@matsyscode
set @orders=0
end
if @matsyscode1=@matsyscode and @orders<2
Begin
insert into #test_dt values(@matsyscode,@digit,@docdate)
set @orders=@orders+1
End
fetch next from cur2 into @matsyscode,@digit,@docdate
End
close cur2
DEALLOCATE cur2select * from #test_dt order by matsyscode asc,docdate descCommit Transaction
set nocount offGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
(Select top 2 category From tablename b where b.category=a.category)
n<(select count(1) from ...
或者
id in(select top n id from ....大多都这两种写法.
into c from aselect * from c where as pm>=2试试看看