组合 SQL Server 查询

我正在使用 SQL Server，并且有两个表，我想将其合并为一个查询，以用于填充网格视图。

表1 dbo.工作
UID（PK，整数）
Tech_Ticket（整数）
RMA_Ticket（整数）
区域 (nchar10)
已完成 (nchar10)
FA (nchar10)
代理 (nvarchar50)
跟踪 (nvarchar50)
Date_Added（日期）
Date_Updated（日期）

表2 dbo.订单
UID（PK，整数）
订单（整数）
代理 (nvarchar50)
门票（国际）
注释 (nvarchar50)

现在我将它们设置为两个单独的查询和两个单独的表。

Query1:

SELECT [Agent], 
  SUM(CASE WHEN [Date_Added] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'New ', 
  SUM(CASE WHEN [Date_Updated] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'Worked', 
  SUM(CASE WHEN [Completed] = 'yes' AND [Date_Updated] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'Completed', 
  SUM(CASE WHEN [Failure_Analysis] = 'yes' AND [Date_Updated] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'FA' 
  FROM Work 
  GROUP BY [Agent] 

Query2:

SELECT [Agent] 
  SUM(CASE WHEN [Date] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'Orders'
  FROM Orders 
  GROUP BY [Agent] 

有没有办法将这两个查询合并为一个？

You can JOIN他们。假如说Work是主表，应该是这样的：

SELECT A.*, B.Orders
FROM (  SELECT  [Agent],
                SUM(CASE WHEN [Date_Added] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'New',
                SUM(CASE WHEN [Date_Updated] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'Worked',
                SUM(CASE WHEN [Completed] = 'yes' AND [Date_Updated] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'Completed',
                SUM(CASE WHEN [Failure_Analysis] = 'yes' AND [Date_Updated] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'FA'
        FROM Work 
        GROUP BY [Agent]) A
LEFT JOIN (SELECT   [Agent]
                    SUM(CASE WHEN [Date] BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS 'Orders' 
            FROM Orders 
            GROUP BY [Agent]) B
ON A.[Agent] = B.[Agent]