SQL查询 - 从两个表获取两列的计数

Question

表格 1：

TicketNumber    | Rules
---------------------------
PR123       | rule_123
PR123       | rule_234
PR123       | rule_456
PR999       | rule_abc
PR999       | rule_xyz

---

表2：

TicketNumber    | Rules
---------------------------
PR123       | rule_123
PR123       | rule_234
PR999       | rule_abc

注意：两个表具有相同的结构，即列名称相同但计数不同。

注意：两个表具有相同的票号值集。

案例1： 如果我需要从table1中获取每个票的票和规则计数，则查询如下：

Select [TicketNo], COUNT([TicketNo]) AS Rules_Count from [Table1] group by TicketNo

这将以以下格式输出结果：

ticketNumber    | Rules_Count
---------------------------
PR123       | 3
PR999       | 9

---

案例2：（需要帮助）

现在，先前的查询只获取了一个表中的票和票数计数。我还需要在table2中获取相同票的数量（因为两个表具有相同的tkt nos）。

我需要以这种方式获得结果：

ticketNumber    | Count(ticketNumber) of table1 | Count(ticketNumber) of table2
---------------------------------------------------------------------------------
PR123       | 3                 | 2
PR999       | 2                 | 1

表1和表2都有相同的票号集合，但计数不同。

如何获得上述结果？

- Sabz

---

我删除了MySQL标签，因为语法表明是SQL Server。 - Gordon Linoff

Answer 1

从“语句角度”来看，有一个更简单的解决方案（不使用COALESCE可能会难以理解）。请注意性能：

Select T1.TicketNumber,T1.Rules_Count_1,T2.Rules_Count_2
FROM
(
  Select [TicketNumber], COUNT([TicketNumber]) AS Rules_Count_1 
  from [Table1] T1
  group by TicketNumber) T1
INNER JOIN
(
  Select [TicketNumber], COUNT([TicketNumber]) AS Rules_Count_2
  from [Table2] T2
  group by TicketNumber
 ) T2
on T1.TicketNumber = T2.TicketNumber

SQL Fiddle演示

- Francesco De Lisi

---

然而，InnerJoin 意味着 count(table1) 应该大于 count(table2)，对吗？如果不同怎么办？ - Sabz

内连接意味着“仅保留两个表中存在的行并将它们连接”。因此，如果您在Table1中放置了一个带有TicketNumberX的行，以及在Table2中放置了42个带有TicketNumberX的行，那么这种方式应该能够正常工作。但是，如果在Table2中放置了0个带有TicketNumberX的行，则永远无法进行连接。 - Francesco De Lisi

谢谢。有没有不使用JOINS的方法来完成这个任务？ - Sabz

是的，但由于WHERE子句和UNION的强制过滤，使用简单的JOIN替代并不是一个好主意。 - Francesco De Lisi

Answer 2

您可以在聚合后使用全外连接来完成此操作（如果您确实知道两个表具有相同的票，则可以使用内连接）：

select coalesce(t1.TicketNo, t2.TicketNo) as TicketNo,
       coalesce(t1.Rules_Count, 0) as t1_Rules_Count,
       coalesce(t2.Rules_Count, 0) as t2_Rules_Count
from (Select [TicketNo], COUNT([TicketNo]) AS Rules_Count
      from [Table1]
      group by TicketNo
     ) t1 full outer join
     (Select [TicketNo], COUNT([TicketNo]) AS Rules_Count
      from [Table2]
      group by TicketNo
     ) t2
     on t1.TicketNo = t2.TicketNo;

- Gordon Linoff

---

谢谢Gordon，你的查询完美地运行了。然而，外连接意味着table2应该比table1包含更多的计数，对吗？如果计数不同怎么办？ - Sabz

@sabz . . . 全外连接返回两个表中的所有行。你可能在想左外连接或右外连接。 - Gordon Linoff

Answer 3

SELECT A.center,
       A.total_1st,
       B.total_2nd
FROM   (SELECT a.center,
               Count (a.dose1) AS Total_1st
        FROM   table_1 a
        GROUP  BY a.center) A
       INNER JOIN (SELECT b.center,
                          Count (b.dose2) AS Total_2nd
                   FROM   table_2 b
                   GROUP  BY b.center) B
               ON a.center = b.center
ORDER  BY A.center  

- Almamun