大虾，帮忙啊，求一Sql语句（邹建大哥，麻烦你的了）
有一个价格表Pub_PriceChePart
字段（corpid 公司号
supid 供应商号
partid 零部件号
partp 价格
startdate 价格执行开始日期
stopdate 价格执行结束日期
数据如下
corpid supid ，partid，partp startdate ，stopdate
105 001002 0012 0.12 2007.05.12 2007.05.20
105 001002 0012 0.15 2007.05.21 2007.05.25
105 001002 0012 0.20 2007.05.26 2007.06.10
105 001002 0012 0.30 2007.06.11 2007.06.20
105 001002 0012 0.35 2007.06.21 2007.06.25
先有个问题是这样的：
需要统计出
每个供应商号下，每个零部件，在2007.05.25的执行价格，和时间段（2007.05.26-2007.06.25）之间该零部件的平均单价
需要的结果是
corpid supid partid (05.25)的价格 （这个月的）平均单价
105 001002 0012 0.15， （0.20＋0.30＋0.35）/3
麻烦帮忙下！
[解决办法]
select corpid,supid,partid,
(select partp from PriceChePart where corpid=a.corpid and supid=a.supid and partid=a.partid and '2007-5-25 ' between startdate and stopdate) as [(05.25)的价格],
avg(partp) as [（这个月的）平均单价]
from Pub_PriceChePart a
where stopdate> = '2007-5-26 '
and startdate <= '2007-6-25 '
group by corpid,supid,partid
[解决办法]
declare @date varchar(10)
select corpid,supid,partid,價格 = (select a.partp from Pub_PriceChePart a where a.corpid = corpid and a.supid = supid and a.partid = partid and a.startdate <= @date and a.stopdate> @date)
, 平均價格 = (select sum(partp)/count(1) from Pub_PriceChePart b where b.corpid = corpid and b.supid = supid and b.partid = partid and datediff(mm,b.stopdate,getdate()) = 0 ) from Pub_PriceChePart
[解决办法]
declare @date varchar(10)
select corpid,supid,partid,價格 = (select a.partp from Pub_PriceChePart a where a.corpid = corpid and a.supid = supid and a.partid = partid and a.startdate <= @date and a.stopdate> @date)
, 平均價格 = (select avg(partp) from Pub_PriceChePart b where b.corpid = corpid and b.supid = supid and b.partid = partid and datediff(mm,b.stopdate,getdate()) = 0 ) from Pub_PriceChePart
[解决办法]
這個可以直接用關聯寫
Select
A.corpid,
A.supid,
A.partid,
B.partp As 执行价格,
Cast(AVG(A.partp) As Numeric(5, 2)) As 平均单价
From
Pub_PriceChePart A
Inner Join
Pub_PriceChePart B
On
A.corpid = B.corpid And A.supid = B.supid And A.partid = B.partid
Where
A.startdate > = '2007-05-25 ' And A.stopdate <= '2007-06-25 '
And
'2007-05-25 ' Between B.startdate And B.stopdate
Group By
A.corpid,
A.supid,
A.partid,
B.partp
[解决办法]
--参数,可以查每个月
declare @date datetime
set @date= '2007-5-25 '
--查询
select corpid,supid,partid,
(select partp from PriceChePart where corpid=a.corpid and supid=a.supid and partid=a.partid and @date between startdate and stopdate) as [(05.25)的价格],
avg(partp) as [（这个月的）平均单价]
from Pub_PriceChePart a
where stopdate> @date
and startdate <=dateadd(month,1,@date)
group by corpid,supid,partid
[解决办法]
Create Table Pub_PriceChePart
(corpid Varchar(10),
supid Varchar(10),
partid Varchar(10),
partp Numeric(5, 2),
startdate DateTime,
stopdate DateTime)
Insert Pub_PriceChePart Select '105 ', '001002 ', '0012 ', 0.12, '2007.05.12 ', '2007.05.20 '
Union All Select '105 ', '001002 ', '0012 ', 0.15, '2007.05.21 ', '2007.05.25 '
Union All Select '105 ', '001002 ', '0012 ', 0.20, '2007.05.26 ', '2007.06.10 '
Union All Select '105 ', '001002 ', '0012 ', 0.30, '2007.06.11 ', '2007.06.20 '
Union All Select '105 ', '001002 ', '0012 ', 0.35, '2007.06.21 ', '2007.06.25 '
GO
--Select * From Pub_PriceChePart
Select
A.corpid,
A.supid,
A.partid,
Min(B.partp) As 执行价格,
Cast(AVG(A.partp) As Numeric(5, 2)) As 平均单价
From
Pub_PriceChePart A
Inner Join
Pub_PriceChePart B
On
A.corpid = B.corpid And A.supid = B.supid And A.partid = B.partid
Where
A.startdate > = '2007-05-25 ' And A.stopdate <= '2007-06-25 '
And
'2007-05-25 ' Between B.startdate And B.stopdate
Group By
A.corpid,
A.supid,
A.partid
GO
Drop Table Pub_PriceChePart
--Result
/*
corpidsupidpartid执行价格平均单价
1050010020012.15.28
*/
