显示所有单价有变化的相关物料的全部记录有表如下:
itemcode dscription cardname price currency docnum docdate
(物料号) (物料描述) (供应商名) (单价) (币别) (订单号) (订单日期)
-------------------------------------------------------------------------------
A A物料 A001 3.00 RMB 12001200 2011-01-01
A A物料 A002 3.50 RMB 12001202 2011-01-05
B B物料 A001 3.50 RMB 12001211 2011-01-05
A A物料 A003 3.50 RMB 12001222 2011-01-05
C C物料 A004 3.50 RMB 12001242 2011-01-05
A A物料 A002 3.50 RMB 12001212 2011-01-05
C C物料 A005 4.50 RMB 12001213 2011-01-05
A A物料 A004 3.50 RMB 12001215 2011-01-05
B B物料 A002 3.50 RMB 12001215 2011-01-05
A A物料 A001 3.55 RMB 12001216 2011-01-05
B B物料 A007 3.50 RMB 12001217 2011-01-05================================================================================
现在要求结果如下:
因为A\C物料价格有变动过,所以A\C物料的所有记录都要:
A A物料 A001 3.00 RMB 12001200 2011-01-01
A A物料 A002 3.50 RMB 12001202 2011-01-05
A A物料 A003 3.50 RMB 12001222 2011-01-05
A A物料 A002 3.50 RMB 12001212 2011-01-05
A A物料 A004 3.50 RMB 12001215 2011-01-05
A A物料 A001 3.55 RMB 12001216 2011-01-05
C C物料 A004 3.50 RMB 12001242 2011-01-05
C C物料 A005 4.50 RMB 12001213 2011-01-05
itemcode dscription cardname price currency docnum docdate
(物料号) (物料描述) (供应商名) (单价) (币别) (订单号) (订单日期)
-------------------------------------------------------------------------------
A A物料 A001 3.00 RMB 12001200 2011-01-01
A A物料 A002 3.50 RMB 12001202 2011-01-05
B B物料 A001 3.50 RMB 12001211 2011-01-05
A A物料 A003 3.50 RMB 12001222 2011-01-05
C C物料 A004 3.50 RMB 12001242 2011-01-05
A A物料 A002 3.50 RMB 12001212 2011-01-05
C C物料 A005 4.50 RMB 12001213 2011-01-05
A A物料 A004 3.50 RMB 12001215 2011-01-05
B B物料 A002 3.50 RMB 12001215 2011-01-05
A A物料 A001 3.55 RMB 12001216 2011-01-05
B B物料 A007 3.50 RMB 12001217 2011-01-05================================================================================
现在要求结果如下:
因为A\C物料价格有变动过,所以A\C物料的所有记录都要:
A A物料 A001 3.00 RMB 12001200 2011-01-01
A A物料 A002 3.50 RMB 12001202 2011-01-05
A A物料 A003 3.50 RMB 12001222 2011-01-05
A A物料 A002 3.50 RMB 12001212 2011-01-05
A A物料 A004 3.50 RMB 12001215 2011-01-05
A A物料 A001 3.55 RMB 12001216 2011-01-05
C C物料 A004 3.50 RMB 12001242 2011-01-05
C C物料 A005 4.50 RMB 12001213 2011-01-05
from tb t
where (select count(distinct price) from tb where itemcode=t.itemcode)>1
where itemcode in(select itemcode from tb group by itemcode having max(price)>min(price))
(select itemcode from (select distinct itemcode , price from tb) t group by itemcode having count(1) > 1)
declare @t table (itemcode varchar(10), dscription varchar(10), cardname varchar(10), price money, currency varchar(10), docnum int ,docdate datetime
)insert @t
select 'A ','A物料', 'A001', 3.00 ,'RMB' ,12001200 ,'2011-01-01' union all
select 'A ','A物料', 'A002', 3.50 ,'RMB' ,12001202 ,'2011-01-05' union all
select 'B ','B物料', 'A001', 3.50 ,'RMB' ,12001211 ,'2011-01-05' union all
select 'A ','A物料', 'A003', 3.50 ,'RMB' ,12001222 ,'2011-01-05' union all
select 'C ','C物料', 'A004', 3.50 ,'RMB' ,12001242 ,'2011-01-05' union all
select 'A ','A物料', 'A002', 3.50 ,'RMB' ,12001212 ,'2011-01-05' union all
select 'C ','C物料', 'A005', 4.50 ,'RMB' ,12001213 ,'2011-01-05' union all
select 'A ','A物料', 'A004', 3.50 ,'RMB' ,12001215 ,'2011-01-05' union all
select 'B ','B物料', 'A002', 3.50 ,'RMB' ,12001215 ,'2011-01-05' union all
select 'A ','A物料', 'A001', 3.55 ,'RMB' ,12001216 ,'2011-01-05' union all
select 'B ','B物料', 'A007', 3.50 ,'RMB' ,12001217 ,'2011-01-05' SELECT * FROM @t a where exists (select 1 from @t where a.dscription=dscription and a.price <> price) order by itemcode