在视图中使用临时表
在视图中使用临时表
提问于 2010-04-26 11:45:13
我真的很想创建一个视图。
我知道你不能在MSSQL2005视图中使用临时表。在不重写sql的情况下，有没有什么明显的遗漏？
后备计划是使用存储的进程。
干杯
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回答 3
Stack Overflow用户
回答已采纳
发布于 2010-04-26 11:50:15
你有没有试过在不使用临时表的情况下重写？
就像这样
select temp.*,
temp1.areaAtTimeOfIncident
from (
select *
from vwIncidents
) temp left outer join
(
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
)
) temp1 on temp.incidentcode = temp1.incidentcode
and temp.employeecode = temp1.employeecode
order by incidentcode Stack Overflow用户
发布于 2010-04-26 11:54:24
您可以使用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 (可能需要将连接更改为右连接，但您明白了……)
Stack Overflow用户
发布于 2010-04-26 11:50:26
使用WITH语句。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2711040
复制相关文章
相似问题
腾讯云开发者
