主页 / coding / 问题 / 78907311
Accepted
John Beasley
Asked: 2024-08-24 03:17:26 +0800 CST

获取每个表中的每个电子邮件类型的计数

  • 772

继续我上一个问题：从多个表中获取每种电子邮件类型的计数

我必须得到澄清……

所以我确实需要多个表格。

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

所有表格的设计都相同。表 1 可能类似于以下内容：

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

table2 可能看起来像这样：

| 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 和其他的数量。

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

如果取得进展，我将继续进行测试并更新问题。

** 编辑 **

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

sql

2 个回答

  1. Best Answer

    解决这个问题的方法是先将所有表联合起来，然后对结果进行 PIVOT，将 ISP 放在列中。或者，对每个表进行 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 示例在此处

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

    • 1

  2. 将表名作为 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

    要将每个提供程序放入一列，您可以透视结果数据。请参阅如何在 MySQL 中返回透视表输出？

    • 0

相关问题