用户日志统计 表A
id userid blogCount
1 100 0
2 101 0
3 102 0
日志表 表B
id userid Title
1 100 日志1
2 102 日志1
3 100 日志1
4 101 日志1
5 101 日志1
6 102 日志1
7 102 日志1
8 101 日志1
9 102 日志1
结果
用户统计 表A
id userid blogCount
1 100 2
2 101 3
3 102 4
需求 统计每个用户的日志总数 相应的插入统计表中
小弟 之前想的是 循环统计表 每条对应用户ID 去统计
请教大师们 有什么性能好的便利的sql。。 求学习
------解决方案--------------------
- SQL code
--> --> (Roy)生成測試數據 if not object_id('A') is null drop table AGoCreate table A([id] int identity,[userid] int,[blogCount] int)Insert Aselect 100,0 union allselect 101,0 union allselect 102,0Go--> --> (Roy)生成測試數據 if not object_id('B') is null drop table BGoCreate table B([id] int,[userid] int,[Title] nvarchar(3))goCreate trigger tr_B_insert on B for insertasset nocount on ;beginupdate Aset [blogCount]=a.[blogCount]+b.confrom(select [userid],count(ID) as con from inserted group by [userid]) as Binner join A on b.[userid]=a.[userid]insert Aselect b.*from(select [userid],count(ID) as con from inserted group by [userid]) as Bleft join A on b.[userid]=a.[userid]where a.[userid] is nullendgo--测试Insert Bselect 1,100,N'日志1' union allselect 2,102,N'日志1' union allselect 3,100,N'日志1' union allselect 4,101,N'日志1' union allselect 5,101,N'日志1' union allselect 6,102,N'日志1' union allselect 7,102,N'日志1' union allselect 8,101,N'日志1' union allselect 9,102,N'日志1'GoSelect * from A/*id userid blogCount1 100 22 101 33 102 4*/
------解决方案--------------------
- SQL code
update t set blogCount=(select count(*) from 表B where userid=t.userid) from 表A t