Select a.line,a.groups,a.hour1*sum(b.hour2) From table1 a Left Join table2 b On a.line = b.line And a.Groups = b.Groups Group By a.line,a.groups,a.hour1
GROUP BY后的字段一定要和SELECT后的字段对应 你的程序没时间帮你看,SORRY
select a.line, a.groups, a.hour1*b.s_hour from table1 as a left join (SELECT line,groups,sum(hour1) as s_hour from table2 group by line,groups) as b on a.line = b.line and a.groups = b.groups
SELECT a.line,a.groups,a.hour1*b.hour2 as hour FROM table1 AS a INNER JOIN ( SELECT line,groups,SUM(hour2) AS hour2 FROM table2 GROUP BY line,groups ) AS b ON a.line=b.line AND a.groups=b.groups
没必要sum(table1.hour1),因为Table1.Line,Table1.Groups本身就是主键, 而且与聚合函数一起出现在Select中的字段,必须出现在Group 中. 下面是我写的,已经调试过了SELECT Table1.Line,Table1.Groups,Table1.Hour1*A.SumHour2 FROM Table1 INNER JOIN (SELECT Table2.Line,Table2.Groups,SUM(Table2.Hour2) AS SumHour2 FROM Table2 GROUP BY Table2.Line,Table2.Groups) A ON Table1.Line=A.Line AND Table1.Groups=A.Groups
SELECT a.line,a.groups,a.hour1*b.hour2 as hour FROM table1 a, ( SELECT line,groups,SUM(hour2) AS hour2 FROM table2 GROUP BY line,groups ) b WHERE a.line=b.line AND a.groups=b.groups
From table1 a Left Join table2 b
On a.line = b.line And a.Groups = b.Groups
Group By a.line,a.groups,a.hour1
你的程序没时间帮你看,SORRY
from table1 as a left join
(SELECT line,groups,sum(hour1) as s_hour from table2
group by line,groups) as b
on a.line = b.line and a.groups = b.groups
FROM table1 AS a INNER JOIN
(
SELECT line,groups,SUM(hour2) AS hour2
FROM table2
GROUP BY line,groups
)
AS b
ON a.line=b.line AND a.groups=b.groups
改成:sum((table1.hour1)*(table2.hour2))
而且与聚合函数一起出现在Select中的字段,必须出现在Group 中.
下面是我写的,已经调试过了SELECT Table1.Line,Table1.Groups,Table1.Hour1*A.SumHour2
FROM Table1
INNER JOIN (SELECT Table2.Line,Table2.Groups,SUM(Table2.Hour2) AS SumHour2 FROM Table2
GROUP BY Table2.Line,Table2.Groups) A
ON Table1.Line=A.Line AND Table1.Groups=A.Groups
FROM table1 a,
(
SELECT line,groups,SUM(hour2) AS hour2
FROM table2
GROUP BY line,groups
) b
WHERE a.line=b.line AND a.groups=b.groups