select a.*, b.* from
(SELECT time, COUNT(*) AS Count1, SUM(x) AS sumx, SUM(y) AS sumy, SUM(x) / SUM(y) AS value
FROM TABLE1
GROUP BY time) as a
FULL OUTER JOIN
(SELECT time, COUNT(*) AS Count2, SUM(z) AS sumz
FROM TABLE2
GROUP BY time) as b
on a.time = b.time
(SELECT time, COUNT(*) AS Count1, SUM(x) AS sumx, SUM(y) AS sumy, SUM(x) / SUM(y) AS value
FROM TABLE1
GROUP BY time) as a
FULL OUTER JOIN
(SELECT time, COUNT(*) AS Count2, SUM(z) AS sumz
FROM TABLE2
GROUP BY time) as b
on a.time = b.time
好像没办法直接用SQL写,但可以用临时表
我按照你的方法很好的实现了,但是有一个问题查询的结果中有两个time字段,但是我需要的是一个完整的time,也就是两个time字段的并。请问怎么实现?
a.Count1, a.sumx, a.sumy, a.value ,
b.Count2,b.Sumz
from(SELECT time, COUNT(*) AS Count1, SUM(x) AS sumx, SUM(y) AS sumy, SUM(x) / SUM(y) AS value
FROM TABLE1
GROUP BY time) as aFULL outer JOIN (SELECT time, COUNT(*) AS Count2, SUM(z) AS sumz
FROM TABLE2
GROUP BY time) as bon a.time = b.time
可以将楼上的SQL语句改成这样
select a.*, b.Count2,b.sumz from
(SELECT time, COUNT(*) AS Count1, SUM(x) AS sumx, SUM(y) AS sumy, SUM(x) / SUM(y) AS value
FROM TABLE1
GROUP BY time) as a
FULL OUTER JOIN
(SELECT time, COUNT(*) AS Count2, SUM(z) AS sumz
FROM TABLE2
GROUP BY time) as b
on a.time = b.time
我举了下面这个例子:
按你原来给我的方法,其结果是这样的:a.time Count1 sumx sumy value b.time Count2 sumz
1995 1 2 3 4 1995 3 4
1996 1 2 3 4 null null null
null null null null null 1997 3 4
可是我希望结果是这样的:
a.time Count1 sumx sumy value Count2 sumz
1995 1 2 3 4 3 4
1996 1 2 3 4 null null
1997 null null null null 3 4