MySQL：分组依据和计数多个字段：REDUX

Question

OK，与早期主题略有不同。 使用相同的基本思想，我想获得字段的独立计数，然后我希望它们按更高阶细分进行分组。

我扩展了 David 的示例以包含更高阶的列：

district_id, product_id, service_id
dist    proj    serv
1   1   1
1   1   2
1   1   2
1   1   3
1   1   3
1   1   4
1   2   2
1   2   4
1   2   4
1   2   5
1   2   5
2   1   1
2   2   1
2   1   6
2   2   6
2   3   6

为了获得总计的结果，我使用了带有两个子查询的简单查询。

select 
(select count(Distinct project_id) from GroupAndCountTest) AS "projects",
(select count(Distinct service_id) from GroupAndCountTest) as "services";
projects  services
       3         6

挑战在于将其分组到district_id内。 我想要的是：

district_id   projects  services
          1          2         5
          2          3         6 

我最终使用了类似的子查询，但我能够组合它们的唯一方法（除了使用存储函数）是为每个地区重新运行子查询。 （这里不是一个大问题，但在我的应用程序中，子查询使用具有大量"地区"的多个表，因此为每个"地区"再次运行两个子查询，这将变得越来越无效。

这个查询有效，但我希望看到更有效的东西。

select t1.district_id, p1.projects, s1.services
  from GroupAndCountTest as t1
  join (select district_id, count(Distinct project_id) as projects 
    from GroupAndCountTest
    group by district_id) AS p1
    on p1.district_id=t1.district_id
  join (select district_id, count(Distinct service_id) as services 
    from GroupAndCountTest
    group by district_id) as s1
    on s1.district_id=t1.district_id
  group by t1.district_id;

谢谢。

PS：如果要进行实验，可以使用以下命令创建表：

CREATE TABLE `GroupAndCountTest` (
  `district_id` int(5) DEFAULT NULL,
  `project_id` int(5) DEFAULT NULL,
  `service_id` int(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert  into `GroupAndCountTest`(`district_id`,`project_id`,`service_id`) 
  values (1,1,1),(1,1,2),(1,1,2),(1,1,3),(1,1,3),(1,1,4),(1,2,2),(1,2,4),
  (1,2,4),(1,2,5),(1,2,5),(2,1,1),(2,2,1),(2,1,6),(2,2,6),(2,3,6);

Answer 1

select district_id, 
count(distinct(product_id)) projects, 
count(distinct(service_id)) services 
from MyTable group by district_id;

其中 MyTable 包含 district_id, product_id, service_id 列

Answer 2

你让这种方式变得比它需要的更难。你不需要子查询，只需要一个分组依据。

select district_id, count(distinct project_id), count(distinct service_id)
from GroupAndCountTest
group by district_id

Answer 3

SELECT district_id, count( DISTINCT (
project_id
) ) projects, count( DISTINCT (
service_id
) ) services
FROM GroupAndCountTest
GROUP BY district_id

我已经进步了:(