翻译一下,就是这样:
select a.工号,a.姓名 ,M1.amt,M2.amt from 表1 a
left Join
(select 工号,sum(金额) amt from 表2 where 时间='2004-1' group by 工号 ) M1
on a.工号=m1.工号
left Join
(select 工号,sum(金额) amt from 表2 where 时间='2004-2' group by 工号 ) M2
on a.工号=m1.工号
select a.id,a.name ,M1.amt,M2.amt from tab_employee a
left Join
(select id,sum(amount) amt from tab_salary where period='2004-1' group by id ) M1
on a.id=m1.id
left Join
(select id,sum(amount) amt from tab_salary where period='2004-2' group by id ) M2
on a.id=m2.id
str1='a.工号,a.姓名';
str2='(select * from 表1) a'
for i=1 to 12 do
begin
str1:=str1+',i.发生金额';
str2:=str2+',(select * from 表2 where substr(年月,6,1)=i) i';
end
str='select '+str1+' from '+str2
with query do
begin
close;
sql.clear;
sql.add(str)
open;
end
SELECT 表1.ID,M1.AMT,M2.AMT,……M12.AMT
FROM 表1
LEFT JOIN (SELECT 1月的金额,按工号分组 FROM 表2) M1
LEFT JOIN (SELECT 2月的金额,按工号分组 FROM 表2) M2
……
LEFT JOIN (SELECT 12月的金额,按工号分组 FROM 表2) M12
WHERE ……