SQL Server - 将多个查询合并为一个

Question

我有两个表格，一个是Customer，另一个是Contacts。 Companies表格包含客户的信息，当向客户发送电子邮件时，在Contacts表格中创建一个联系人，其中包含有关发送的电子邮件类型和时间的信息。

以下是示例：

Companies:                               Contacts:
------------------------              ----------------------------------------------    
  ID  | Action                           ID   |  CompanyID  | CreatedON  | EmailType
----- | ----------------------          ----- |  ---------- | ---------  | ---------
 Cus1 | 07.03.2014 Registered            Con1 |   Cus1      | 07.03.2014 | NewsLetter
 Cus2 | 08.03.2014 UnSubscribe           Con2 |   Cus2      | 07.03.2014 | NewsLetter
 Cus3 | 10.03.2014 Order                 Con3 |   Cus3      | 10.03.2014 | NewsLetter                  
 Cus4 | 10.03.2014 Registered            Con4 |   Cus4      | 10.03.2014 | NewsLetter   
 Cus5 | NULL                             Con5 |   Cus5      | 14.03.2014 | NewsLetter
 Cus6 | 15.03.2014 UnSubscribe           Con6 |   Cus6      | 14.03.2014 | NewsLetter   
 Cus7 | NULL                             Con7 |   Cus7      | 17.03.2014 | NewsLetter        
 Cus8 | NULL                             Con8 |   Cus8      | 17.03.2014 | NewsLetter
 Cus9 | NULL                             Con9 |   Cus9      | 17.03.2014 | NewsLetter

我希望您能为我提供一份查询，以便我可以在特定日期查看发送了多少封电子邮件以及有多少客户进行了注册、取消订阅或预订订单。

例如，当我运行以下查询时，可以根据日期获取三月份发送的电子邮件数量：

Select Left(convert(date, CreatedON, 105),12) AS DATE_Sent, 
count ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) ) as Alle 
From Contacts 
Where Contacts.Comment Like 'Newsletter' and  
        ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) >= '01.03.2014' and 
CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) <= '30.03.2014')
Group by Left(convert(date, CreatedON, 105),12)
Order by DATE_Sent

我可以使用这个查询来获取三月份注册用户的数量：

SELECT Count(Companies.ID) as NumOfRegistered 
FROM Companies, Contacts 
Where Companies.ID = Contacts.CompanyID and Contacts.Comment Like 'Newsletter' And
      ( Companies.Action Like '%Registered%') And
      (Right(Companies.Action, 10) Like '%03.2014'
Group By Right(Companies.Action, 10)

同样的方式，我可以获得在三月份取消订阅或下单的用户。

我正在获取单个结果，不知道如何将这些小查询组合起来，以便我可以按日期查看所有结果。任何帮助都将不胜感激。我期望该查询能够给出以下结果：

    Date     | Alle |  Registered |   Unsubscribe |  Order |
  ----------   ----   ----------      ----------     -----
  07.03.2014 |  2   |     1       |    NULL       |   NULL
  10.03.2014 |  2   |     1       |    NULL       |    1    
  14.03.2014 |  2   |     NULL    |    NULL       |   NULL
  17.03.2014 |  3   |     NULL    |    NULL       |   NULL

- Kamran

Answer 1

试一试

Select DATE_Sent,Alle,NumOfRegistered 
from (
(Select CompanyID as id, Left(convert(date, CreatedON, 105),12) AS DATE_Sent, count ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) ) as Alle 
From Contacts 
Where Contacts.Comment Like 'Newsletter' and  
        ( CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) >= '01.03.2014' and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, CreatedON))) <= '30.03.2014')
Group by Left(convert(date, CreatedON, 105),12),CompanyID
Order by DATE_Sent) As query1

Left outer Join

(SELECT Companies.ID as id1,Count(Companies.ID) as NumOfRegistered 
FROM Companies, Contacts 
Where Companies.ID = Contacts.CompanyID and Contacts.Comment Like 'Newsletter' And
      ( Companies.Action Like '%Registered%') And
      (Right(Companies.Action, 10) Like '%03.2014'
Group By Right(Companies.Action, 10),Companies.ID) as query2
on query1.id=query2.id1)

从戈登的回复中可以看到，修改查询以避免Null并替换成0。

select thedate, 
Case when Alle=NULL then 0 else sum(Alle) as col1, 
Case when NumOfRegistered=NULL then 0 else sum(NumOfRegistered) as col2, 
Case when NumOfUnsubscribed=NULL then 0 else sum(NumOfUnsubscribed) as col3, 
Case when NumOfOrdered=NULL then 0 else sum(NumOfOrdered) as col4
from ((SELECT convert(date, left(co.Action 10), 104) as thedate, NULL as Alle
              SUM(case when co.Action Like '%Registered%' then 1 else 0 end) as NumOfRegistered,
              SUM(case when co.Action Like '%Unsubscribed%' then 1 else 0 end) as NumOfUnsubscribed,
              SUM(case when co.Action Like '%Ordered%' then 1 else 0 end) as NumOfOrdered, 
       FROM Companies co
       GROUP BY convert(date, left(co.Action 10), 104)
      ) union all
      (select convert(date, CreatedON, 105) as DATE_Sent, count(*) as Alle, NULL, NULL, NULL
       from Contacts c
       where c.Comment Like 'Newsletter' and  
             substring(c.CreatedOn, 4, 7) = '03.2014'
       group by convert(date, CreatedON, 105) 
      )
     ) t
group by thedate
order by thedate;

- Siva

---

此查询未返回正确的结果。NumOfRegistered 和Alle中的数据相同。Alle应包含在某个日期发送的所有电子邮件。但在此查询中，它显示已注册用户的数量。 - Kamran

感谢更新。但是我遇到了一个错误。 Msg 1033，第8行 ORDER BY子句仅在视图、内联函数、派生表、子查询和公共表达式中有效，除非指定TOP或FROM XML。 Msg 156，第17行 as关键字附近的语法不正确。 我删除了Order by，但是我又遇到了另一个错误： Msg 8120，级别16，状态1，第3行 Contacts.CompanyID'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。 - Kamran

再次感谢更新！但是我删除了Order By语句后仍然遇到了相同的错误。然后我看到了结果，但不幸的是，“Alle”字段仍然显示注册人数。但是这个字段应该显示当天发送的所有电子邮件。 - Kamran

@Kami 你可以使用Gordon的答案，因为它非常清晰明了。 - Siva

@Kami，请检查我的编辑，试图避免在该过程中出现Null，而是将其替换为0。据我所知，你无法避免这种情况，因为第三个查询的结果是前两个查询的联合，并且这两个查询具有不同的日期，因此当你进行union操作时，对于那些没有数据的date的结果将会是0。 - Siva

Answer 2

从companies表中获取数字将使用条件聚合：

SELECT convert(date, left(co.Action 10), 104) as date
       SUM(case when co.Action Like '%Registered%' then 1 else 0 end) as NumOfRegistered,
       SUM(case when co.Action Like '%Unsubscribed%' then 1 else 0 end) as NumOfUnsubscribed,
       SUM(case when co.Action Like '%Ordered%' then 1 else 0 end) as NumOfOrdered, 
FROM Companies co
GROUP BY convert(date, left(co.Action 10), 104);

您可以从联系人表中获取数字，如下所示：

select convert(date, CreatedON, 105) as DATE_Sent, count(*) as Alle 
from Contacts c
where c.Comment Like 'Newsletter' and  
      substring(c.CreatedOn, 4, 7) = '03.2014'
group by convert(date, CreatedON, 105) ;

你可以使用union all和aggregation来结合它们：

select thedate, sum(Alle), sum(NumOfRegistered), sum(NumOfUnsubscribed), sum(NumOfOrdered)
from ((SELECT convert(date, left(co.Action 10), 104) as thedate, NULL as Alle
              SUM(case when co.Action Like '%Registered%' then 1 else 0 end) as NumOfRegistered,
              SUM(case when co.Action Like '%Unsubscribed%' then 1 else 0 end) as NumOfUnsubscribed,
              SUM(case when co.Action Like '%Ordered%' then 1 else 0 end) as NumOfOrdered, 
       FROM Companies co
       GROUP BY convert(date, left(co.Action 10), 104)
      ) union all
      (select convert(date, CreatedON, 105) as DATE_Sent, count(*) as Alle, NULL, NULL, NULL
       from Contacts c
       where c.Comment Like 'Newsletter' and  
             substring(c.CreatedOn, 4, 7) = '03.2014'
       group by convert(date, CreatedON, 105) 
      )
     ) t
group by thedate
order by thedate;

一些教训：

1. 修正数据模型，确保日期以日期形式存储而非字符串。
2. 使用正确的转换方式。根据您提供的数据，日期转换格式为104（请参阅文档）。
3. 学习使用正确的显示连接语法，将条件放在on子句中而非where子句中。
4. 学习使用条件聚合。

- Gordon Linoff