select dw,mc,sum(sl)
from(
select * from @表1 A
union all
select * from @表2 B) T
group by T.dw,T.mcorder by dw
from(
select * from @表1 A
union all
select * from @表2 B) T
group by T.dw,T.mcorder by dw
select * from 表1 t
where not exists(select 1
from 表2
where dw=t.dw
and mc=t.mc)
select distinct dw,mc
from 表1 t
where not exists(select 1
from 表2
where dw=t.dw
and mc=t.mc)update t
set zsl=tb.zsl
from 表2 t
join (
select dw
,mc
,sum(sl) as 'zsl'
from 表1
group by dw,mc
)tb on t.dw=tb.dw
and
t.mc=tb.mc
declare @表1 table (dw varchar(10),mc varchar(10), sl int)
insert into @表1 select '厂一','牙刷',1
union all select '厂一','牙刷',2
union all select '厂二','牙刷',1
union all select '厂三','脸盆',1
union all select '厂四','毛巾',1
union all select '厂四','毛巾',1
union all select '厂五','毛巾',1
declare @表2 table (dw varchar(10),mc varchar(10), zsl int)
insert into @表2 select '厂一','牙刷',1
union all select '厂二','牙刷',1delete from @表2 (表2数据有什么作用??)--插入
insert into @表2
select dw,mc,sum(sl) from @表1 A
group by dw,mc
order by dw--显示结果
select * from @表2
order by dw
dw mc zsl
---------- ---------- -----------
厂二 牙刷 1
厂三 脸盆 1
厂四 毛巾 2
厂五 毛巾 1
厂一 牙刷 3
where not exists(select 1 from tb2 where tb2.dw=tb1.dw and tb2.mc=tb1.mc and tb2.sl=tb1.sl)
union all select * from tb2)t group by dw,mc
insert into TABLE2
select dw,mc,sum(sl) as sl from TABLE1 group by dw,mc
UPDATE a
SET a.zsl=b.sl
FROM TABLE2 a,
(SELECT dw,mc,SUM(sl) AS sl
FROM TABLE1 GROUP BY dw,mc) b
WHERE a.dw=b.dw
AND a.mc=b.mc--插入表二中没有的数据
INSERT INTO TABLE2
(dw, mc, zsl)
SELECT dw, mc, SUM(sl)
FROM TABLE1 a
WHERE NOT EXISTS
(
SELECT 1
FROM TABLE2 b
WHERE b.dw=a.dw
AND b.mc=a.mc
)
GROUP BY dw, mc