select top 1 * from (select top 3 * from orders where productID=3) order by id desc我这样写并未得到我想要的结果,请大家赐教!
试试。select top 1 t.* from (select top 3 * from orders where productID=3)t order by t.id desc
不行,我试了,第一张图是select top 3 * from orders where productID=3执行的结果,我想在这个结果里取最后一条记录,也就是第二张图所显示的结果。
额 没SQL 闷 不然可以多试试 高手都睡了 呵呵
select top 1 * from orders where id in (select top 3 id from orders where ProductID =3)
少了个Order byselect top 1 * from orders where id in (select top 3 id from orders where ProductID =3 order by id desc)
declare @orders table (id int,userId int,productId int,quantity int,orderDate datetime) insert into @orders select 1,2,3,20,'2010-10-08' union all select 3,1,3,10,'2010-10-08' union all select 4,3,3,5,'2010-10-08'select top 1 * from ( select top 3 * from @orders where productID=3 ) aa order by aa.id desc/* id userId productId quantity orderDate ----------- ----------- ----------- ----------- ----------------------- 4 3 3 5 2010-10-08 00:00:00.000 */
select a.* from (select top 3 * from orders where productID=3) a left join (select top 2 id from orders where productID=3) b on a.id=b.id where b.id is null
还是不行,第一张图是select top 3 * from orders where productID=3执行的结果,原始完整的数据如下图:
--这个是有序的,#8是无序的,我觉得#8符合要求,因为你的 top 3 没有 order by select top 1 * from (select top 3 * from orders where productID=3 order by id) t order by id desc
use tempdb; go set nocount on; declare @orders table (id int,userId int,productId int,quantity int,orderDate datetime) insert into @orders select 1,2,3,20,'2010-10-08' union all select 3,1,3,10,'2010-10-08' union all select 4,3,3,5,'2010-10-08';select top(1)* from (select top(3)*, ROW_NUMBER()over(order by (select 1))as row from @orders)as t order by row desc; /** id userId productId quantity orderDate row ----------- ----------- ----------- ----------- ----------------------- ------- 4 3 3 5 2010-10-08 00:00:00.000 3**/
select * from (select top 3 * from test where pid is null order by id) c where not exists(select 1 from (select top 3 * from test where pid is null order by id) d where d.id > c.id )
select * from (select top 3 * from orders where productId = 3 order by id) c where not exists(select 1 from (select top 3 * from orders where productId = 3 order by id) d where d.id > c.id )
select top 3 * into #tb from orders where productID=3select * from #tb a where not exists (select * from #tb b where b.[id] > a.[id])
select top 1 * from b order by id desc
select top 1 * from b where orderdate=(select min(orderdate) from b) order by id desc
--其实有个看起来很明白的语句 select top 3 * from orders except select top 2 * from orders --可以看做是前面查询结果减去后面查询结果
select top 1 * from (select top 3 * from orders where productID=3) order by id desc我这样写并未得到我想要的结果,请大家赐教!
order by t.id desc
高手都睡了 呵呵
select top 1 * from orders where id in (select top 3 id from orders where ProductID =3)
declare @orders table (id int,userId int,productId int,quantity int,orderDate datetime)
insert into @orders
select 1,2,3,20,'2010-10-08' union all
select 3,1,3,10,'2010-10-08' union all
select 4,3,3,5,'2010-10-08'select top 1 * from
(
select top 3 * from @orders where productID=3
) aa
order by aa.id desc/*
id userId productId quantity orderDate
----------- ----------- ----------- ----------- -----------------------
4 3 3 5 2010-10-08 00:00:00.000
*/
(select top 3 * from orders where productID=3) a
left join
(select top 2 id from orders where productID=3) b
on a.id=b.id
where b.id is null
select top 1 * from
(select top 3 * from orders where productID=3 order by id) t
order by id desc
use tempdb;
go
set nocount on;
declare @orders table (id int,userId int,productId int,quantity int,orderDate datetime)
insert into @orders
select 1,2,3,20,'2010-10-08' union all
select 3,1,3,10,'2010-10-08' union all
select 4,3,3,5,'2010-10-08';select top(1)*
from (select top(3)*,
ROW_NUMBER()over(order by (select 1))as row
from @orders)as t
order by row desc;
/**
id userId productId quantity orderDate row
----------- ----------- ----------- ----------- ----------------------- -------
4 3 3 5 2010-10-08 00:00:00.000 3**/
select * from (select top 3 * from test where pid is null order by id) c where not exists(select 1 from (select top 3 * from test where pid is null order by id) d where d.id > c.id )
select * from (select top 3 * from orders where productId = 3 order by id) c where not exists(select 1 from (select top 3 * from orders where productId = 3 order by id) d where d.id > c.id )
--其实有个看起来很明白的语句
select top 3 * from orders except select top 2 * from orders
--可以看做是前面查询结果减去后面查询结果