单号 品号 时间 单价
1406005 A 20140601 0.2
1406006 B 20140602 0.4
1406007 C 20140603 0.8
1406008 D 20140604 0.1
1406009 E 20140605 0.7
1406010 A 20140606 0.6
1406011 B 20140607 0.9
1406012 C 20140608 0.5
1406013 A 20140609 0.4
现在要求查询出来的是在单价旁边加多上次的进货单价用意就是最后一次单价跟上一次的对比，要求结果显示如下内容
单号 品号 时间 单价 上次单价
1406005 A 20140601 0.2 0
1406006 B 20140602 0.4 0
1406007 C 20140603 0.8 0
1406008 D 20140604 0.1 0
1406009 E 20140605 0.7 0
1406010 A 20140606 0.6 0.2
1406011 B 20140607 0.9 0.4
1406012 C 20140608 0.5 0.8
1406013 A 20140609 0.4 0.6
4 个解决方案
#1
发出来格式变了，
#2
我自己编辑不到原来的贴子。
#3
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] ([单号] int,[品号] varchar(1),[时间] datetime,[单价] numeric(2,1))
insert into [ta]
select 1406005,'A','20140601',0.2 union all
select 1406006,'B','20140602',0.4 union all
select 1406007,'C','20140603',0.8 union all
select 1406008,'D','20140604',0.1 union all
select 1406009,'E','20140605',0.7 union all
select 1406010,'A','20140606',0.6union all
select 1406011,'B','20140607',0.9 union all
select 1406012,'C','20140608',0.5 union all
select 1406013,'A','20140609',0.4
;with wang as (
select ID=ROW_NUMBER() over(PARTITION by 品号 order by 时间),* from [ta]
)
select s.*,t.单价
from wang s left join wang t on s.品号=t.品号 and s.ID=t.ID+1
order by 单号
1 1406005 A 2014-06-01 00:00:00.000 0.2 NULL
1 1406006 B 2014-06-02 00:00:00.000 0.4 NULL
1 1406007 C 2014-06-03 00:00:00.000 0.8 NULL
1 1406008 D 2014-06-04 00:00:00.000 0.1 NULL
1 1406009 E 2014-06-05 00:00:00.000 0.7 NULL
2 1406010 A 2014-06-06 00:00:00.000 0.6 0.2
2 1406011 B 2014-06-07 00:00:00.000 0.9 0.4
2 1406012 C 2014-06-08 00:00:00.000 0.5 0.8
3 1406013 A 2014-06-09 00:00:00.000 0.4 0.6
#4
改一下
select s.*,isnull(t.单价,0)
from wang s left join wang t on s.品号=t.品号 and s.ID=t.ID+1
order by 单号
#1
发出来格式变了，
#2
我自己编辑不到原来的贴子。
#3
--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] ([单号] int,[品号] varchar(1),[时间] datetime,[单价] numeric(2,1))
insert into [ta]
select 1406005,'A','20140601',0.2 union all
select 1406006,'B','20140602',0.4 union all
select 1406007,'C','20140603',0.8 union all
select 1406008,'D','20140604',0.1 union all
select 1406009,'E','20140605',0.7 union all
select 1406010,'A','20140606',0.6union all
select 1406011,'B','20140607',0.9 union all
select 1406012,'C','20140608',0.5 union all
select 1406013,'A','20140609',0.4
;with wang as (
select ID=ROW_NUMBER() over(PARTITION by 品号 order by 时间),* from [ta]
)
select s.*,t.单价
from wang s left join wang t on s.品号=t.品号 and s.ID=t.ID+1
order by 单号
1 1406005 A 2014-06-01 00:00:00.000 0.2 NULL
1 1406006 B 2014-06-02 00:00:00.000 0.4 NULL
1 1406007 C 2014-06-03 00:00:00.000 0.8 NULL
1 1406008 D 2014-06-04 00:00:00.000 0.1 NULL
1 1406009 E 2014-06-05 00:00:00.000 0.7 NULL
2 1406010 A 2014-06-06 00:00:00.000 0.6 0.2
2 1406011 B 2014-06-07 00:00:00.000 0.9 0.4
2 1406012 C 2014-06-08 00:00:00.000 0.5 0.8
3 1406013 A 2014-06-09 00:00:00.000 0.4 0.6
#4
改一下
select s.*,isnull(t.单价,0)
from wang s left join wang t on s.品号=t.品号 and s.ID=t.ID+1
order by 单号