时间戳

首页 > 解决方案 > Mysql 多个 SELECT 和 Count 查询

mysql - Mysql 多个 SELECT 和 Count 查询

问题描述

您能否建议将多项选择和计数组合到一个查询中的最佳方法是什么

例如，这里有一些来自 test_table 的查询，它们可以自己完美运行

SELECT name, count(*) AS 'Overall' FROM test_test WHERE country_prefix = '44' AND area_code = '203' GROUP BY `city_name`;  

SELECT name, count(*) AS 'unallocated' FROM test_tab le WHERE country_prefix = '44' AND area_code = '203' AND removed != '1' AND destination-value = '1234' GROUP BY `city_name`;

还有一些查询我会运行，但我想如果我让两个一起工作，我就可以做剩下的了

我在想这样的事情：

SELECT name, (SELECT count(*) FROM test_table WHERE country_prefix = '44' AND area_code = '203' GROUP BY `city_name') AS "Overall", (SELECT count(*) AS 'unallocated' FROM test_table WHERE country_prefix = '44' AND city_code = '203' AND removed != '1' AND destination-value = '1234' GROUP BY 'city_name')

但不幸的是不起作用

最好的方法是在以下情况下使用 sum case：

sum(case when destination-value = '1234' then 1 else 0 end) AS unallocated

标签： mysqlselectcount

解决方案

您可以使用条件聚合来做到这一点：

SELECT city_name, 
  COUNT(CASE WHEN country_prefix = '44' AND area_code = '203' THEN 1 END) AS Overall, 
  COUNT(CASE WHEN country_prefix = '44' AND area_code = '203' AND removed != '1' AND destination-value = '1234' THEN 1 END) AS unallocated
FROM tablename
GROUP BY city_name;

或者由于这两种情况包含共同条件，这些条件可以移到一个WHERE子句中：

SELECT city_name, 
  COUNT(*) AS Overall, 
  COUNT(CASE WHEN removed != '1' AND destination-value = '1234' THEN 1 END) AS unallocated
FROM tablename
WHERE country_prefix = '44' AND area_code = '203'
GROUP BY city_name;

在 Mysql 中，可以使用函数来简化代码，SUM()而不是COUNT()：

SELECT city_name, 
  COUNT(*) AS Overall, 
  SUM(removed != '1' AND destination-value = '1234') AS unallocated
FROM tablename
WHERE country_prefix = '44' AND area_code = '203'
GROUP BY city_name;

推荐阅读