比如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 GOCREATE 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 GOCREATE 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 GOINSERT 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=2SELECT * FROM CDROP 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
GOCREATE 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 GOINSERT 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 ENVIRONMENTDROP 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 ----------- ----------- ----------- (0 件処理されました)ID E ----------- ----------- (0 件処理されました) (1 件処理されました) (1 件処理されました) (1 件処理されました) (1 件処理されました) (1 件処理されました) (1 件処理されました) (1 件処理されました) (1 件処理されました)ID A B ----------- ----------- ----------- 1 5 5 2 5 5(2 件処理されました)ID C D ----------- ----------- ----------- 2 3 5(1 件処理されました)ID E ----------- ----------- 1 10 2 18(2 件処理されました)
insert into c(e) select (a+b+(select c+d from b)) from a这种类型的结构不行吗?
insert into c(e) select (sum(a)+sum(b)) as sum1 from a
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
GOCREATE 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
GOCREATE 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
GOINSERT 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=2SELECT * FROM CDROP PROC P_C
DROP TRIGGER T_A,T_B
DROP TABLE A,B,C
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
GOCREATE 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
GOINSERT 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 ENVIRONMENTDROP 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
----------- ----------- ----------- (0 件処理されました)ID E
----------- ----------- (0 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)ID A B
----------- ----------- -----------
1 5 5
2 5 5(2 件処理されました)ID C D
----------- ----------- -----------
2 3 5(1 件処理されました)ID E
----------- -----------
1 10
2 18(2 件処理されました)