---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([列1] numeric(20,0),[列2] numeric(3,2),[列3] numeric(4,3),[列4] numeric(4,3)) insert [tb] select 20090506142053500000,0.11,1.235,4.022 union all select 20090506142053500000,0.11,1.245,4.022 union all select 20090506142053500000,0.14,1.542,4.022 union all select 20090506142107800000,0.13,1.541,4.091 union all select 20090506142107800000,0.13,1.32,4.091 union all select 20090506142107800000,0.11,1.23,4.091 union all select 20090506142121200000,0.13,1.235,3.659 union all select 20090506142121200000,0.18,1.21,3.659 union all select 20090506142121200000,0.13,1.214,3.659
---查询--- select 列1, sum(列2*列3)/列4 from tb group by 列1, 列4 ---结果--- 列1 ---------------------- ---------------------------------------- 20090506142121200000 .146534 20090506142053500000 .121501 20090506142107800000 .123986(所影响的行数为 3 行)
select 列1, sum(列2*列3)/min(列4) from tb group by 列1树人兄的代码中如果同一个列1值,如果列4值不一样,那么可能出现问题。 如果取一个列4的最小,应该能避免这个问题。(楼主已经说明,列4中取随便一个值就可以的)
楼上两位高手 我的数据集是这么得来的 我怎么弄成你们那样的 太笨了 select randomID,caoh, (select fecnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as fecnt, (select sicnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as sicnt, weight, (select Total from scheduling_Total where scheduling.randomID=scheduling_Total.randomID and scheduling_Total.cheak_ID='1') as Total, team,date_time from scheduling where date_time='2009-05-06' and plant_name='5' order by randomID,team
列1randomID 列2fecnt 列3 weight 列4Total
select randomID, sum(fecnt*weight)/Total from ( select randomID,caoh, (select fecnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as fecnt, (select sicnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as sicnt, weight, (select Total from scheduling_Total where scheduling.randomID=scheduling_Total.randomID and scheduling_Total.cheak_ID='1') as Total, team,date_time from scheduling where date_time='2009-05-06' and plant_name='5' ) t group by randomID, Total
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([列1] numeric(20,0),[列2] numeric(3,2),[列3] numeric(4,3),[列4] numeric(4,3))
insert [tb]
select 20090506142053500000,0.11,1.235,4.022 union all
select 20090506142053500000,0.11,1.245,4.022 union all
select 20090506142053500000,0.14,1.542,4.022 union all
select 20090506142107800000,0.13,1.541,4.091 union all
select 20090506142107800000,0.13,1.32,4.091 union all
select 20090506142107800000,0.11,1.23,4.091 union all
select 20090506142121200000,0.13,1.235,3.659 union all
select 20090506142121200000,0.18,1.21,3.659 union all
select 20090506142121200000,0.13,1.214,3.659
---查询---
select
列1,
sum(列2*列3)/列4
from
tb
group by
列1,
列4
---结果---
列1
---------------------- ----------------------------------------
20090506142121200000 .146534
20090506142053500000 .121501
20090506142107800000 .123986(所影响的行数为 3 行)
列1,
sum(列2*列3)/min(列4)
from
tb
group by
列1树人兄的代码中如果同一个列1值,如果列4值不一样,那么可能出现问题。
如果取一个列4的最小,应该能避免这个问题。(楼主已经说明,列4中取随便一个值就可以的)
select randomID,caoh,
(select fecnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as fecnt,
(select sicnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as sicnt,
weight,
(select Total from scheduling_Total where scheduling.randomID=scheduling_Total.randomID and scheduling_Total.cheak_ID='1') as Total,
team,date_time
from scheduling where date_time='2009-05-06' and plant_name='5' order by randomID,team
列2fecnt
列3 weight
列4Total
randomID,
sum(fecnt*weight)/Total
from
(
select randomID,caoh,
(select fecnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as fecnt,
(select sicnt from YLFX where scheduling.caoh=YLFX.caoh and ddate=(select max(ddate) from YLFX)) as sicnt,
weight,
(select Total from scheduling_Total where scheduling.randomID=scheduling_Total.randomID and scheduling_Total.cheak_ID='1') as Total,
team,date_time
from scheduling where date_time='2009-05-06' and plant_name='5'
) t
group by
randomID,
Total