怎么列出价格变动的记录？

[funsuzhou]

比如出货记录：

CODE APRICE FDATE
A 1 20091001
A 1 20091002
A 2 20091003
A 2 20091004
A 2 20091005
A 1 20091006
A 1.5 20091007
A 1 20091008
A 1 20091009
A 1 20091010

我希望得到：
CODE APRICE FDATE
A 1 20091001
A 2 20091003
A 1 20091006
A 1.5 20091007
A 1 20091008
即CODE相同的记录中APRICE有变动时要提出来，无变动时只取第1条记录。

[让你望见影子的墙]

with
wang as(select row=row_number() over(order by FDATE),* from tb),
wang1 as (select *,flag=case when aprice=(select aprice from wang where row=t.tow+1)
                        then 1 else 0 end)
          from wang t)

select CODE, APRICE, FDATE 
from wang1
where id=1

[让你望见影子的墙]

上面多了一个),没环境测试。
with
wang as(select row=row_number() over(order by FDATE),* from tb),
wang1 as (select *,flag=case when aprice=(select aprice from wang where row=t.tow+1)
                        then 1 else 0 end
          from wang t)

select CODE, APRICE, FDATE 
from wang1
where id=1

[sgtzzc]

[Quote=引用 1 楼 sgtzzc 的回复:]
SQL codeselect*from tb twherenotexists(select1from tbwhere code=t.codeand fdate>t.fdate)
[/Quote]
看错了，修正一下

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([CODE] varchar(1),[APRICE] numeric(2,1),[FDATE] datetime)
insert [tb]
select 'A',1,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'A',1,'20091010'
 
---查询---
select *
from tb t
where not exists(select 1 from tb where datediff(dd,fdate,t.fdate)=1 and aprice=t.aprice)


---结果---
CODE APRICE FDATE                                                  
---- ------ ------------------------------------------------------ 
A    1.0    2009-10-01 00:00:00.000
A    2.0    2009-10-03 00:00:00.000
A    1.0    2009-10-06 00:00:00.000
A    1.5    2009-10-07 00:00:00.000
A    1.0    2009-10-08 00:00:00.000

（所影响的行数为 5 行）

[华夏小卒]

--> 测试数据: @tb
declare @tb table (CODE varchar(1),APRICE numeric(2,1),FDATE datetime)
insert into @tb
select 'A',1,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'A',1,'20091010'


select top 1 * from @tb  
union all 
select  b.code,b.aprice,b.fdate 
from @tb a,@tb b
where  dateadd(day,1,a.fdate)=b.fdate and a.aprice!=b.aprice

CODE APRICE                                  FDATE
---- --------------------------------------- -----------------------
A    1.0                                     2009-10-01 00:00:00.000
A    2.0                                     2009-10-03 00:00:00.000
A    1.0                                     2009-10-06 00:00:00.000
A    1.5                                     2009-10-07 00:00:00.000
A    1.0                                     2009-10-08 00:00:00.000

(5 行受影响)

[华夏小卒]

[Quote=引用 4 楼 sgtzzc 的回复:]
引用 1 楼 sgtzzc 的回复:
SQL codeselect*from tb twherenotexists(select1from tbwhere code=t.codeand fdate>t.fdate)

看错了，修正一下
SQL code---测试数据---ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([CODE]varchar(1),[APRICE] numeric(2,1),[FDATE]datetime)insert[tb]select'A',1,'20091001'unionallselect'A',1,'20091002'unionallselect'A',2,'20091003'unionallselect'A',2,'20091004'unionallselect'A',2,'20091005'unionallselect'A',1,'20091006'unionallselect'A',1.5,'20091007'unionallselect'A',1,'20091008'unionallselect'A',1,'20091009'unionallselect'A',1,'20091010'---查询---select*from tb twherenotexists(select1from tbwheredatediff(dd,fdate,t.fdate)=1and aprice=t.aprice)---结果---CODE APRICE FDATE---- ------ ------------------------------------------------------A1.02009-10-0100:00:00.000
A2.02009-10-0300:00:00.000
A1.02009-10-0600:00:00.000
A1.52009-10-0700:00:00.000
A1.02009-10-0800:00:00.000

（所影响的行数为5 行）
[/Quote]学习

[nianran520]

--> 测试数据：@table
declare @table table([CODE] varchar(1),[APRICE] numeric(2,1),[FDATE] varchar(8))
insert @table
select 'A',1,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'A',1,'20091010'

select r.* from @table r left join @table h
on r.code = h.code
and cast(r.FDATE as datetime) = dateadd(day,1,h.FDATE)
where r.APRICE <> h.APRICE or h.APRICE is null
--结果
------------------------
A	1.0	20091001
A	2.0	20091003
A	1.0	20091006
A	1.5	20091007
A	1.0	20091008

[funsuzhou]

我一一测过，都不对。

[sgtzzc]

[Quote=引用 8 楼 funsuzhou 的回复:]
我一一测过，都不对。
[/Quote]

不是每天都有价格？

[z1g2w3i4]

我机器上没有MS SQlSERVER,写了如下 sql代码,希望在你整理后能解决问题.
1.CODE相同的记录中APRICE有变动时要提出来
select * from table0 t5,(
select count(t3.code) count_,t3.code code from (
select t2.code,t2.aprice,count(t3.code) count_ from table0 t2 group by t2.code,t2.aprice
) t3 group by t3.code where t3.count_>1
) t4 where t4.code=t5.code
2.
union all
3.无变动时只取第1条记录
select destinct(t4.code,aprice) from table0 t5,(
select count(t3.code) count_,t3.code code from (
select t2.code,t2.aprice,count(t3.code) count_ from table0 t2 group by t2.code,t2.aprice
) t3 group by t3.code where t3.count_=1
) t4 where t4.code=t5.code

[Mirana_NightShade]

学习

[wlmstar]

lz怎么没动静了？？？？

[z812183667]

学习了！

[shabble]

我也很关注这种问题

[shabble]

--> 测试数据：@table
CREATE TABLE #table([CODE] varchar(1),[APRICE] numeric(2,1),[FDATE] varchar(8))
insert #table
select 'A',1,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'A',1,'20091010'

CREATE TABLE #t(
id int IDENTITY (1, 1),
[CODE] varchar(1),
[APRICE] numeric(2,1),
[FDATE] varchar(8))

insert into #t
select CODE,APRICE,FDATE from
(select distinct CODE,0 APRICE,'' FDATE from #table union select CODE,APRICE,FDATE from #table) a
order by CODE,FDATE

select * from #t
select b.CODE,b.APRICE,b.FDATE from #t a,#t b
where a.id = b.id -1 and a.APRICE <> b.APRICE

drop table #table
drop table #t
--测试结果:
/*
CODE APRICE FDATE
A 1.0 20091001
A 2.0 20091003
A 1.0 20091006
A 1.5 20091007
A 1.0 20091008
*/

另：这段代码是我抱着我们家可可（刚刚60天）一边哄着她哼着催眠曲一边敲出来的，这是我第一次在这里解答问题，仅以此贴献给我家那可爱的姑娘^_^

[funsuzhou]

自己顶一下。

[wlmstar]

把4楼的改了一下，加了一些数据，楼主看看，这个结果对不对

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([CODE] varchar(1),[APRICE] numeric(2,1),[FDATE] datetime)
insert [tb]
select 'A',1,'20091001' union all
select 'B',5,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'B',8,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'B',6,'20091009' union all
select 'A',1,'20091010'

with cte as 
(
select Row_Number() OVER(ORDER BY CODE ASC,FDATE ASC) as RN,*
from tb
)

select CODE,APRICE,FDATE
from cte t
where not exists(select 1 from cte where t.RN-RN=1 and APRICE=t.APRICE and CODE=t.CODE)
order by FDATE asc,CODE asc 

[shabble]

--> 测试数据：@table
declare @table table([CODE] varchar(1),[APRICE] numeric(2,1),[FDATE] varchar(8))
insert @table
select 'A',1,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'A',1,'20091010'

select [CODE],[APRICE],min([FDATE]) as [FDATE]
from
(
select
(select count(1) from @table where [FDATE] <= t.[FDATE]) -
(select count(1) from @table where [FDATE]<= t.[FDATE] and [APRICE] = t.[APRICE]) as id,* 
from @table t 
) h
group by id,[CODE],[APRICE] order by [CODE],[FDATE]

[funsuzhou]

TO 16楼：
with cte as 提示错误。
稍微改一下，就可以了，谢谢！

结贴。

[funsuzhou]

To shabble：
非常感谢你的回复，祝你家姑娘越长越漂亮！

[funsuzhou]

昨天又测了一下，最下面还应该加上,RN，因为同一天里面如果相同CODE也有价格变动的情况发生。

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([CODE] varchar(1),[APRICE] numeric(2,1),[FDATE] datetime)
insert [tb]
select 'A',1,'20091001' union all
select 'B',5,'20091001' union all
select 'A',1,'20091002' union all
select 'A',2,'20091003' union all
select 'A',2,'20091004' union all
select 'A',2,'20091005' union all
select 'A',1,'20091006' union all
select 'B',8,'20091006' union all
select 'A',1.5,'20091007' union all
select 'A',1,'20091008' union all
select 'A',1,'20091009' union all
select 'B',6,'20091009' union all
select 'A',1,'20091010'

with cte as 
(
select Row_Number() OVER(ORDER BY CODE ASC,FDATE ASC) as RN,*
from tb
)

select CODE,APRICE,FDATE
from cte t
where not exists(select 1 from cte where t.RN-RN=1 and APRICE=t.APRICE and CODE=t.CODE)
order by FDATE asc,CODE asc,RN asc