我有两个选择语句。 一个获取过去60秒内记录的电压数据的列表(如果有的话)和相关的室名,并且一个获得过去5分钟内记录的电弧事件数据的列表(如果有的话)。 我试图将弧计数数据作为新列附加到电压数据表。 我无法弄清楚如何做到这一点。
注意,对于电压数据表中的给定腔室名称,可能存在或可能不存在弧计数行。 如果没有行,我想将弧计数列值设置为零。
有关如何实现这一目标的任何想法?
电压数据:
SELECT DISTINCT dbo.CoatingChambers.Name, AVG(dbo.CoatingGridVoltage_Data.ChanA_DCVolts) AS ChanADC, AVG(dbo.CoatingGridVoltage_Data.ChanB_DCVolts) AS ChanBDC, AVG(dbo.CoatingGridVoltage_Data.ChanA_RFVolts) AS ChanARF, AVG(dbo.CoatingGridVoltage_Data.ChanB_RFVolts) AS ChanBRF FROM dbo.CoatingGridVoltage_Data LEFT OUTER JOIN dbo.CoatingChambers ON dbo.CoatingGridVoltage_Data.CoatingChambersID = dbo.CoatingChambers.CoatingChambersID WHERE (dbo.CoatingGridVoltage_Data.DT > DATEADD(second, - 60, SYSUTCDATETIME())) GROUP BY dbo.CoatingChambers.Name返回
Name | ChanADC | ChanBDC | ChanARF | ChanBRF -----+-------------------+--------------------+---------------------+------------------ OX2 | 2.9099999666214 | -0.485000004371007 | 0.344801843166351 | 0.49748428662618 S2 | 0.100000001490116 | -0.800000016887983 | 0.00690172302226226 | 0.700591623783112 S3 | 4.25666658083598 | 0.5 | 0.96554297208786 | 0.134956782062848弧数表:
SELECT CoatingChambers.Name, SUM(ArcCount) as ArcCount FROM CoatingChambers LEFT JOIN CoatingArc_Data ON dbo.[CoatingArc_Data].CoatingChambersID = dbo.CoatingChambers.CoatingChambersID where EventDT > DATEADD(mi,-5, GETDATE()) Group by Name返回
Name | ArcCount -----+--------- L1 | 283 L4 | 0 L6 | 1 S2 | 55为了清楚起见,我想要这个表(添加了弧计数列),给出上面的两个表:
Name | ChanADC | ChanBDC | ChanARF | ChanBRF | ArcCount -----+-------------------+--------------------+---------------------+-------------------+--------- OX2 | 2.9099999666214 | -0.485000004371007 | 0.344801843166351 | 0.49748428662618 | 0 S2 | 0.100000001490116 | -0.800000016887983 | 0.00690172302226226 | 0.700591623783112 | 55 S3 | 4.25666658083598 | 0.5 | 0.96554297208786 | 0.134956782062848 | 0I have two select statements. One gets a list (if any) of logged voltage data in the past 60 seconds and related chamber names, and one gets a list (if any) of logged arc event data in the past 5 minutes. I am trying to append the arc count data as new columns to the voltage data table. I cannot figure out how to do this.
Note that, there may or may not be arc count rows, for a given chamber name that is in the voltage data table. If there are no rows, I want to set the arc count column value to zero.
Any ideas on how to accomplish this?
Voltage Data:
SELECT DISTINCT dbo.CoatingChambers.Name, AVG(dbo.CoatingGridVoltage_Data.ChanA_DCVolts) AS ChanADC, AVG(dbo.CoatingGridVoltage_Data.ChanB_DCVolts) AS ChanBDC, AVG(dbo.CoatingGridVoltage_Data.ChanA_RFVolts) AS ChanARF, AVG(dbo.CoatingGridVoltage_Data.ChanB_RFVolts) AS ChanBRF FROM dbo.CoatingGridVoltage_Data LEFT OUTER JOIN dbo.CoatingChambers ON dbo.CoatingGridVoltage_Data.CoatingChambersID = dbo.CoatingChambers.CoatingChambersID WHERE (dbo.CoatingGridVoltage_Data.DT > DATEADD(second, - 60, SYSUTCDATETIME())) GROUP BY dbo.CoatingChambers.NameReturns
Name | ChanADC | ChanBDC | ChanARF | ChanBRF -----+-------------------+--------------------+---------------------+------------------ OX2 | 2.9099999666214 | -0.485000004371007 | 0.344801843166351 | 0.49748428662618 S2 | 0.100000001490116 | -0.800000016887983 | 0.00690172302226226 | 0.700591623783112 S3 | 4.25666658083598 | 0.5 | 0.96554297208786 | 0.134956782062848Arc count table:
SELECT CoatingChambers.Name, SUM(ArcCount) as ArcCount FROM CoatingChambers LEFT JOIN CoatingArc_Data ON dbo.[CoatingArc_Data].CoatingChambersID = dbo.CoatingChambers.CoatingChambersID where EventDT > DATEADD(mi,-5, GETDATE()) Group by NameReturns
Name | ArcCount -----+--------- L1 | 283 L4 | 0 L6 | 1 S2 | 55To be clear, I want this table (with added arc count column), given the two tables above:
Name | ChanADC | ChanBDC | ChanARF | ChanBRF | ArcCount -----+-------------------+--------------------+---------------------+-------------------+--------- OX2 | 2.9099999666214 | -0.485000004371007 | 0.344801843166351 | 0.49748428662618 | 0 S2 | 0.100000001490116 | -0.800000016887983 | 0.00690172302226226 | 0.700591623783112 | 55 S3 | 4.25666658083598 | 0.5 | 0.96554297208786 | 0.134956782062848 | 0最满意答案
您可以将select语句视为虚拟表,并将它们连接在一起:
select x.Name, x.ChanADC, x.ChanBDC, x.ChanARF, x.ChanBRF, isnull( y.ArcCount, 0 ) ArcCount from ( select distinct cc.Name, AVG(cgv.ChanA_DCVolts) AS ChanADC, AVG(cgv.ChanB_DCVolts) AS ChanBDC, AVG(cgv.ChanA_RFVolts) AS ChanARF, AVG(cgv.ChanB_RFVolts) AS ChanBRF from dbo.CoatingGridVoltage_Data cgv left outer join dbo.CoatingChambers cc on cgv.CoatingChambersID = cc.CoatingChambersID where cgv.DT > dateadd(second, - 60, sysutcdatetime()) group by cc.Name ) as x left outer join ( select cc.Name, sum(ac.ArcCount) as ArcCount from dbo.CoatingChambers cc left outer join dbo.CoatingArc_Data ac on ac.CoatingChambersID = cc.CoatingChambersID where EventDT > dateadd(mi,-5, getdate()) group by Name ) as y on x.Name = y.Name此外,使用别名简化您的名称并将查询格式化为可读性是值得的...我无耻地采取了一些措施。
You can treat the select statements as virtual tables and just join them together:
select x.Name, x.ChanADC, x.ChanBDC, x.ChanARF, x.ChanBRF, isnull( y.ArcCount, 0 ) ArcCount from ( select distinct cc.Name, AVG(cgv.ChanA_DCVolts) AS ChanADC, AVG(cgv.ChanB_DCVolts) AS ChanBDC, AVG(cgv.ChanA_RFVolts) AS ChanARF, AVG(cgv.ChanB_RFVolts) AS ChanBRF from dbo.CoatingGridVoltage_Data cgv left outer join dbo.CoatingChambers cc on cgv.CoatingChambersID = cc.CoatingChambersID where cgv.DT > dateadd(second, - 60, sysutcdatetime()) group by cc.Name ) as x left outer join ( select cc.Name, sum(ac.ArcCount) as ArcCount from dbo.CoatingChambers cc left outer join dbo.CoatingArc_Data ac on ac.CoatingChambersID = cc.CoatingChambersID where EventDT > dateadd(mi,-5, getdate()) group by Name ) as y on x.Name = y.NameAlso, it's worthwhile to simplify your names with aliases and format the queries for readability...which I shamelessly took a stab at.
更多推荐
发布评论