27,579
社区成员
发帖
与我相关
我的任务
分享
If not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([pono] nvarchar(10),[datex] Datetime)
Insert a
Select '0809010001','2008-09-01' union all
Select '0901010001','2009-01-01' union all
Select '0902010001','2009-02-01'
Go
--Select * from a
--> 生成测试数据表:b
If not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([pono] nvarchar(10),[code] nvarchar(4),[price] decimal(18,1))
Insert b
Select '0809010001','a123',1.5 union all
Select '0809010001','b123',5 union all
Select '0809010001','c123',4 union all
Select '0901010001','a123',1.5 union all
Select '0901010001','b123',3 union all
Select '0901010001','c123',3.5 union all
Select '0902010001','a123',1.2 union all
Select '0902010001','b123',2.5 union all
Select '0902010001','c123',3.5
Go
select identity(int,1,1) as id, a.datex,b.* into # from a join b on a.pono=b.pono order by code ,price
select * from # t where ID in(select top 2 ID from # where t.code=code order by datex desc )
id datex pono code price
----------- ----------------------- ---------- ---- ---------------------------------------
1 2009-02-01 00:00:00.000 0902010001 a123 1.2
3 2009-01-01 00:00:00.000 0901010001 a123 1.5
4 2009-02-01 00:00:00.000 0902010001 b123 2.5
5 2009-01-01 00:00:00.000 0901010001 b123 3.0
7 2009-01-01 00:00:00.000 0901010001 c123 3.5
8 2009-02-01 00:00:00.000 0902010001 c123 3.5
declare @a table (pono nvarchar(10),datex datetime)
insert into @a select '0809010001','2008-09-01'
union all select '0901010001','2009-01-01'
union all select '0902010001','2009-02-01'
declare @b table (pono nvarchar(10),code nvarchar(10),price money)
insert into @b select '0809010001','a123',1.5
union all select '0809010001','b123',5
union all select '0809010001','c123',4
union all select '0901010001','a123',1.5
union all select '0901010001','b123',3
union all select '0901010001','c123',3.5
union all select '0902010001','a123',1.2
union all select '0902010001','b123',2.5
;with China as
(
select a.datex,b.* from @a a join @b b on a.pono=b.pono
)
select * from China a where datex in (select top 2 datex from China where a.code=code order by datex desc )
order by price
---
datex pono code price
----------------------- ---------- ---------- ---------------------
2009-02-01 00:00:00.000 0902010001 a123 1.20
2009-01-01 00:00:00.000 0901010001 a123 1.50
2009-02-01 00:00:00.000 0902010001 b123 2.50
2009-01-01 00:00:00.000 0901010001 b123 3.00
2009-01-01 00:00:00.000 0901010001 c123 3.50
2008-09-01 00:00:00.000 0809010001 c123 4.00
(6 行受影响)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-10 15:55:44
---------------------------------
--> 生成测试数据表:a
If not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([pono] nvarchar(10),[datex] Datetime)
Insert a
Select '0809010001','2008-09-01' union all
Select '0901010001','2009-01-01' union all
Select '0902010001','2009-02-01'
Go
--Select * from a
--> 生成测试数据表:b
If not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([pono] nvarchar(10),[code] nvarchar(4),[price] decimal(18,1))
Insert b
Select '0809010001','a123',1.5 union all
Select '0809010001','b123',5 union all
Select '0809010001','c123',4 union all
Select '0901010001','a123',1.5 union all
Select '0901010001','b123',3 union all
Select '0901010001','c123',3.5 union all
Select '0902010001','a123',1.2 union all
Select '0902010001','b123',2.5 union all
Select '0902010001','c123',3.5
Go
--Select * from b
-->SQL查询如下:
select pono,datex,code,price
from (
select a.pono,a.datex,b.code,b.price,
rn=row_number() over(partition by code order by datex desc)
from a
join (select * from b t where not exists(select 1 from b where code=t.code and price=t.price and [pono]>t.[pono])) b
on a.pono=b.pono
) as t
where rn<=2
/*
pono datex code price
---------- ----------------------- ---- ---------------------------------------
0902010001 2009-02-01 00:00:00.000 a123 1.2
0901010001 2009-01-01 00:00:00.000 a123 1.5
0902010001 2009-02-01 00:00:00.000 b123 2.5
0901010001 2009-01-01 00:00:00.000 b123 3.0
0902010001 2009-02-01 00:00:00.000 c123 3.5
0809010001 2008-09-01 00:00:00.000 c123 4.0
(6 行受影响)
*/
修改一下.
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-10 15:55:44
---------------------------------
--> 生成测试数据表:a
If not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([pono] nvarchar(10),[datex] Datetime)
Insert a
Select '0809010001','2008-09-01' union all
Select '0901010001','2009-01-01' union all
Select '0902010001','2009-02-01'
Go
--Select * from a
--> 生成测试数据表:b
If not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([pono] nvarchar(10),[code] nvarchar(4),[price] decimal(18,1))
Insert b
Select '0809010001','a123',1.5 union all
Select '0809010001','b123',5 union all
Select '0809010001','c123',4 union all
Select '0901010001','a123',1.5 union all
Select '0901010001','b123',3 union all
Select '0901010001','c123',3.5 union all
Select '0902010001','a123',1.2 union all
Select '0902010001','b123',2.5 union all
Select '0902010001','c123',3.5
Go
--Select * from b
-->SQL查询如下:
select pono,datex,code,price
from (
select a.pono,a.datex,b.code,b.price,
rn=row_number() over(partition by code order by datex desc)
from a
join b
on a.pono=b.pono
) as t
where rn<=2
/*
pono datex code price
---------- ----------------------- ---- ---------------------------------------
0902010001 2009-02-01 00:00:00.000 a123 1.2
0901010001 2009-01-01 00:00:00.000 a123 1.5
0902010001 2009-02-01 00:00:00.000 b123 2.5
0901010001 2009-01-01 00:00:00.000 b123 3.0
0902010001 2009-02-01 00:00:00.000 c123 3.5
0901010001 2009-01-01 00:00:00.000 c123 3.5
(6 行受影响)
*/