需要在MySQL中构建一个报告

Question

有人能帮我吗：

1. 将我的一堆查询合并为比17个单独的查询更干净的查询；以及
2. 了解如何合并这样的查询

我遇到的最大问题是交叉表。

以下是我的疑问：

SELECT Created_by_id,count(*) as Total from account      WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from call         WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from campaign     WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from case         WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from lead         WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from meeting      WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from opportunity  WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT Created_by_id,count(*) as Total from task         WHERE created_at between '2020-01-01' and '2020-01-31' group by Created_by_id;
SELECT modified_by_id,count(*) as Total from account     WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from call        WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from campaign    WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from case        WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from lead        WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from meeting     WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from opportunity WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;
SELECT modified_by_id,count(*) as Total from task        WHERE modified_at between '2020-01-01' and '2020-01-31' group by modified_by_id;

它们工作得很好。我运行每一个，将结果复制到电子表格中(逐行，按ID排序(。接下来，我进入另一个表(用户(，并将该表的内容复制到同一个电子表格中。来自modified_by_id和created_by_id的每个ID都与一个用户名对齐。然后我可以删除"；id"；专栏，我有一份报告要交给经理们。

我只想将所有这些简化为一个单一的查询，在这个查询中我只需要更改日期范围并转到。。。但更重要的是，我想了解如何以及它应该如何工作。

Answer 1

我在SQL方面有30年的经验。我写了一本关于SQL的书。

我不会试图将这些查询合并为一个查询。

因为它们来自不同的表，并且按不同的列分组，所以没有办法将这些查询组合成一个查询，除非从技术上讲，将它们作为一组单独查询的UNION。

在这种情况下，单独运行查询是更好的选择。

Answer 2

您需要了解两件事。

子查询，Union All

假设我们创建了两个表

create table a(a_id integer, a_name char(20));
create table b(b_id integer, b_name char(20));

里面有一些数据

insert into a(a_id, a_name) values(1, "a");
insert into b(b_id, b_name) values(1, "b");

我们可以进行联合所有查询来组合两个相似的表

(select a_id as id, a_name as name  from a) union all (select b_id as id, b_name as name  from b) 

结果是

id  name
1   a
1   b

现在让我们将这个子查询作为源或另一个查询

select new_tab.id as id, count(new_tab.name) as count from  ( 
(select a_id as id, a_name as name  from a) union all (select b_id as id, b_name as name  from b) 
) as new_tab 
group by new_tab.id;

结果是：

id  count
1   2

希望这有助于了解如何为您的案例进行单一查询。