sql - 如何使用计算列计算同一查询中使用子查询的另一列
问题描述
我正在尝试根据以下查询中的 NLength 和 NSpeedLimit 字段添加一个新的计算字段(TravelTime)
SELECT
sc.OBJECTID,
sn.Name,
case when hn.side = 'Right Side' then ''
else sc.LCity
end as LCity,
case when hn.side = 'Left Side' then ''
else sc.RCity
end as RCity,
case when hn.side = 'Right Side' then ''
else sc.LZip
end as LZip,
case when hn.side = 'Left Side' then ''
else sc.RZip
end as RZip,
sc.SHAPE.STLength() AS NLength,
ISNULL(sc.SpeedLimit,1) AS NSpeedLimit
FROM STREETNAME AS sn
INNER JOIN
STREETHASSTREETNAME AS hn ON
sn.GlobalID = hn.GlobalID AND
hn.Role = 'Primary'
INNER JOIN STREETCENTERLINE AS sc ON
hn.GlobalID = sc.GlobalID
新的计算字段是 TravelTime = NLength/(NSpeedLimit*88) 但我无法NLength/(NSpeedLimit*88) AS TravelTime
在 select 语句中添加。我知道我需要做一个子查询,但我不知道它应该去哪里。
解决方案
您可以使用 Temp_tables、派生表或公用表表达式 (CTE) 来获取结果。简单的方法是派生表,因为您不需要更多的编码。
SELECT A.*
, A.NLength/(A.NSpeedLimit * 88) as [TravelTime]
FROM
(
SELECT
sc.OBJECTID,
sn.Name,
case when hn.side = 'Right Side' then ''
else sc.LCity
end as LCity,
case when hn.side = 'Left Side' then ''
else sc.RCity
end as RCity,
case when hn.side = 'Right Side' then ''
else sc.LZip
end as LZip,
case when hn.side = 'Left Side' then ''
else sc.RZip
end as RZip,
sc.SHAPE.STLength() AS NLength,
ISNULL(sc.SpeedLimit,1) AS NSpeedLimit
FROM STREETNAME AS sn
INNER JOIN
STREETHASSTREETNAME AS hn ON
sn.GlobalID = hn.GlobalID AND
hn.Role = 'Primary'
INNER JOIN STREETCENTERLINE AS sc ON
hn.GlobalID = sc.GlobalID
) AS A
推荐阅读
- python - 带有两个数据框的 Dask 折叠
- node.js - 关于使用 SSO Tile 保护关键云代工厂中的 vue js 前端应用程序和节点 js 后端应用程序
- discord.py - Discord.py 机器人重复命令
- python - 加速元胞自动机的绘图
- python - 解析Json响应值Python
- javascript - 将数据从 JSON 输出到模态
- javascript - 节点:`fast-sort` 包 - 排序不是函数
- javascript - 在Angular中使用Input传递数据时如何处理未定义?
- android - 在 Jetpack Compose 中的分配和委派中使用记住有什么区别?
- reactjs - 如何在 React 中独立选择多个选项卡