我试图仅根据两列中子字符串的标准显示最新记录。
SELECT convert(varchar, DateTime, 100) AS "Date and Time"
,Text2 AS Area
,Substring(Text1, 1,Charindex('/', Text1)-1) AS Device
,(Substring(Text1, Charindex('/', Text1)+1, LEN(Text1)) + Substring(PText6, Charindex(' ', PText6)+1, LEN(PText6))) AS Interlock
,Substring(PText7, 1,Charindex(':', PText7)-1) AS Username
FROM a_databse.dbo.a_view AS data
WHERE PText6 LIKE '%BypIn%' AND DateTime = (SELECT MAX(DateTime) FROM
a_database.dbo.a_view WHERE Text1 = data.Text1 AND Interlock = data.Interlock AND PText6 LIKE 'Set%')
在最后一行
Interlock = data.Interlock
由于列名“ Interlock”无效，因此需要替换以进行正确的分组。但是，它将仅接受真实的列引用，例如：
Text1 = data.Text1
但是使用子字符串：
Substring(PText6, Charindex(' ', PText6)+1, LEN(PText6)) = data.Substring(PText6, Charindex(' ', PText6)+1, LEN(PText6))
会抛出错误：
Cannot find either column "data" or the user-defined function or aggregate "data.Substring", or the name is ambiguous
您需要正确地复制粘贴所有表达式，例如data.SUBSTRING(PText6, ...)应该是SUBSTRING(data.PText6, ...)。但是您可以使用CROSS APPLY以下命令简化查询：
SELECT CONVERT(varchar, DateTime, 100) AS "Date and Time"
, Text2 AS Area
, data_vars.Device
, data_vars.Interlock
, data_vars.Username
FROM a_databse.dbo.a_view AS data
CROSS APPLY (
SELECT SUBSTRING(Text1, 1, CHARINDEX('/', Text1) - 1) AS Device
, SUBSTRING(Text1, CHARINDEX('/', Text1) + 1, LEN(Text1)) + SUBSTRING(PText6, CHARINDEX(' ', PText6) + 1, LEN(PText6)) AS Interlock
, SUBSTRING(PText7, 1, CHARINDEX(':', PText7) - 1) AS Username
) AS data_vars
-- all columns inside data_vars are now accessible inside WHERE, GROUP BY, SELECT and ORDER BY
WHERE PText6 LIKE '%BypIn%' AND DateTime = (
SELECT MAX(DateTime)
FROM a_database.dbo.a_view
WHERE Text1 = data.Text1 AND Interlock = data_vars.Interlock AND PText6 LIKE 'Set%'
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句