34,837
社区成员




select *
from (
select fgysbm,fcpbm,最新单价 as fdj, 最新数量 as fsl
from table a
where exists(select 1 from table where fgysbm = a.fgysbm and fcpbm = a.fcpbm and fsj > a.fsj)
) a
join (
select fgysbm,fcpbm,max(fdj) as maxfdj,min(fdj) as minfdj,avg(fdj) as avgfdj1,sum(fdj*fsl)/sum(fsl) as avgfdj2,max(fsl) as maxfsl,min(fsl) as minfsl
from table
group by fgysbm,fcpbm) b
on a.fgysbm = b.fgysbm and a.fcpbm = b.fcpbm
select fgysbm,fcpbm,
max(fdj) 最大单价,
min(fdj) 最小单价,
avg(fdj) 平均单价1,
sum(fdj*fsl)/sum(fsl)平均单价2,
(select top 1 fdj from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc) 最新单价,
max(fsl) 最大数量,
min(fsl) 最小数量,
(select top 1 fsl from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc) 最新数量
from table m
group by fgysbm,fcpbm
select fgysbm,fcpbm,
max(fdj) 最大单价,
min(fdj) 最小单价,
avg(fdj) 平均单价1,
sum(fdj*fsl)/sum(fsl)平均单价2,
(select top 1 fdj from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc) 最新单价,
max(fsl) 最大数量,
min(fsl) 最小数量,
(select top 1 fsl from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc) 最新数量
from table m
group by fgysbm,fcpbm
select fgysbm,fcpbm,
max(fdj) 最大单价,
min(fdj) 最小单价,
avg(fdj) 平均单价1,
sum(fdj*fsl)/sum(fsl)平均单价2,
(select top 1 fdj from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc) 最新单价,
max(fsl) 最大数量,
min(fsl) 最小数量,
(select fsl from table where fsj = (select top 1 fdj from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc)) 最新数量
from table m
select
fgysbm,
max(fdj),
min(fdj),
avg(fdj),
sum(fdj*fsl)/sum(fsl),
(select top 1 fdj from 表 where fgysbm=t.fgysbm order by fsj desc),
max(fsl),
min(fsl),
(select top 1 fsl from 表 where fgysbm=t.fgysbm order by fsj desc)
from
表 t
group by
fgysbm
select fgysbm,fcpbm,
max(fdj) 最大单价,
min(fdj) 最小单价,
avg(fdj) 平均单价1,
sum(fdj*fsl)/sum(fsl)平均单价2,
(select top 1 fdj from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc) 最新单价,
max(fsl) 最大数量,
min(fsl) 最小数量,
(select fsl from table where fsj = (select top 1 fdj from table where fgysbm = m.fgysbm and fcpbm = n.fcpbm order by fsj desc)) 最新数量
from table m
group by fgysbm,fcpbm