SQL union / join / intersect多个select语句(SQL union / join / intersect multiple select statements)

我有两个选择语句。一个获取过去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 | 0

I 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.Name

Returns

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

Arc 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 Name

Returns

Name | ArcCount -----+--------- L1 | 283 L4 | 0 L6 | 1 S2 | 55

To 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.Name

Also, it's worthwhile to simplify your names with aliases and format the queries for readability...which I shamelessly took a stab at.

更多推荐