两个关联表查询出合计数？？（在线等）
有两个表的数据  
ZJCL表如下：
GH             CL                  
01               10  
02               12.5
…
01               16
02               12.5
…
SWCL表如下：
GH             SW          
01 5
02 6
…
01 5
02 5.5
…
现用工号相关联，出一个两个表的合计数
GH           CL           SW    
01             26           10
02             25           11.5  
--
用SQL怎么实现，急用，再线等~~~谢谢：）））


------解决方案--------------------

--如果两个表的数据不一一对应，则应用如下：

select
A.GH,
sum(B.CL) AS CL,
SUM(C.SW) AS SW
from
(select gh from SWCL group by gh union select gh from SWCL group by gh) as A
inner join ZJCL as B on A.gh=B.gh
inner join SWCL as C on A.GH=C.GH
group by A.GH


------解决方案--------------------
上面語句應改為（打left join 改為fll join ）

SELECT A.GH,A.CL,B.SW FROM
(select GH,sum(CL)AS CL from t group by GH)A
full JOIN
(SELECT GH,SUM(SW)AS SW FROM t1 group by GH)B
ON A.GH=B.GH
------解决方案--------------------
create table zjcl(Gh varchar(10),cl int)
insert into zjcl select 01,10
insert into zjcl select 01,12
insert into zjcl select 01,16
insert into zjcl select 02,12.5

create table swcl(Gh varchar(10),sw int)
insert into swcl select 01,5
insert into swcl select 02,6
insert into swcl select 02,5

select a.gh,cl,sw from
(select gh,sum(cl) as cl from zjcl
group by gh ) a
inner join
(select gh,sum(sw) as sw from swcl
group by gh) b
on a.gh=b.gh

gh cl sw
---------- ----------- -----------
1 38 5
2 12 11

(2 行受影响)

------解决方案--------------------

llh6795(紫燕归) ( ) 信誉：100 Blog 加为好友 2007-06-19 14:55:16 得分: 0


我现在是用存储过程处理的，将两个表关联生成了一个临时表

----------------------

因為倆表不是一一對應的，所以用FULL JOIN才行，INNNER JOIN不行

生成臨時表，稍微修改下代碼即可

--生成臨時表
Select
IsNull(A.GH, B.GH) As GH,
A.CL,
B.SW
INTO #T
From
(Select GH, SUM(CL) As CL From ZJCL Group By GH) A
Full Join
(Select GH, SUM(SW) As SW From SWCL Group By GH) B
On A.GH = B.GH

--查詢臨時表
Select * From #T