将两个查询合并为一个

问题描述：

我有两个正在运行的查询。查询1对一个大表的过滤器，准确地返回我需要的数据，它看起来像这样：将两个查询合并为一个

/****** QUERY #1 - This query will filter the data ******/ 
    SELECT [WacnId], 
    [StartDT] 
    ,[EndDT] 
    ,[Group] 
    ,[ID_Agency] 
    ,[TargetUnit_Agency], 


    case [Group] 
    when 1 then 'in' 
    when 0 then 'out' 
    end as traffic 

FROM [GW_20140315].[dbo].[ARC_Calls_ReportView] 

WHERE [GroupDisplayID] = 'T802149' OR [ID_Agency] = 'Dispatch' or [TargetUnit_Agency] = 'Dispatch' 
order by StartDT

查询＃2行为从查询1过滤后的数据，并产生一个1/2每小时报告。查询2如下所示：

/******Query #2- This query will take the filtered data and process it as needed ******/ 
SELECT dateadd(mi, (datediff(mi, 0, StartDT)/30) * 30, 0) as HalfHour 
    , sum(DATEDIFF (s , [StartDT] , [EndDT])) as [Total Time (Seconds)], 
    SUM(CASE WHEN [TargetUnit_Agency] = 'Dispatch' then 1 ELSE 0 END) AS InCount, 
SUM(CASE WHEN [ID_Agency] = 'Dispatch' then 1 ELSE 0 END) AS OutCount 
FROM [Radio].[dbo].[Filter_Data]--This is how I did it before, but now I want to combine the two queries 
GROUP BY dateadd(mi, (datediff(mi, 0, StartDT)/30) * 30, 0) 
ORDER BY 1

我该如何将这两个查询合并为一个？

Is [Radio]。[dbo]。[Filter_Data]临时表或视图还是子查询？ – Fenistil 2014-09-01 22:33:03

你想把'UNION'（把它们垂直连接在一起作为两个列表）或者将它们关联到某个事物上（连接它们）。您需要提供一些示例输入和输出数据。 – 2014-09-01 22:39:50

只需用方括号中的查询1代替'[Radio]。[dbo]。[Filter_Data]'，您就很好 – cha 2014-09-01 22:41:22

答

你可以使用一个CTE来形容您的过滤后的数据（第一查询），然后查询中使用CTE作为主表（第二查询）：

;WITH FilteredCTE AS 
(
    SELECT [WacnId], 
    [StartDT] 
    ,[EndDT] 
    ,[Group] 
    ,[ID_Agency] 
    ,[TargetUnit_Agency], 
    case [Group] 
     when 1 then 'in' 
     when 0 then 'out' 
    end as traffic 
    FROM [GW_20140315].[dbo].[ARC_Calls_ReportView] 
    WHERE [GroupDisplayID] = 'T802149' 
     OR [ID_Agency] = 'Dispatch' 
     or [TargetUnit_Agency] = 'Dispatch' 
) 
SELECT dateadd(mi, (datediff(mi, 0, StartDT)/30) * 30, 0) as HalfHour, 
sum(DATEDIFF (s , [StartDT] , [EndDT])) as [Total Time (Seconds)], 
SUM(CASE WHEN [TargetUnit_Agency] = 'Dispatch' then 1 ELSE 0 END) AS InCount, 
SUM(CASE WHEN [ID_Agency] = 'Dispatch' then 1 ELSE 0 END) AS OutCount 
FROM FilteredCTE 
GROUP BY dateadd(mi, (datediff(mi, 0, StartDT)/30) * 30, 0) 
ORDER BY StartDT

这就是我所需要的，但是它在用StartDT命令行执行时遇到了问题，所以我将它移除了，它运行良好且有趣，表由StartDT排序，这很好，但我确实需要确保始终发生。我需要在某处添加StartDT的订单吗？ – eltel2910 2014-09-01 23:56:06

@ eltel2910这是我的不好，你可能没有在CTE定义内订购。您需要在最终选择中定义订单。 – 2014-09-02 00:07:03

@Paraskevopoulos，我的理由是通过它，但没有你的帮助我无法做到。谢谢。 – eltel2910 2014-09-02 00:10:52

答

只需选择查询1 FROM QUERY2：

SELECT Dateadd(mi, (Datediff(mi, 0, startdt)/30) * 30, 0) AS HalfHour, 
     Sum(Datediff (s, [startdt], [enddt]))     AS 
     [Total Time (Seconds)], 
     Sum(CASE 
      WHEN [targetunit_agency] = 'Dispatch' THEN 1 
      ELSE 0 
      end)            AS InCount, 
     Sum(CASE 
      WHEN [id_agency] = 'Dispatch' THEN 1 
      ELSE 0 
      end)            AS OutCount 
FROM (SELECT [wacnid], 
       [startdt], 
       [enddt], 
       [group], 
       [id_agency], 
       [targetunit_agency], 
       CASE [group] 
       WHEN 1 THEN 'in' 
       WHEN 0 THEN 'out' 
       end AS traffic 
     FROM [GW_20140315].[dbo].[arc_calls_reportview] 
     WHERE [groupdisplayid] = 'T802149' 
       OR [id_agency] = 'Dispatch' 
       OR [targetunit_agency] = 'Dispatch' 
     ORDER BY startdt) 
GROUP BY Dateadd(mi, (Datediff(mi, 0, startdt)/30) * 30, 0) 
ORDER BY 1

将两个查询合并为一个

相关推荐