第一张表:id name
1 张三
2 李四
3 王五第二张表:id total (table1)
1 2.5
2 3.5
3 5.5
1 2.9
1 3.6
2 3.9
第三张表: id total (table2)
1 3.5
2 6.7
3 6.4
1 5.5
1 4.5
2 1.5
第四张表: id name total1 total1 总total (table3)
向第二张表和(地三张表) 增加和修改数据时候,一段时间,第四张表就会存储他们处理过的信息, 列 id total1 total2 总 total name
1 (表1的id=1时,total的平均) (表2的id=1时,total的平均) (id=1时,total1*权重+total2*权重) 张三
2 (表1的id=2时,total的平均) (表2的id=2时,total的平均) (id=2时,total1*权重+total2*权重) 李四
table3的列id也能自动的加入进表table3
建议使用多语句表值查询:我可以写一个函数 return一张表结构和表3一样,用与存储表3的信息.表三也要保持信息变更,用与数据的挖掘和分析!希望高手帮助,可以追加分!
1 张三
2 李四
3 王五第二张表:id total (table1)
1 2.5
2 3.5
3 5.5
1 2.9
1 3.6
2 3.9
第三张表: id total (table2)
1 3.5
2 6.7
3 6.4
1 5.5
1 4.5
2 1.5
第四张表: id name total1 total1 总total (table3)
向第二张表和(地三张表) 增加和修改数据时候,一段时间,第四张表就会存储他们处理过的信息, 列 id total1 total2 总 total name
1 (表1的id=1时,total的平均) (表2的id=1时,total的平均) (id=1时,total1*权重+total2*权重) 张三
2 (表1的id=2时,total的平均) (表2的id=2时,total的平均) (id=2时,total1*权重+total2*权重) 李四
table3的列id也能自动的加入进表table3
建议使用多语句表值查询:我可以写一个函数 return一张表结构和表3一样,用与存储表3的信息.表三也要保持信息变更,用与数据的挖掘和分析!希望高手帮助,可以追加分!
Create Function [dbo].[Func_Test]
-------------------------------------------------------------------------------------------------
Returns @temp Table(ID varchar(100),Total1 varchar(100),Total2 varchar(100),col4 varchar(100))
-----------------------------------------------------------------------------------------------
As
Begin
-------------------------------------------------------------------------------------------------
insert into @temp
select a.id,a.total1,b.total2,total1*权重+total2*权重 from
(select id,avg(total) as total1 from TB1
group by id) a,
(select id,avg(total) as total2 from TB2
group by id) b
where a.id=b.id-------------------------------------------------------------------------------------------------
return
-------------------------------------------------------------------------------------------------
End
-- Author: happyflystone
-- Date : 2009-05-24 17:39:53
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: T0
IF OBJECT_ID('T0') IS NOT NULL
DROP TABLE T0
Go
CREATE TABLE T0(id INT,name NVARCHAR(2))
Go
INSERT INTO T0
SELECT 1,'张三' UNION ALL
SELECT 2,'李四' UNION ALL
SELECT 3,'王五'
GO
-- Test Data: T1
IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1
Go
CREATE TABLE T1(id INT,total NUMERIC(12,1))
Go
-- Test Data: T2
IF OBJECT_ID('T2') IS NOT NULL
DROP TABLE T2
Go
CREATE TABLE T2(id INT,total NUMERIC(12,1))
Go-- Test Data: T3
IF OBJECT_ID('T3') IS NOT NULL
DROP TABLE T3
Go
CREATE TABLE T3(id INT,[name] NVARCHAR(11),total1 NUMERIC(12,1),total2 NUMERIC(12,1),总total NUMERIC(12,1))
Go
create trigger tr_t1
on t1
for insert,update
as
begin
if exists(select 1 from t3,inserted i where i.id = t3.id)
update t3
set total1 = b.t ,总total = b.t
from (select id,sum(t) as t
from ( select id , -1 * total as t from deleted
union all
select id,total from inserted) a group by id) b
where t3.id = b.id
else
insert t3
select a.id,[name],sum(total),0, sum(total) as t from t0 a,inserted i
where a.id = i.id
group by a.id,[name]
end
go
create trigger tr_t2
on t2
for insert,update
as
begin
if exists(select 1 from t3,inserted i where i.id = t3.id)
update t3
set total2 = b.t ,总total = b.t
from (select id,sum(t) as t
from ( select id , -1 * total as t from deleted
union all
select id,total from inserted) a group by id) b
where t3.id = b.id
else
insert t3
select a.id,[name],0,sum(total), sum(total) as t from t0 a,inserted i
where a.id = i.id
group by a.id,[name]
end
goINSERT INTO T1
SELECT 1,2.5 UNION ALL
SELECT 2,3.5 UNION ALL
SELECT 3,5.5 UNION ALL
SELECT 1,2.9 UNION ALL
SELECT 1,3.6 UNION ALL
SELECT 2,3.9
INSERT INTO T2
SELECT 1,3.5 UNION ALL
SELECT 2,6.7 UNION ALL
SELECT 3,6.4 UNION ALL
SELECT 1,5.5 UNION ALL
SELECT 1,4.5 UNION ALL
SELECT 2,1.5 SELECT
*
FROM
T3--Result:
/*id name total1 total2 总total
----------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 张三 9.0 13.5 13.5
2 李四 7.4 8.2 8.2
3 王五 5.5 6.4 6.4
*/
--End