第1个回答 2011-07-04
create table 表 (
日期 varchar(8),
id int,
名称 varchar(10),
进货数量 int,
进货价格 money,
零售数量 int,
零售价格 money)
go
insert into 表
select '20110301',101,'笔芯',500,0.5,0,0 union all
select '20110302',102,'尺子',300,0.8,0,0 union all
select '20110303',103,'记事本',400,1.2,0,0 union all
select '20110304',102,'尺子',0,0,50,1.0 union all
select '20110305',103,'记事本',0,0,60,1.5
go
select e.最新日期,e.id,e.名称,h.平均价格,h.最高价格,h.最低价格,e.最新价格 from
(
select distinct c.id,d.名称,c.最新日期,case when d.进货价格>0 then d.进货价格 else d.零售价格 end as 最新价格
from
(
select a.id,case when a.ma>b.mb or b.mb is null then a.ma else b.mb end as 最新日期
from
(select id,max(convert(datetime,日期))as ma from 表 where 进货价格>0 group by id) a left join
(select id,max(convert(datetime,日期))as mb from 表 where 零售价格>0 group by id) b
on a.id=b.id
)c left join 表 d on c.id=d.id and c.最新日期=convert(datetime,d.日期)
)e left join
(
select f.id,(f.进货总价+case when g.零售总价 is null then 0 else g.零售总价 end)/(f.进货总数量+case when g.零售总数量 is null then 0 else g.零售总数量 end) as 平均价格,
case when f.最高进货价格>g.最高零售价格 or g.最高零售价格 is null then f.最高进货价格 else g.最高零售价格 end as 最高价格,
case when f.最低进货价格<g.最低零售价格 or g.最高零售价格 is null then f.最低进货价格 else g.最低零售价格 end as 最低价格
from
(select id,max(进货价格) as 最高进货价格,min(进货价格) as 最低进货价格,sum(进货价格*进货数量)as 进货总价,sum(进货数量) as 进货总数量 from 表 where 进货价格>0 group by id) f left join
(select id,max(零售价格) as 最高零售价格,min(零售价格) as 最低零售价格,sum(零售价格*零售数量)as 零售总价,sum(零售数量) as 零售总数量 from 表 where 零售价格>0 group by id) g
on f.id=g.id
) h on e.id=h.id
运行结果如下:
（所影响的行数为 5 行）
最新日期 id 名称 平均价格 最高价格 最低价格 最新价格
------------------------------------------------------ ----------- ---------- --------------------- --------------------- --------------------- ---------------------
2011-03-01 00:00:00.000 101 笔芯 .5000 .5000 .5000 .5000
2011-03-04 00:00:00.000 102 尺子 .8285 1.0000 .8000 1.0000
2011-03-05 00:00:00.000 103 记事本 1.2391 1.5000 1.2000 1.5000
（所影响的行数为 3 行）本回答被提问者采纳