我仍然在学习sql和内联视图。我一直在做一个项目，在这个项目中，我需要考虑到某些东西的滞后性，所以可以说，我需要依赖于嵌套的东西。但是我对内联视图的语法还不是很在行，它总是在圆括号、逗号等处抛出错误。有人能帮我清理一下，让它运行吗？
SELECT *
, SiteName
, VentCount AS 'Number Of Vents'
, ROUND(PumpAHours,1) AS 'Pump A Hours'
, ROUND(PumpBHours,1) AS 'Pump B Hours'
, ROUND(PumpAStarts, 1) AS 'Pump A Starts'
, ROUND(PumpBStarts, 1) AS 'Pump B Starts'
FROM (
SELECT * , SiteName
, SUM(CASE WHEN ECV36_Open_Count_Lifetime - Lag_ECV36_Open_Count_Lifetime Between 1 AND 5 THEN ECV36_Open_Count_Lifetime - Lag_ECV36_Open_Count_Lifetime ELSE 0 END) AS 'VentCount'
, CASE WHEN NULLIF(SUM(CASE WHEN PumpAStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT),0) < MAX(Pump_A_Hours) - MIN(Pump_A_Hours)
THEN SUM(CASE WHEN PumpAStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT)
ELSE MAX(Pump_A_Hours) - MIN(Pump_A_Hours) END AS 'PumpAHours'
, CASE WHEN NULLIF(SUM(CASE WHEN PumpBStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT),0) < MAX(Pump_B_Hours) - MIN(Pump_B_Hours)
THEN SUM(CASE WHEN PumpBStatus LIKE '%Running%' THEN 1 ELSE 0 END) / CAST(20 AS FLOAT)
ELSE MAX(Pump_B_Hours) - MIN(Pump_B_Hours) END AS 'PumpBHours'
,COUNT(CASE WHEN Pump_A_StateId = 12 OR Pump_A_StateId = 14 OR Pump_A_StateId = 9 OR Pump_A_StateId = 1837 OR Pump_A_StateId = 1839 THEN 1 END) AS PumpAStarts
,COUNT(CASE WHEN Pump_B_StateId = 12 OR Pump_B_StateId = 14 OR Pump_B_StateId = 9 OR Pump_B_StateId = 1837 OR Pump_B_StateId = 1839 THEN 1 END) AS PumpBStarts
FROM (
SELECT * , SiteName
, LAG([ECV36_Open_Count_Lifetime],1,NULL) OVER(PARTITION BY R.SiteId ORDER BY RecordDateTime ASC) AS 'Lag_ECV36_Open_Count_Lifetime'
FROM (
[Cryo].[dbo].[Record] R
Cryo.dbo.Site S on S.SiteId = R.RecordId
Cryo.dbo.pad P on P.RecordId = R.RecordId
Cryo.dbo.Pump PP on PP.RecordId = R.RecordId
)a
)a
WHERE RecordDateTime BETWEEN DATEADD(DD,-1,CONVERT(DATE,GETDATE())) AND DATEADD(DD, 0,CONVERT(DATE,GETDATE()))
)a编辑：我重新格式化了代码并修复了一些标注。事情仍然不能正常工作，但唯一的错误是我猜子查询别名，如果这是他们所说的？。对于）a和）c，我有语法错误，并且）b说sitename被指定了多次，尽管我只能找到一个。我不知道如何解决这个问题。再说一遍，抱歉代码太乱了，我真的很新鲜。
SELECT * ,
sitename ,
ventcount AS 'Number Of Vents' ,
Round(pumpahours,1) AS 'Pump A Hours' ,
Round(pumpbhours,1) AS 'Pump B Hours' ,
Round(pumpastarts, 1) AS 'Pump A Starts' ,
Round(pumpbstarts, 1) AS 'Pump B Starts'
FROM (
SELECT * ,
sitename ,
Sum(
CASE
WHEN ecv36_open_count_lifetime - lag_ecv36_open_count_lifetime BETWEEN 1 AND 5 THEN ecv36_open_count_lifetime - lag_ecv36_open_count_lifetime
ELSE 0
END) AS 'VentCount' ,
CASE
WHEN NULLIF(Sum(
CASE
WHEN pumpastatus LIKE '%Running%' THEN 1
ELSE 0
END) / Cast(20 AS FLOAT),0) < Max(pump_a_hours) - Min(pump_a_hours) THEN Sum(
CASE
WHEN pumpastatus LIKE '%Running%' THEN 1
ELSE 0
END) / Cast(20 AS FLOAT)
ELSE Max(pump_a_hours) - Min(pump_a_hours)
END AS 'PumpAHours' ,
CASE
WHEN NULLIF(Sum(
CASE
WHEN pumpbstatus LIKE '%Running%' THEN 1
ELSE 0
END) / Cast(20 AS FLOAT),0) < Max(pump_b_hours) - Min(pump_b_hours) THEN Sum(
CASE
WHEN pumpbstatus LIKE '%Running%' THEN 1
ELSE 0
END) / Cast(20 AS FLOAT)
ELSE Max(pump_b_hours) - Min(pump_b_hours)
END AS 'PumpBHours' ,
Count(
CASE
WHEN pump_a_stateid = 12
OR pump_a_stateid = 14
OR pump_a_stateid = 9
OR pump_a_stateid = 1837
OR pump_a_stateid = 1839 THEN 1
END) AS pumpastarts ,
Count(
CASE
WHEN pump_b_stateid = 12
OR pump_b_stateid = 14
OR pump_b_stateid = 9
OR pump_b_stateid = 1837
OR pump_b_stateid = 1839 THEN 1
END) AS pumpbstarts
FROM (
SELECT * ,
s.SiteName ,
Lag([ECV36_Open_Count_Lifetime],1,NULL) OVER(partition BY r.siteid ORDER BY recorddatetime ASC) AS 'Lag_ECV36_Open_Count_Lifetime'
FROM ( [Cryo].[dbo].[Record] R JOIN cryo.dbo.site s ON s.siteid = r.recordid JOIN cryo.dbo.pad p ON p.recordid = r.recordid JOIN cryo.dbo.pump pp ON pp.recordid = r.recordid )a
WHERE recorddatetime BETWEEN dateadd(dd,-1,CONVERT(date,getdate())) AND dateadd(dd, 0,CONVERT(date,getdate())) )b )c
1条答案
按热度按时间whlutmcx1#
我得从改善压痕开始。这样就更容易看到事情是如何排列的：
注意，我并没有像确保嵌套的每个级别的缩进都是正确的那样关心如何使选择区域中的项目看起来漂亮。说完，这一节就跳出来了：
你好像错过了一些连接。不幸的是，我不能为您解决这个问题，因为我们无法知道您是否需要内部连接、左连接、交叉连接等。还怀疑此查询有
*在SELECT条款。我注意到的另一件事是下一级的聚合函数。你不能像这样使用聚合函数
SUM()除非它们是select子句中的唯一字段，否则您有一个GROUPBY子句，或者它们位于具有分区by节的窗口函数（over expression）中。最后，为了使将来更容易管理，我建议将每个级别编写为一个cte（commontableexpression）。这将减少缩进，并且（以我的经验）使查询更容易在心理上进行推理。不必重写所有内容，下面是一个简短的概述，它将是什么样子：
这是我的喜好。有些人更喜欢已有的嵌套视图，因为它允许他们只突出显示一个部分，然后在managementstudio中按f5来运行该部分，这样会使运行更困难，比如说，只运行一个部分
PumpCounts部分。但是，我发现这有助于减少嵌套和使缩进更加一致（所有select/from/where/etc可以处于同一级别）。最重要的是，这允许我们按照您需要的顺序编写查询，以便能够理解它，而另一种方式是您必须从内到外读取内容。