表A
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yy_Ryye]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[yy_Ryye]
GOCREATE TABLE [dbo].[yy_Ryye] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[rq] [datetime] NOT NULL default getdate(),
[xl] [varchar] (1) COLLATE Chinese_PRC_CI_AS not NULL default '',
[yye] [decimal](9, 2) NOT NULL default 0,
[yp] [numeric](9, 2) NOT NULL default 0,
) ON [PRIMARY]
GO
表B
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Yy_RyyeCx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Yy_RyyeCx]
GOCREATE TABLE [dbo].[Yy_RyyeCx] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[rq] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[yye1] [numeric](10, 2) NOT NULL ,
[yp1] [int] NOT NULL ,
[yye3] [numeric](10, 2) NOT NULL ,
[yp3] [int] NOT NULL ,
[yye4] [numeric](10, 2) NOT NULL ,
[yp4] [int] NOT NULL ,
[yye6] [numeric](10, 2) NOT NULL ,
[yp6] [int] NOT NULL ,
[yye7] [numeric](10, 2) NOT NULL ,
[yp7] [int] NOT NULL ,
[yye_hj] [numeric](10, 2) NOT NULL ,
[yp_hj] [int] NOT NULL
) ON [PRIMARY]
GO
将表A中的数据汇总到表B中,要求是这样的:
1.表A中的rq=表B中的rq.
2.表A中xl为1的yye数值汇总到表B中为yye1,表Ayp数值汇总到表B中为yp1
表A中xl为3的yye数值汇总到表B中为yye3,表Ayp数值汇总到表B中为yp3
表A中xl为4的yye数值汇总到表B中为yye4,表Ayp数值汇总到表B中为yp4
表A中xl为6的yye数值汇总到表B中为yye6,表Ayp数值汇总到表B中为yp6
表A中xl为7的yye数值汇总到表B中为yye7,表Ayp数值汇总到表B中为yp7
3.表B中的yye_hj=yye1+yye3+yye4+yye6+yye7,yp_hj=yp1+yp3+yp4+yp6+yp7.
我没写出来,请教各位,先表示感谢了!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[yy_Ryye]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[yy_Ryye]
GOCREATE TABLE [dbo].[yy_Ryye] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[rq] [datetime] NOT NULL default getdate(),
[xl] [varchar] (1) COLLATE Chinese_PRC_CI_AS not NULL default '',
[yye] [decimal](9, 2) NOT NULL default 0,
[yp] [numeric](9, 2) NOT NULL default 0,
) ON [PRIMARY]
GO
表B
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Yy_RyyeCx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Yy_RyyeCx]
GOCREATE TABLE [dbo].[Yy_RyyeCx] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[rq] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[yye1] [numeric](10, 2) NOT NULL ,
[yp1] [int] NOT NULL ,
[yye3] [numeric](10, 2) NOT NULL ,
[yp3] [int] NOT NULL ,
[yye4] [numeric](10, 2) NOT NULL ,
[yp4] [int] NOT NULL ,
[yye6] [numeric](10, 2) NOT NULL ,
[yp6] [int] NOT NULL ,
[yye7] [numeric](10, 2) NOT NULL ,
[yp7] [int] NOT NULL ,
[yye_hj] [numeric](10, 2) NOT NULL ,
[yp_hj] [int] NOT NULL
) ON [PRIMARY]
GO
将表A中的数据汇总到表B中,要求是这样的:
1.表A中的rq=表B中的rq.
2.表A中xl为1的yye数值汇总到表B中为yye1,表Ayp数值汇总到表B中为yp1
表A中xl为3的yye数值汇总到表B中为yye3,表Ayp数值汇总到表B中为yp3
表A中xl为4的yye数值汇总到表B中为yye4,表Ayp数值汇总到表B中为yp4
表A中xl为6的yye数值汇总到表B中为yye6,表Ayp数值汇总到表B中为yp6
表A中xl为7的yye数值汇总到表B中为yye7,表Ayp数值汇总到表B中为yp7
3.表B中的yye_hj=yye1+yye3+yye4+yye6+yye7,yp_hj=yp1+yp3+yp4+yp6+yp7.
我没写出来,请教各位,先表示感谢了!
B.yye3=u.yye3, B.yp3 =u.yp3,
B.yye4=u.yye4, B.yp4 =u.yp4,
B.yye6=u.yye6, B.yp6 =u.yp6,
B.yye7=u.yye7, B.yp7 =u.yp7,
B.yye_hj=u.yye_hj, B.yp_hj =u.yp_hj
from
(Select yye1=sum(case when a.x1=1 then yye else 0 end),
yp1 =sum(case when a.x1=1 then Ayp else 0 end),
yye3=sum(case when a.x1=3 then yye else 0 end),
yp3 =sum(case when a.x1=3 then Ayp else 0 end),
yye4=sum(case when a.x1=4 then yye else 0 end),
yp4 =sum(case when a.x1=4 then Ayp else 0 end),
yye6=sum(case when a.x1=6 then yye else 0 end),
yp6 =sum(case when a.x1=6 then Ayp else 0 end),
yye7=sum(case when a.x1=7 then yye else 0 end),
yp7 =sum(case when a.x1=7 then Ayp else 0 end),
yye_hj=sum(yye),yp_hj =sum(Ayp)
from A where x1 in (1,2,4,6,7) group by x1) as u
inner join B on u.rq=B.rq
INSERT Yy_RyyeCx
SELECT [RQ],
SUM(CASE WHEN XL=1 THEN yye ELSE 0 END) AS [yye1],
SUM(CASE WHEN XL=1 THEN yp ELSE 0 END) AS [yp1],
SUM(CASE WHEN XL=3 THEN yye ELSE 0 END) AS [yye3],
SUM(CASE WHEN XL=3 THEN yp ELSE 0 END) AS [yp3],
SUM(CASE WHEN XL=4 THEN yye ELSE 0 END) AS [yye4],
SUM(CASE WHEN XL=4 THEN yp ELSE 0 END) AS [yp4],
SUM(CASE WHEN XL=6 THEN yye ELSE 0 END) AS [yye6],
SUM(CASE WHEN XL=6 THEN yp ELSE 0 END) AS [yp6],
SUM(CASE WHEN XL=7 THEN yye ELSE 0 END) AS [yye7],
SUM(CASE WHEN XL=7 THEN yp ELSE 0 END) AS [yp7],
SUM(CASE WHEN XL in (1,3,4,6,7) THEN yye ELSE 0 END) AS [yye_hj], --如果xl只有1,3,4,6,7,可以直接写 SUM(yye) AS [yye_hj]
SUM(CASE WHEN XL in (1,3,4,6,7) THEN yp ELSE 0 END) AS [yp_hj] --如果xl只有1,3,4,6,7,可以直接写 SUM(yp) AS [yp_hj]
from yy_Ryye
group by rq
B.yye3=u.yye3, B.yp3 =u.yp3,
B.yye4=u.yye4, B.yp4 =u.yp4,
B.yye6=u.yye6, B.yp6 =u.yp6,
B.yye7=u.yye7, B.yp7 =u.yp7,
B.yye_hj=u.yye_hj, B.yp_hj =u.yp_hj
from
(Select rq,
yye1=sum(case when a.xl=1 then yye else 0 end),
yp1 =sum(case when a.xl=1 then yp else 0 end),
yye3=sum(case when a.xl=3 then yye else 0 end),
yp3 =sum(case when a.xl=3 then yp else 0 end),
yye4=sum(case when a.xl=4 then yye else 0 end),
yp4 =sum(case when a.xl=4 then yp else 0 end),
yye6=sum(case when a.xl=6 then yye else 0 end),
yp6 =sum(case when a.xl=6 then yp else 0 end),
yye7=sum(case when a.xl=7 then yye else 0 end),
yp7 =sum(case when a.xl=7 then yp else 0 end),
yye_hj=sum(yye),yp_hj =sum(yp)
from yy_Ryye as A
where (xl in (1,3,4,6,7)) --如果xl只有固定的这几个值,此条件可以不要
group by rq
) as u
inner join Yy_RyyeCx B on u.rq=B.rq
update Yy_RyyeCx
set [yye1]=t.[yye1],[yp1]=t.[yp1],
[yye3]=t.[yye3],[yp3]=t.[yp3],
[yye4]=t.[yye4],[yp4]=t.[yp4],
[yye6]=t.[yye6],[yp6]=t.[yp6],
[yye7]=t.[yye7],[yp7]=t.[yp7],
[yye_hj]=t.[yye_hj],[yp_hj]=t.[yp_hj]
from Yy_RyyeCx b,(
SELECT [RQ],
SUM(CASE WHEN XL=1 THEN yye ELSE 0 END) AS [yye1],
SUM(CASE WHEN XL=1 THEN yp ELSE 0 END) AS [yp1],
SUM(CASE WHEN XL=3 THEN yye ELSE 0 END) AS [yye3],
SUM(CASE WHEN XL=3 THEN yp ELSE 0 END) AS [yp3],
SUM(CASE WHEN XL=4 THEN yye ELSE 0 END) AS [yye4],
SUM(CASE WHEN XL=4 THEN yp ELSE 0 END) AS [yp4],
SUM(CASE WHEN XL=6 THEN yye ELSE 0 END) AS [yye6],
SUM(CASE WHEN XL=6 THEN yp ELSE 0 END) AS [yp6],
SUM(CASE WHEN XL=7 THEN yye ELSE 0 END) AS [yye7],
SUM(CASE WHEN XL=7 THEN yp ELSE 0 END) AS [yp7],
SUM(CASE WHEN XL in (1,3,4,6,7) THEN yye ELSE 0 END) AS [yye_hj], --如果xl只有1,3,4,6,7,可以直接写 SUM(yye) AS [yye_hj]
SUM(CASE WHEN XL in (1,3,4,6,7) THEN yp ELSE 0 END) AS [yp_hj] --如果xl只有1,3,4,6,7,可以直接写 SUM(yp) AS [yp_hj]
from yy_Ryye
group by rq
) as t
where b.rq=t.rq
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE SP_RyyeCxHz
(@yyrq_S datetime,@yyrq_E datetime) AS
BEGIN TRANSACTION delete from Yy_RyyeCx where rq>=@yyrq_S and rq<=@yyrq_E INSERT Yy_RyyeCx
SELECT [RQ],
SUM(CASE WHEN XL=1 THEN yye ELSE 0 END) AS [yye1],
SUM(CASE WHEN XL=1 THEN yp ELSE 0 END) AS [yp1],
SUM(CASE WHEN XL=3 THEN yye ELSE 0 END) AS [yye3],
SUM(CASE WHEN XL=3 THEN yp ELSE 0 END) AS [yp3],
SUM(CASE WHEN XL=4 THEN yye ELSE 0 END) AS [yye4],
SUM(CASE WHEN XL=4 THEN yp ELSE 0 END) AS [yp4],
SUM(CASE WHEN XL=6 THEN yye ELSE 0 END) AS [yye6],
SUM(CASE WHEN XL=6 THEN yp ELSE 0 END) AS [yp6],
SUM(CASE WHEN XL=7 THEN yye ELSE 0 END) AS [yye7],
SUM(CASE WHEN XL=7 THEN yp ELSE 0 END) AS [yp7],
SUM(CASE WHEN XL in (1,3,4,6,7) THEN yye ELSE 0 END) AS [yye_hj], --如果xl只有1,3,4,6,7,可以直接写 SUM(yye) AS [yye_hj]
SUM(CASE WHEN XL in (1,3,4,6,7) THEN yp ELSE 0 END) AS [yp_hj] --如果xl只有1,3,4,6,7,可以直接写 SUM(yp) AS [yp_hj]
from yy_Ryye where rq>=@yyrq_S and rq<=@yyrq_E
group by rq if @@error <> 0 goto ErrorCOMMIT TRANSACTION
return
error:
begin
print '出错误了,不能生成数据'
rollback TRANSACTION
return
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
+
insert
了
就不必
update
了