请教一个问题
有两个表,一个档案表设为a,一个到货表设为b
a表中存放所有的商品档案,格式如下:
商品内码 商品编码 商品名称
1 1101 商品1
2 1102 商品2
3 1103 商品3
4 1104 商品4
b表中存放上表所有商品的到货时间(时间不唯一)格式如下:
商品内码 到货日期
1 2004-06-23
1 2005-03-05
1 2005-09-09
1 2006-01-01
2 2003-12-30
2 2004-09-06
2 2005-12-10
2 2006-03-10
3 2002-07-07
3 2003-01-11
3 2004-03-03
3 2005-12-30
4 2004-07-04
4 2004-12-08
4 2005-05-12
4 2006-02-01
现在想一次查询出所有商品编码的最后两次到货时间,谢谢各位高手请指点,查询后所要字段商品编码 商品名称 到货日期
有两个表,一个档案表设为a,一个到货表设为b
a表中存放所有的商品档案,格式如下:
商品内码 商品编码 商品名称
1 1101 商品1
2 1102 商品2
3 1103 商品3
4 1104 商品4
b表中存放上表所有商品的到货时间(时间不唯一)格式如下:
商品内码 到货日期
1 2004-06-23
1 2005-03-05
1 2005-09-09
1 2006-01-01
2 2003-12-30
2 2004-09-06
2 2005-12-10
2 2006-03-10
3 2002-07-07
3 2003-01-11
3 2004-03-03
3 2005-12-30
4 2004-07-04
4 2004-12-08
4 2005-05-12
4 2006-02-01
现在想一次查询出所有商品编码的最后两次到货时间,谢谢各位高手请指点,查询后所要字段商品编码 商品名称 到货日期
insert into @t select 1,'2004-06-23'
union all select 1,'2005-03-05'
union all select 1,'2005-09-09'
union all select 1,'2006-01-01'
union all select 2,'2003-12-30'
union all select 2,'2004-09-06'
union all select 2,'2005-12-10'
union all select 2,'2006-03-10'
union all select 3,'2002-07-07'
union all select 3,'2003-01-11'
union all select 3,'2004-03-03'
union all select 3,'2005-12-30'
union all select 4,'2004-07-04'
union all select 4,'2004-12-08'
union all select 4,'2005-05-12'
union all select 4,'2006-02-01'declare @a table(商品内码 int,商品编码 varchar(10),商品名称 varchar(10))
insert into @a select 1,'1101','商品1'
union all select 2,'1102','商品2'
union all select 3,'1103','商品3'
union all select 4,'1104','商品4' declare @id int
set @id=0
declare cur_1 cursor for
select 商品内码 from @acreate table #
(
商品内码 int,
到货日期 varchar(10)
)open cur_1
fetch next from cur_1 into @id
while(@@FETCH_STATUS=0)
begin
insert into # select top 2 商品内码,到货日期 from @t where 商品内码=@id order by 到货日期 desc
fetch next from cur_1 into @id
endselect a.商品编码,a.商品名称,b.到货日期 from @a a,# b where a.商品内码=b.商品内码drop table #
close cur_1
deallocate cur_1
group by 商品名称,商品编码
union
select 商品编码,商品名称,max(到货日期) from a表,b表
where a表.商品内码 = b表.商品内码
and b表.到货日期 not in (select max(到货日期) from b表 group by 商品内码)
group by 商品名称,商品编码
create table b(商品内码 char(3),到货日期 char(10))
insert into a select '1','1101','商品1'
union
select '2', '1102', '商品2'
union
select '3', '1103', '商品3'
union
select '4', '1104', '商品4'
select * from a
insert into b select '1', '2004-06-23'
union
select '1', '2005-03-05'
union
select '1', '2005-09-09'
union
select '1', '2006-01-01'
union
select '2', '2003-12-30'
union
select '2', '2004-09-06'
union
select '2', '2005-12-10'
union
select '2', '2006-03-10'
union
select '3', '2002-07-07'
union
select '3', '2003-01-11'
union
select '3', '2004-03-03'
union
select '3', '2005-12-30'
union
select '4', '2004-07-04'
union
select '4', '2004-12-08'
union
select '4', '2005-05-12'
union
select '4', '2006-02-01'
select * from b
select a.商品内码,a.商品编码,a.商品名称,b.到货日期 from a,b where a.商品内码=b.商品内码
and b.到货日期 in (select top 2 b.到货日期 from b where b.商品内码=a.商品内码 order by b.到货日期 desc)
drop table a
drop table b
结果:
----------------------
商品内码 商品编码 商品名称
---- ---- --------
1 1101 商品1
2 1102 商品2
3 1103 商品3
4 1104 商品4 (所影响的行数为 4 行)
(所影响的行数为 16 行)商品内码 到货日期
---- ----------
1 2004-06-23
1 2005-03-05
1 2005-09-09
1 2006-01-01
2 2003-12-30
2 2004-09-06
2 2005-12-10
2 2006-03-10
3 2002-07-07
3 2003-01-11
3 2004-03-03
3 2005-12-30
4 2004-07-04
4 2004-12-08
4 2005-05-12
4 2006-02-01(所影响的行数为 16 行)商品内码 商品编码 商品名称 到货日期
---- ---- -------- ----------
1 1101 商品1 2005-09-09
1 1101 商品1 2006-01-01
2 1102 商品2 2005-12-10
2 1102 商品2 2006-03-10
3 1103 商品3 2004-03-03
3 1103 商品3 2005-12-30
4 1104 商品4 2005-05-12
4 1104 商品4 2006-02-01(所影响的行数为 8 行)
create table AA(商品内码 int, 商品编码 int, 商品名称 varchar(10))
insert into AA select 1 ,1101, '商品1'
union select 2 ,1102, '商品2'
union select 3 ,1103, '商品3'
union select 4 ,1104, '商品4'create table BB(商品内码 int, 到货日期 datetime)insert into BB values(1 , '2004-06-23')
insert into BB values( 1 ,'2005-03-05')
insert into BB values( 1 ,'2005-09-09')
insert into BB values( 1 ,'2006-01-01')
insert into BB values( 2 ,'2003-12-30')
insert into BB values( 2 ,'2004-09-06')
insert into BB values( 2 ,'2005-12-10')
insert into BB values( 2 ,'2006-03-10')
insert into BB values( 3 ,'2002-07-07')
insert into BB values( 3 ,'2003-01-11')
insert into BB values( 3 ,'2004-03-03')
insert into BB values( 3 ,'2005-12-30')
insert into BB values(4 ,'2004-07-04')
insert into BB values(4 ,'2004-12-08')
insert into BB values( 4 ,'2005-05-12')
insert into BB values( 4 , '2006-02-01')
select * from AA
select * from BB
create view 到货表
as
select a.商品编码,a.商品名称,b.到货日期
from AA a,BB b
where a.商品内码=b.商品内码select 商品编码,商品名称,max(到货日期) from 到货表 group by 商品编码,商品名称
union
(
select 商品编码,商品名称,max(到货日期) from 到货表 where 到货日期 not in(select max(到货日期) from 到货表 group by 商品编码,商品名称)
group by 商品编码,商品名称
)
insert into AA select 1 ,1101, '商品1'
union select 2 ,1102, '商品2'
union select 3 ,1103, '商品3'
union select 4 ,1104, '商品4'create table BB(商品内码 int, 到货日期 datetime)insert into BB values(1 , '2004-06-23')
insert into BB values( 1 ,'2005-03-05')
insert into BB values( 1 ,'2005-09-09')
insert into BB values( 1 ,'2006-01-01')
insert into BB values( 2 ,'2003-12-30')
insert into BB values( 2 ,'2004-09-06')
insert into BB values( 2 ,'2005-12-10')
insert into BB values( 2 ,'2006-03-10')
insert into BB values( 3 ,'2002-07-07')
insert into BB values( 3 ,'2003-01-11')
insert into BB values( 3 ,'2004-03-03')
insert into BB values( 3 ,'2005-12-30')
insert into BB values(4 ,'2004-07-04')
insert into BB values(4 ,'2004-12-08')
insert into BB values( 4 ,'2005-05-12')
insert into BB values( 4 , '2006-02-01')
insert into BB values( 4 , '2005-09-09')
select * from AA
select * from BBcreate view 到货表
as
select a.商品编码,a.商品名称,b.到货日期
from AA a,BB b
where a.商品内码=b.商品内码select *
from 到货表 a
where a.到货日期 in (select top 2 到货日期 from 到货表 b where a.商品编码=b.商品编码 order by 到货日期 desc)