求教一条汇总的SQL语句？

[softheaded]

表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]
GO

CREATE 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]
GO

CREATE 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.
我没写出来，请教各位，先表示感谢了!

[splory]

动态查询加行转列
但不想搞,有点烦

[WangZWang]

update B set B.yye1=u.yye1, B.yp1 =u.yp1,
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

[pao1uo]

--插入
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

[WangZWang]

update B set B.yye1=u.yye1, B.yp1 =u.yp1,
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

[pao1uo]

--更新
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

[softheaded]

非常感谢二位，我写成如下的存储过程了，请问我插入和更新如何写到一个存储过程里呢？

SET QUOTED_IDENTIFIER OFF
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 Error

COMMIT TRANSACTION
return

error:
begin
print '出错误了，不能生成数据'
rollback TRANSACTION
return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[pao1uo]

delete
+
insert
了
就不必
update
了

[softheaded]

非常感谢!还有一个问题，和此类似，我再开一个贴子!

[killxtt]

不知道搂主的意思是不是更新刚插入的数据，如果有这个意思，就要取得刚插入的记录的ID了。