另一个子查询使用从子查询的列和选择子查询
问题描述:
多列,请看看下面的SQL查询:另一个子查询使用从子查询的列和选择子查询
我想存储在@startlog
列值log_LogId
(6号线),这样我可以稍后在第15行使用它。还是有任何其他方式来实现这一目标?
我不能写在一行6 @log_LogId = log_LogId
它显示语法错误。
另一个问题是,我怎么能在第一子查询中选择多个列?也许喜欢 (SELECT TOP 1 log_LogId, log_Cost .....
?
在此先感谢。
DECLARE @startlog INT
SELECT
log_VehicleId AS gpsId,
log_LogId AS ignOnLogId,
(SELECT TOP 1
log_LogId ---Set this value to @startlog so that I can use this in "HERE"
--- Also Select another column here
FROM VehicleLog
WHERE log_Eventid = 6012 AND log_LogId > v2.log_LogId
AND log_VehicleId = v2.log_VehicleId
) ignOffLogId,
(SELECT SUM(CAST(log_COG AS FLOAT))
FROM VehicleLog
WHERE log_LogId >= v2.log_LogId
AND log_LogId <= @startlog ---**HERE**
)
from VehicleLog v2
WHERE log_Eventid = 6011 AND log_LogId < 300
答
可以使用CTE(公共表表达式)如下:
;WITH CTE_V2 AS
(
SELECT
log_VehicleId
, log_LogId
FROM
VehicleLog
WHERE
log_Eventid = 6011 AND log_LogId < 300
)
, CTE_IGN_OFF AS
(
SELECT TOP 1
log_LogId ---Set this value to @startlog so that I can use this in "HERE"
,log_OtherColumn --- Also Select another column here
FROM
VehicleLog
WHERE
log_Eventid = 6012
AND log_LogId > (SELECT MAX(CTE_V2.log_LogId)
FROM CTE_V2 WHERE VehicleId = CTE_V2.log_VehicleId)
)
, CTE_COG AS
(
SELECT
log_VehicleId AS gpsId
, log_LogId AS ignOnLogId
, SUM(CAST(log_COG AS FLOAT)) sum_COG
FROM
VehicleLog
WHERE
log_LogId >= (SELECT MAX(CTE_V2.log_LogId)
FROM CTE_V2 WHERE VehicleId = CTE_V2.log_VehicleId)
AND log_LogId <= (SELECT TOP 1 log_OtherColumn FROM CTE_IGN_OFF)
GROUP BY
log_VehicleId
, log_LogId
)
SELECT * FROM CTE_COG;
嗨,它显示错误:'消息207,级别16,状态1,行32 无效的列名称log_VehicleId ”。 消息207,级别16,状态1,行31 无效的列名称log_LogId'.'虽然在数据库 – sha256 2013-04-23 21:52:24
@ sha404对不起存在的专栏中,我我们它在firts更名为其它列CTE,因此它可以在第二个引用第一个的地方找不到它。请参阅上面的更新回答。 – Charl 2013-04-24 07:13:43
嘿,感谢您的答复,但仍然得到错误:'消息8155,级别16,状态2,第35行 没有列名的CTE_COG'.' – sha256 2013-04-24 12:44:19