表A
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stuff_OutLs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Stuff_OutLs]
GOCREATE TABLE [dbo].[Stuff_OutLs] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[gdh] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kf] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kgy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[spdm] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[jldw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cksl] [decimal](15, 6) NOT NULL ,
[cbje] [decimal](12, 2) NOT NULL ,
[cbdj] [decimal](15, 6) NOT NULL ,
[jjje] [decimal](12, 2) NOT NULL ,
[jjdj] [decimal](15, 6) NOT NULL ,
[czy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kfys] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]表B
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stuff_Actual]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Stuff_Actual]
GOCREATE TABLE [dbo].[Stuff_Actual] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[kf] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kgy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[spdm] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hw] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[jldw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[kcsl] [decimal](15, 6) NOT NULL ,
[cbje] [decimal](12, 2) NOT NULL ,
[cbdj] [decimal](15, 6) NOT NULL ,
[jjje] [decimal](12, 2) NOT NULL ,
[jjdj] [decimal](15, 6) NOT NULL ,
) ON [PRIMARY]
GO我的意思是将表stuff_outls中的数据汇总且修改表stuff_actual中的数据,条件是stuff_outls.spdm=stuff_actual.spdm and stuff_outls.kf=stuff_actual.kf
我以前的写法有错误,是这样的:
update a set a.kcsl=a.kcsl-b.cksl,a.cbje=a.cbje-b.cbje,a.jjje=a.jjje-b.jjje,
a.cbdj=round((a.cbje-b.cbje)/(a.kcsl-b.cksl),6),a.jjdj=round((a.jjje-b.jjje)/(a.kcsl-b.cksl),6)
from Stuff_Actual a left join Stuff_OutLs b on a.spdm=b.spdm and a.kf=b.kf
这种写法只是修改一条记录。
如果是汇总修改我就没想明白怎么做?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stuff_OutLs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Stuff_OutLs]
GOCREATE TABLE [dbo].[Stuff_OutLs] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[gdh] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kf] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kgy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[spdm] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[jldw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cksl] [decimal](15, 6) NOT NULL ,
[cbje] [decimal](12, 2) NOT NULL ,
[cbdj] [decimal](15, 6) NOT NULL ,
[jjje] [decimal](12, 2) NOT NULL ,
[jjdj] [decimal](15, 6) NOT NULL ,
[czy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kfys] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]表B
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Stuff_Actual]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Stuff_Actual]
GOCREATE TABLE [dbo].[Stuff_Actual] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[kf] [varchar] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kgy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[spdm] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hw] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[jldw] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[kcsl] [decimal](15, 6) NOT NULL ,
[cbje] [decimal](12, 2) NOT NULL ,
[cbdj] [decimal](15, 6) NOT NULL ,
[jjje] [decimal](12, 2) NOT NULL ,
[jjdj] [decimal](15, 6) NOT NULL ,
) ON [PRIMARY]
GO我的意思是将表stuff_outls中的数据汇总且修改表stuff_actual中的数据,条件是stuff_outls.spdm=stuff_actual.spdm and stuff_outls.kf=stuff_actual.kf
我以前的写法有错误,是这样的:
update a set a.kcsl=a.kcsl-b.cksl,a.cbje=a.cbje-b.cbje,a.jjje=a.jjje-b.jjje,
a.cbdj=round((a.cbje-b.cbje)/(a.kcsl-b.cksl),6),a.jjdj=round((a.jjje-b.jjje)/(a.kcsl-b.cksl),6)
from Stuff_Actual a left join Stuff_OutLs b on a.spdm=b.spdm and a.kf=b.kf
这种写法只是修改一条记录。
如果是汇总修改我就没想明白怎么做?
a.cbje=a.cbje-(select sum(cbje) from Stuff_OutLs c where a.spdm=c.spdm and a.kf=c.kf),
a.jjje=a.cbje-(select sum(jjje) from Stuff_OutLs c where a.spdm=c.spdm and a.kf=c.kf)
from Stuff_Actual a left join Stuff_OutLs b on a.spdm=b.spdm and a.kf=b.kf
where a.spdm=b.spdm and a.kf=b.kf
我这样写在存储过程中,不知道是否正确,如果stuff_outls中的合计数据大于stuff_actual中的数据,怎么样返回一个错误呢?
set a.kcsl = a.kcsl-b.cksl,
a.cbje=a.cbje -b.cbje,
a.jjje=a.jjje-b.jjjefrom Stuff_Actual a
inner join
(select spdm,kf,sum(cksl) as cksl,sum(cbje) as cbje,sum(jjje) as jjje from Stuff_OutLs group by spdm,kf)b
on a.spdm=b.spdm and a.kf=b.kf
例:
select * from 表,
(select id,sum(qty) from 表 group by id)t
where 表.id = t.id