我简化成很简单一个问题:
有2个表(上半年表和下半年表), 2个字段,一个是工号,一个是工资。
现在要select into一个新表,
表:g3200601 字段 w1(工号) 总额(工资)
g3200602 字段 w1(工号) 总额(工资)
要求:因为有人员调动,工号按照02表的为准,也就是新表的记录和02表一致,
新表的工资为01表和02表相加;
我这样写:select g3200602.w1 as w1,g3200601.总额+g3200602.总额 as 总额 into g32006 from g3200601,g3200602
我看数据工资字段是对的,但是w1字段出现好多重复的工号,是怎么回事?应该怎么写?
有2个表(上半年表和下半年表), 2个字段,一个是工号,一个是工资。
现在要select into一个新表,
表:g3200601 字段 w1(工号) 总额(工资)
g3200602 字段 w1(工号) 总额(工资)
要求:因为有人员调动,工号按照02表的为准,也就是新表的记录和02表一致,
新表的工资为01表和02表相加;
我这样写:select g3200602.w1 as w1,g3200601.总额+g3200602.总额 as 总额 into g32006 from g3200601,g3200602
我看数据工资字段是对的,但是w1字段出现好多重复的工号,是怎么回事?应该怎么写?
from g3200602
LEFT JOIN g3200601 ON g3200602.w1 = g3200601.w1
select g3200602.w1 as w1,isnull(g3200601.总额,0)+isnull(g3200602.总额,0) as 总额
into g32006 from g3200602
LEFT JOIN g3200601 ON g3200602.w1 = g3200601.w1----汇总求和后插入到新表中
select g3200602.w1 as w1,sum(isnull(g3200601.总额,0)+isnull(g3200602.总额,0)) as 总额
into g32006 from g3200602
LEFT JOIN g3200601 ON g3200602.w1 = g3200601.w1
GROUP BY g3200602.w1
select w1,sum(总额) as 总额 from
(
select g3200601.w1 as w1,sum(g3200601.总额) as 总额 from g3200601 group by g3200601.w1 union all
select g3200602.w1 as w1,sum(g3200602.总额) as 总额 from g3200602 group by g3200602.w1
) tb group by tb.w1
select a.w1,a.总额 + b.总额 as 总额 into g32006 from
(select w1,sum(isnull(总额,0) as 总额 from g3200602 group by w1) as a
LEFT JOIN
(select w1,sum(isnull(总额,0) as 总额 from g3200601 group by w1) as b
ON a.w1 = b.w1
在后面加上 g3200602.w1 = g3200601.w1但这样也应不对(表1有人员调动),这样关联当表,表2都有的记录才会出来.用hellowork(一两清风) 的就可以了:
select g3200602.w1 as w1,g3200601.总额+g3200602.总额 as 总额 into g32006
from g3200602
LEFT JOIN g3200601 ON g3200602.w1 = g3200601.w1
但实际情况是这样的表是12个,1月1个,工号取12月的。 不知道怎么样的格式才能leftjoin?