视图带参？
CREATE OR REPLACE VIEW V_DATMT_DNPORT_QE_VLANID
(dnportid, vlanid)
AS
SELECT DISTINCT DNPORT.ID, MIN(DNVLAN.VLANID)
FROM DNPORT, DNPORT_DNVLAN, DNVLAN, DNDEVICE, DNDEVICETYPE
WHERE (DNPORT.ID = DNPORT_DNVLAN.DNPORTID)
AND (DNDEVICE.ID = DNPORT.DNDEVICEID)
AND (DNDEVICETYPE.ID = DNDEVICE.DNDEVICETYPEID)
AND (DNVLAN.ID = DNPORT_DNVLAN.DNVLANID)
AND (DNPORT.ISTEMPLATE = 0)
AND (DNDEVICE.ISTEMPLATE = 0)
--AND (DNDEVICETYPE.CATEGORY IN (4, 5)) --4: DSLAM, 5: 路由器
GROUP BY DNPORT.ID
其中(dnportid, vlanid)这两个参数什么意思啊 去掉了 就报错 说”必须使用列别名表示MIN(DNVLAN.VLANID)表达式“?
[解决办法]
并且为表达式指定一个别名做为视图的字段名，这很好理解
CREATE OR REPLACE VIEW V_DATMT_DNPORT_QE_VLANID
as
SELECT DISTINCT DNPORT.ID, MIN(DNVLAN.VLANID) NEW_VLANID
FROM DNPORT, DNPORT_DNVLAN, DNVLAN, DNDEVICE, DNDEVICETYPE
WHERE (DNPORT.ID = DNPORT_DNVLAN.DNPORTID)
AND (DNDEVICE.ID = DNPORT.DNDEVICEID)
AND (DNDEVICETYPE.ID = DNDEVICE.DNDEVICETYPEID)
AND (DNVLAN.ID = DNPORT_DNVLAN.DNVLANID)
AND (DNPORT.ISTEMPLATE = 0)
AND (DNDEVICE.ISTEMPLATE = 0)
--AND (DNDEVICETYPE.CATEGORY IN (4, 5)) --4: DSLAM, 5: 路由器
GROUP BY DNPORT.ID
[解决办法]
CREATE OR REPLACE VIEW V_DATMT_DNPORT_QE_VLANID
(dnportid, vlanid)
为视图提供了字段名dnportid和vlanid
如果去掉(dnportid, vlanid)的话，则字段名从查询结果过取，MIN(DNVLAN.VLANID)不能作为字段名，所以出错。可以使用列别名解决
