我真的很想创建一个视图。
我知道您不能在 MSSQL2005 视图中使用临时表。在不重写 sql 的情况下,我是否遗漏了任何明显的东西?
备份计划是使用存储过程。
Cheers
select * into #temp from vwIncidents
SELECT vwIncidents.incidentcode, employeecode, EMPOS.POS_L4_CDA as areaAtTimeOfIncident
into #temp1
FROM vwIncidents
INNER JOIN EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA
WHERE EMPOS.POS_STARTC < vwIncidents.incidentdate
AND (EMPOS.POS_ENDD > vwIncidents.incidentdate OR EMPOS.POS_ENDD IS NULL)
order by incidentcode
select #temp.*, #temp1.areaAtTimeOfIncident from #temp
left outer join #temp1 on #temp.incidentcode = #temp1.incidentcode
and #temp.employeecode = #temp1.employeecode
order by incidentcode
您可以使用 CTE:
WITH cteIncidents (incidentcode, employeecode, areaAtTimeOfIncident)
AS
(
SELECT
vwIncidents.incidentcode, employeecode, EMPOS.POS_L4_CDA as areaAtTimeOfIncident
FROM
vwIncidents
INNER JOIN EMPOS ON vwIncidents.employeecode = EMPOS.DET_NUMBERA
WHERE EMPOS.POS_STARTC < vwIncidents.incidentdate
AND (EMPOS.POS_ENDD > vwIncidents.incidentdate OR EMPOS.POS_ENDD IS NULL)
)
SELECT
incidentcode, employeecode, areaAtTimeOfIncident
FROM
cteIncidents
left outer join vwIncidents on vwIncidents.incidentcode = cteIncidents.incidentcode
and vwIncidents.employeecode = cteIncidents.employeecode
ORDER BY
incidentcode
(可能需要将连接更改为右侧,但您明白了...)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)