怎样将几个字段值的汇总，写入到另一个表中呀？
怎样将几个字段值的汇总，写入到另一个表中呀？
就是在一个表中的字段存入另几个表中的几个字段的汇总。
比如A表中的　a字段　b字段　和B表中的c字段　d字段　的和存入C表中的e字段呀？

------解决方案--------------------
比如A表中的　a字段　b字段　和B表中的c字段　d字段　的和存入C表中的e字段呀？
insert into c(e) select (sum(a)+sum(b)) as sum1 from a
------解决方案--------------------
insert into c(e) select (a+b+(select c+d from b)) from a
------解决方案--------------------
自动的，得用触发器
------解决方案--------------------
-- 比如A表中的　a字段　b字段　和B表中的c字段　d字段　的和存入C表中的e字段呀？
CREATE TABLE A(A INT,B INT)
SELECT * FROM A
GO
CREATE TABLE B(C INT,D INT)
SELECT * FROM B
GO
CREATE TABLE C(E INT)
SELECT * FROM C
GO

CREATE PROC P_C
AS
BEGIN
DECLARE @RESULT INT
SELECT @RESULT=(SELECT ISNULL(SUM(A),0)+ISNULL(SUM(B),0) FROM A) + (SELECT ISNULL(SUM(C),0)+ISNULL(SUM(D),0) FROM B)

IF EXISTS(SELECT 1 FROM C)
UPDATE C SET E=@RESULT
ELSE
INSERT C(E) SELECT @RESULT
END
GO

CREATE TRIGGER T_A ON A FOR INSERT,UPDATE,DELETE
AS
BEGIN
EXEC P_C
END
GO
CREATE TRIGGER T_B ON B FOR INSERT,UPDATE,DELETE
AS
BEGIN
EXEC P_C
END
GO

INSERT A(A,B) SELECT 1,5
INSERT A(A,B) SELECT 2,5
INSERT B(C,D) SELECT 2,3
--DELETE FROM A WHERE A=2

SELECT * FROM C

DROP PROC P_C
DROP TRIGGER T_A,T_B
DROP TABLE A,B,C
------解决方案--------------------
----------CREATE TEST ENVIRONMENT------

CREATE TABLE TABLE_A([ID] INT, A INT, B INT)
SELECT * FROM TABLE_A
GO
CREATE TABLE TABLE_B([ID] INT, C INT, D INT)
SELECT * FROM TABLE_B
GO
CREATE TABLE TABLE_C([ID] INT, E INT)
SELECT * FROM TABLE_C
GO

-------SQL---------

CREATE PROC P_C
AS
BEGIN

DECLARE @E INT
DECLARE @ID INT
DECLARE CUR_TEST CURSOR
FOR
SELECT ISNULL(TABLE_A.[ID], TABLE_B.[ID])
,(ISNULL(A, 0)+ISNULL(B, 0) + ISNULL(C,0) + ISNULL(D,0))

FROM TABLE_A FULL JOIN TABLE_B
ON TABLE_A.[ID] = TABLE_B.[ID]

OPEN CUR_TEST

FETCH NEXT FROM CUR_TEST
INTO @ID, @E

WHILE @@FETCH_STATUS = 0
BEGIN

IF EXISTS(SELECT 1 FROM TABLE_C WHERE [ID] = @ID)
UPDATE TABLE_C SET E=@E WHERE [ID] = @ID
ELSE
INSERT TABLE_C SELECT @ID, @E



FETCH NEXT FROM CUR_TEST
INTO @ID, @E
END

CLOSE CUR_TEST
DEALLOCATE CUR_TEST
END

GO

CREATE TRIGGER T_A ON TABLE_A FOR INSERT,UPDATE,DELETE
AS
BEGIN
EXEC P_C
END
GO
CREATE TRIGGER T_B ON TABLE_B FOR INSERT,UPDATE,DELETE
AS
BEGIN
EXEC P_C
END
GO

INSERT TABLE_A SELECT 1,5,5
INSERT TABLE_A SELECT 2,5,5
INSERT TABLE_B SELECT 2,3,5

-------CHECK ----
SELECT * FROM TABLE_A
SELECT * FROM TABLE_B
SELECT * FROM TABLE_C
GO

--------DELETE TEST ENVIRONMENT

DROP PROC P_C
DROP TRIGGER T_A,T_B
DROP TABLE TABLE_A,TABLE_B,TABLE_C

---------RESULT
ID A B
----------- ----------- -----------

(0 件処理されました)

ID C D