A表如下:
ID Month Event
PC123 1 sss
PC132 1 aaa
PC131 2 CCC
PC123 2 SSS
PC123 2 aaa----------------------------------
B表"列"如下:
ID 1 2
PC123 1 2
PC132 1 0
PC131 0 1要求如下:当在A表插入数据时
1.如果B表"ID"列没有A表插入的ID值,将该ID值插入,并在该B表的对应的月份列加1.
2.如果B表"ID"列已经存在A表插入的ID值,在该B表的对应的月份里统计该ID在该月的出现次数.
ID Month Event
PC123 1 sss
PC132 1 aaa
PC131 2 CCC
PC123 2 SSS
PC123 2 aaa----------------------------------
B表"列"如下:
ID 1 2
PC123 1 2
PC132 1 0
PC131 0 1要求如下:当在A表插入数据时
1.如果B表"ID"列没有A表插入的ID值,将该ID值插入,并在该B表的对应的月份列加1.
2.如果B表"ID"列已经存在A表插入的ID值,在该B表的对应的月份里统计该ID在该月的出现次数.
--建立測試環境
Create Table A
(ID Varchar(10),
[Month] Int,
Event Varchar(20))
Create table B
(ID Varchar(10),
[1] Int,
[2] Int)
GO
--建立觸發器
Create Trigger Update_B On A
For Insert
As
Begin
Update B Set [1] = B.[1] + A.[1], [2] = B.[2] + A.[2] From B Inner Join
(Select ID, SUM(Case [Month] When 1 Then 1 Else 0 End) As [1], SUM(Case [Month] When 2 Then 1 Else 0 End) As [2] From Inserted Group By ID) A
On A.ID = B.ID Insert B Select ID, SUM(Case [Month] When 1 Then 1 Else 0 End) As [1], SUM(Case [Month] When 2 Then 1 Else 0 End) As [2] From Inserted
Where ID Not In (Select ID From B) Group By ID
End
GO
--測試
Insert A Select 'PC123', 1, 'sss'
Union All Select 'PC132', 1, 'aaa'
Union All Select 'PC131', 2, 'CCC'
Union All Select 'PC123', 2, 'SSS'Insert A Select 'PC123', 2, 'aaa' Select * From B
GO
--刪除測試環境
Drop Table A, B
--結果
/*
ID 1 2
PC123 1 2
PC131 0 1
PC132 1 0
*/
按照ID分月統計出紀錄數Update B Set [1] = B.[1] + A.[1], [2] = B.[2] + A.[2] From B Inner Join
(Select ID, SUM(Case [Month] When 1 Then 1 Else 0 End) As [1], SUM(Case [Month] When 2 Then 1 Else 0 End) As [2] From Inserted Group By ID) A
On A.ID = B.ID這就是按照兩表關聯,將統計出來的紀錄數插入到B表中,前提就是B表中存在這些IDInsert B Select ID, SUM(Case [Month] When 1 Then 1 Else 0 End) As [1], SUM(Case [Month] When 2 Then 1 Else 0 End) As [2] From Inserted
Where ID Not In (Select ID From B) Group By ID這就是將剛統計出來的數據,在B表中不存在的ID插入到B表中。