1 原来我一直认为视图纯粹是sql语句的集合,但是现在看来,视图也是会被编译的。直接看例子。
首先 我定义了视图V_sh_MainInfoSub,代码如下
CREATE view V_sh_MainInfoSub //主要是几个表的联合查询,没有什么复杂的操作
as
select
m1.ma_id,
CONVERT(varchar(20), m1.ma_rep_datetime, 23) as ma_rep_datetime,
--(case ma_isOK when 1 then '已解决' when 2 then '待解决' else '未知' end) as 影响排序,暂时不用
m1.ma_isOK,
m1.ma_appear, m1.ma_link, d.dev_pos_detail,
(select i.item_info from sh_item i where i.item_order = d.dev_type
and i.item_type_name = 'dev_type' and item_valid = 1) as dev_type,
(select i.item_info from sh_item i where i.item_order = d.dev_spec
and i.item_type_name = 'dev_spec' and item_valid = 1) as dev_spec,
m1.ma_dev_GUID,--ma_dev_GUID和ma_order不用来显示到页面,
--当用户更新MainInfo时,先通过id找到临时表内容,然后通过ma_dev_GUID和ma_order找到sh_maintain的内容
m1.ma_order
from sh_maintain m1, sh_device d
where m1.ma_dev_GUID = d.dev_GUID
and d.dev_valid = 1
and m1.ma_valid = 1
2 然后定义视图 V_sh_MainInfo 注意比上面的名字少了sub,内容如下
CREATE view V_sh_MainInfo //调用了V_sh_MainInfoSub 。主要功能是为查询结果动态创建连续的id号
as
select
(select count(*) from V_sh_MainInfoSub v2 where v2.ma_id <= v1.ma_id) as id,
* from V_sh_MainInfoSub v1
上面两个视图运行良好,没有任何问题。
3 然后,由于需要,在sub视图中增加了个别字段,修改之后的代码如下
CREATE view V_sh_MainInfoSub //主要是几个表的联合查询,没有什么复杂的操作
as
select
m1.ma_id,
CONVERT(varchar(20), m1.ma_rep_datetime, 23) as ma_rep_datetime,
--(case ma_isOK when 1 then '已解决' when 2 then '待解决' else '未知' end) as 影响排序,暂时不用
m1.ma_isOK,
m1.ma_appear, m1.ma_link, d.dev_pos_detail,
(select i.item_info from sh_item i where i.item_order = d.dev_type
and i.item_type_name = 'dev_type' and item_valid = 1) as dev_type,
(select i.item_info from sh_item i where i.item_order = d.dev_spec
and i.item_type_name = 'dev_spec' and item_valid = 1) as dev_spec,
m1.ma_dev_GUID,--ma_dev_GUID和ma_order不用来显示到页面,
--当用户更新MainInfo时,先通过id找到临时表内容,然后通过ma_dev_GUID和ma_order找到sh_maintain的内容
m1.ma_order,
p.province as dev_province,
c.city as dev_city,
a.area as dev_area
from sh_maintain m1, sh_device d, sh_province p, sh_city c, sh_area a
where m1.ma_dev_GUID = d.dev_GUID
and d.dev_valid = 1
and m1.ma_valid = 1
and p.provinceID = dev_province
and c.cityID = dev_city
and a.areaID = dev_area
and p.provinceID = c.father
and c.cityID = a.father
怪事出现了,单独运行sub视图没问题,运行V_sh_MainInfo 里面的语句也没问题。注意是视图里的语句
select
(select count(*) from V_sh_MainInfoSub v2 where v2.ma_id <= v1.ma_id) as id,
* from V_sh_MainInfoSub v1
但是只要运行 exec V_sh_MainInfo 就是看不到最后追加的三个字段dev_province ,dev_city,dev_area,
最后把V_sh_MainInfo 视图删除,重建,哎好了。
因此怀疑视图也是经过了编译了,以上是个人见解,欢迎批评指正