求一sql语句

[dayusky]

产品记录里有一个字段叫做"ptype"，"ptype"表示该产品的类型，可能值为"A类"、"B类"、"C类"……，求一sql语句要求选出类型为"A类"和"B类"各自分别最近更新的10条产品记录，更新时间字段为"logintime"。

多谢

[xuam]

select top 10 * from table where ptype='A类' order by logintime desc
union all
select top 10 * from table where ptype='B类' order by logintime desc

[dayusky]

有更简单的写法吗？如果要选出ABCDEFG……十几类的产品，不是要重复写十几遍select top 10 * from table where ptype……吗？

[jones129]

Create Table #Temp1
(ptype varchar(10),
pname varchar(10),
logintime datetime)

Insert into #temp1(ptype,pname,logintime)
Select 'A类','1','2005-03-01'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','3','2005-03-02'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','4','2005-03-03'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','5','2005-03-04'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','7','2005-03-05'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','2','2005-03-06'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','6','2005-03-07'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','8','2005-03-12'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','9','2005-03-09'
Insert into #temp1(ptype,pname,logintime)
Select 'A类','1','2005-03-10'


Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-01'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-02'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-03'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-04'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-05'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-06'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-07'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-08'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-09'
Insert into #temp1(ptype,pname,logintime)
Select 'B类','1','2005-03-11'


Select Top 10 logintime,Max([A类]),Max([B类])
From (Select case When ptype='A类' then pname else '' end 'A类',
case When ptype='B类' then pname else '' end 'B类',
logintime
From #Temp1
) A
group By logintime
Order by logintime desc

Drop Table #Temp1

[dayusky]

哇塞，这个有创意，不过好象更复杂了。。。

[xluzhong]

alter table tablename add sid int identity(1,1)

select *
from tablename a
where sid in(select top 10 sid from tablename where ptype=a.ptype order by logintime desc)

alter table tablename drop column sid