代码之家 › 专栏 › 技术社区 › John Beasley

获取每种电子邮件类型的PER表计数

mysql sql

John Beasley · 技术社区 · 1 年前

跟进我之前的问题: Get count of each email type from multiple tables

我必须得到澄清。。。

所以我确实需要多个表。

然后,我需要从每个表中获取所有“gmail”、“msfts”、“others”和“vmgs”的计数。

所有的桌子都有相同的设计。表1可能类似于:

| EMAIL | MY_ISP | STATUS |
 -------------------------
| email1| gmail  | active |
 -------------------------
| email2| msft   | unsub  |
 -------------------------
| email3| vmg    | active |
 -------------------------
| email4| gmail  | active |
 -------------------------

表2可能看起来像这样:

| EMAIL | MY_ISP | STATUS |
 -------------------------
| email1| gmail  | unsub  |
 -------------------------
| email2| msft   | active |
 -------------------------
| email3| vmg    | active |
 -------------------------
| email5| gmail  | unsub  |
 -------------------------

一封电子邮件可能存在于多个表中,但不会在同一个表中列出两次。同一电子邮件可能在一个或多个表中处于活动状态,而在另一个表中可能不存在。

我需要得到每个表中所有活动电子邮件的运行计数。

我需要展示最终产品如下:

TABLE | GMAIL | MSFT | OTHER | VMG 
-----------------------------------
T1    | 5000  | 7000 | 4500  | 475
-----------------------------------
T2    | 4520  | 6789 | 4450  | 425
-----------------------------------
T3    | 4400  | 6500 | 4123  | 410
-----------------------------------

类似的东西。一直到T15。

随着我不断添加更多的表,总数应该逐渐减少,直到它们几乎为0。

我希望这是有道理的。

我很难理解这个逻辑。

根据我之前的问题,我可以使用以下查询获得my_ISP的总和:

SELECT COUNT(`my_isp`) AS 'COUNT', `my_isp` FROM `table1` GROUP BY `my_isp`

然后会吐出这样的东西:

COUNT | my_isp
---------------
4000  | Gmail
---------------
2000  | MSFT
---------------
10000 | other
---------------
15000 | VMG
---------------

我可以用这样的方法得到每个MY_ISP的总数:

select my_isp ,sum(cnt) AS total
 from (select my_isp,count(t1.`my_isp`) cnt from `table1` t1 group by my_isp
      UNION ALL
       select my_isp,count(t2.`my_isp`) cnt from `table2` t2 group by my_isp
      ) subquery_name
 group by my_isp

上面的工作实际上非常出色。但这不是要求的。

所以,我想重申一下,我需要计算每张表中的GMAILS、MSFT、VMG和OTHER。

请帮我弄清楚这个查询应该是什么样子的,因为我迷路了。

如果我取得了一些进展,我将继续运行测试并更新问题。

**编辑**

我在想,也许我需要创建一个视图,将不同的MY_ISP显示为列标题,将每个TABLE显示为行。

2 回复 | 直到 1 年前

JNevill 1 年前

解决这个问题的方法是首先将所有表联合在一起,然后将结果PIVOT到列中。或者,也可以选择将每个表中的ISP放在列中,然后将它们联合在一起。我会选择第一个,因为PIVOT的计算成本很高,所以执行一次该步骤是最好的

这看起来像:

SELECT tablename,
       COUNT(CASE WHEN ISP = 'gmail' THEN email END) as gmail,
       COUNT(CASE WHEN ISP = 'msft' THEN email END) as msft,
       COUNT(...
       COUNT(...
FROM
    (
        SELECT 't1' as tablename, my_isp, email FROm table1 
        UNION ALL
        SELECT 't2', my_isp, email FROM table2
        UNION ALL
        SELECT 't3', my_isp, email FROM table3
        UNION ALL
        SELECT....
    ) sub
GROUP BY tablename
ORDER BY tablename

dbfiddle example here

我还没有看你的相关问题,但我相信这已经被详细讨论过了,我觉得也需要在这里添加,因为这个UNION解决方案是对实际问题的权宜之计:也就是说,所有这些数据都应该已经在一个表中了。一个正确的模式基本上看起来像这个答案中的子查询的输出。如果你不负责模式或数据采集,这可能是不可能的,但这值得思考。更改模式将降低此查询的复杂性,因为不需要子查询,并且将降低运行此查询的计算成本,因为数据已经正确联合。

Barmar 1 年前

在UNION的每个查询中添加表名作为另一列

select table_name, my_isp ,sum(cnt) AS total
from (
    select 't1' AS table_name, my_isp, count(t1.`my_isp`) cnt from `table1` t1 group by my_isp
    UNION ALL
    select 't2' AS table_name, my_isp, count(t2.`my_isp`) cnt from `table2` t2 group by my_isp
) subquery_name
group by table_name, my_isp

要将每个提供者放入一列中,您可以对结果数据进行透视。请参阅 How can I return pivot table output in MySQL?