表HS
XM(姓名) TPM(图片名) GZL(工作量) ZLXS(质量系数) GZZY(标准工作量金额)(固定)
A C1 3 4 25
A C2 6 8 25
A C3 5 4 25
B C4 4 4 25
B C5 3 6 25表PD
XM(姓名) EW1(额外1) EW2(额外2) EW3(额外3)
A 2 3 5
B 5 5 8我要从这两个表中取数据得到一个数据Q=(一个人得工作量之和+EW1+EW2)/(所有人员的工作量之和/人数)+(一个人质量系数之和/一个人的图片数)+EW3
然后得到一个表
XM Q
这个SQL语句怎么写呢?
XM(姓名) TPM(图片名) GZL(工作量) ZLXS(质量系数) GZZY(标准工作量金额)(固定)
A C1 3 4 25
A C2 6 8 25
A C3 5 4 25
B C4 4 4 25
B C5 3 6 25表PD
XM(姓名) EW1(额外1) EW2(额外2) EW3(额外3)
A 2 3 5
B 5 5 8我要从这两个表中取数据得到一个数据Q=(一个人得工作量之和+EW1+EW2)/(所有人员的工作量之和/人数)+(一个人质量系数之和/一个人的图片数)+EW3
然后得到一个表
XM Q
这个SQL语句怎么写呢?
,((select sum(GZZY) from hs where xm = a.xm)
+(select sum(EW1+EW2) from pd where xm = a.xm))
/((select sum(GZZY) from hs)/count(distinct xm))
+ (sum(ZLXS)/count(distinctTPM)
+ (select sum(EW3) from pd where xm = a.xm))
from hs a
group by XM
,((select sum(GZZY) from hs where xm = a.xm)
+(select sum(EW1+EW2) from pd where xm = a.xm))
/((select sum(GZZY) from hs)/count(distinct xm))
+ (sum(ZLXS)/count(distinct TPM)
+ (select sum(EW3) from pd where xm = a.xm)) as Q
from hs a
group by XM
create table HS(XM char,TPM char(2),GZL int,ZLXS int,GZZY int)
create table PD(XM char,EW1 int,EW2 int,EW3 int)
insert into hs select 'A', 'C1', 3, 4, 25
insert into hs select 'A', 'C2', 6 , 8, 25
insert into hs select 'A', 'C3', 5, 4, 25
insert into hs select 'B', 'C4', 4, 4, 25
insert into hs select 'B', 'C5', 3 , 6 , 25
insert into pd select 'A', 2, 3, 5
insert into pd select 'B', 5, 5, 8
select xm
,((select sum(GZZY) from hs where xm = a.xm)
+(select sum(EW1+EW2) from pd where xm = a.xm))
/((select sum(GZZY) from hs)/count(distinct xm))
+ (sum(ZLXS)/count(distinct TPM)
+ (select sum(EW3) from pd where xm = a.xm)) as Q
from hs a
group by XM
drop table hs
drop table pd
--测试结果--
A 10
B 13
[Q]=((select sum(GZL) from HS where XM=c.XM group by XM) +
(select(EW1) from PD where XM=c.XM)+
(select(EW2) from PD where XM=c.XM))/
((select sum(GZL) from HS) /(select count(XM) from PD)),--+ ((select sum(ZLXS) from HS where XM=c.XM )/
(select count(TPM) from HS where XM=c.XM group by XM))
+(select EW3 from PD where XM=c.XM)
from HS c inner join PD d on c.XM=d.XM
group by c.XM
[Q]=((select sum(GZL) from HS where XM=c.XM group by XM) +
(select(EW1+EW2) from PD where XM=c.XM))/
((select sum(GZL) from HS) /(select count(XM) from PD))+
((select sum(ZLXS) from HS where XM=c.XM )/
(select count(TPM) from HS where XM=c.XM group by XM))
+(select EW3 from PD where XM=c.XM)
from HS c inner join PD d on c.XM=d.XM
group by c.XM