视图的数据是根据基本表计算而得,为了计算方便,我想再把视图的数据插入到另一张新表,请问:这张新表内容可以根据基本表并通过视图而自动更新吗.如果可以直按用INTO就可以了吗.
create view vv_管理
as
Select
N'一月份'As 月份,
IsNull(SUM(view1),0) As view1,
IsNull(SUM(view2),0) As view2,
IsNull(SUM(view3),0) As view3,
IsNull(SUM(hetonge1),0) As hetonge1,
IsNull(SUM(view4),0) As view4
From v_管理
Where Month(liulan1)=1 And Year(liulan1)=Year(GetDate())
如我想把以上视图计算的数据放入到另一张新表,得如何做啊.可以自动更新吗.
大家有时间帮看下,谢谢!
create view vv_管理
as
Select
N'一月份'As 月份,
IsNull(SUM(view1),0) As view1,
IsNull(SUM(view2),0) As view2,
IsNull(SUM(view3),0) As view3,
IsNull(SUM(hetonge1),0) As hetonge1,
IsNull(SUM(view4),0) As view4
From v_管理
Where Month(liulan1)=1 And Year(liulan1)=Year(GetDate())
如我想把以上视图计算的数据放入到另一张新表,得如何做啊.可以自动更新吗.
大家有时间帮看下,谢谢!
2、用新表的必要性并不大
insert tablename (...)
Select
N'一月份'As 月份,
IsNull(SUM(view1),0) As view1,
IsNull(SUM(view2),0) As view2,
IsNull(SUM(view3),0) As view3,
IsNull(SUM(hetonge1),0) As hetonge1,
IsNull(SUM(view4),0) As view4
From v_管理
Where Month(liulan1)=1 And Year(liulan1)=Year(GetDate())
插入数据
视图:月份 view1 view2 view3 hetonge1 view4
目标表单:aa view1 bb view3 hetonge1 view4
其中:aa为人工输入,bb=aa-view1
为了计算方便,我把基本表统计出的数据放入到视图中,再把视图的数据插入到另一张表中(不知道这么设计是否合理,插入表数据是否能更新)。
问题1.为什么目标表单中的aa,bb字段不允许插入呢。
大家帮看下啊,谢谢了!
谢谢!
(Select
N'一月份'As 月份,
IsNull(SUM(view1),0) As view1,
IsNull(SUM(view2),0) As view2,
IsNull(SUM(view3),0) As view3,
IsNull(SUM(hetonge1),0) As hetonge1,
IsNull(SUM(view4),0) As view4
From v_管理
Where Month(liulan1)=1 And Year(liulan1)=Year(GetDate()))
大家帮看下这段代码哪里有问题,目的是把视图V_管理的数据插入到表AA,并把0插入到字段addd1里.
N'一月份'As 月份,
IsNull(SUM(view1),0) As view1,
IsNull(SUM(view2),0) As view2,
IsNull(SUM(view3),0) As view3,
IsNull(SUM(hetonge1),0) As hetonge1,
IsNull(SUM(view4),0) As view4,
0 As addd1
From v_管理
Where Month(liulan1)=1 And Year(liulan1)=Year(GetDate())但是字段名要一一對應上
如果表AA不存在Select
N'一月份'As 月份,
IsNull(SUM(view1),0) As view1,
IsNull(SUM(view2),0) As view2,
IsNull(SUM(view3),0) As view3,
IsNull(SUM(hetonge1),0) As hetonge1,
IsNull(SUM(view4),0) As view4,
0 As addd1
Into AA
From v_管理
Where Month(liulan1)=1 And Year(liulan1)=Year(GetDate())