如何在选择mysql5.7时找到案例

Question

这是我的小提琴https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=19b6e1298eba9eaa92ba2c726916d5d2

假设我有这张表

CREATE TABLE Prime_Table (
user_Id varchar(100),
join_date date,
platform varchar(100),
country varchar(30)
);
INSERT INTO Prime_Table VALUES
('895A8F53-C61C-471A-B934-CC2795286E19', '2020-01-01','IOS', 'United States'),
('2B4C47D7-0DDC-4007-B1CF-4E3C802E5D5D', '2020-01-02', 'IOS', 'United States'),
('5309F361-DBF9-4AF9-8B6B-79F377C3647C', '2020-01-02', 'IOS', 'United States'),
('79077D38-F49D-44E0-B47B-030954F6FE33', '2020-01-02', 'IOS', 'United States'),
('569D79E4-8EA8-4D0D-83F3-6EC3F4B307D9', '2020-01-03', 'IOS', 'Indonesia'),
('CF29B015-7337-429E-A055-EE9640033E69', '2020-01-03', 'IOS', 'United Kingdom'),
('4759E38F-7DEE-4D0E-91ED-9B8CB7C545FA', '2020-01-03', 'IOS', 'Lebanon'),
('499B35D0-84D9-43CE-B684-43A50F107866', '2020-01-04', 'IOS', 'Canada'),
('5C1ED286-BA84-434B-8FC7-97AF5235D051', '2020-01-04', 'IOS', 'Indonesia');

+--------------------------------------+------------+----------+----------------+
|               user_Id                | join_date  | platform |    country     |
+--------------------------------------+------------+----------+----------------+
| 895A8F53-C61C-471A-B934-CC2795286E19 | 2020-01-01 | IOS      | United States  |
| 2B4C47D7-0DDC-4007-B1CF-4E3C802E5D5D | 2020-01-02 | IOS      | United States  |
| 5309F361-DBF9-4AF9-8B6B-79F377C3647C | 2020-01-02 | IOS      | United States  |
| 79077D38-F49D-44E0-B47B-030954F6FE33 | 2020-01-02 | IOS      | United States  |
| 569D79E4-8EA8-4D0D-83F3-6EC3F4B307D9 | 2020-01-03 | IOS      | Indonesia      |
| CF29B015-7337-429E-A055-EE9640033E69 | 2020-01-03 | IOS      | United Kingdom |
| 4759E38F-7DEE-4D0E-91ED-9B8CB7C545FA | 2020-01-03 | IOS      | Lebanon        |
| 499B35D0-84D9-43CE-B684-43A50F107866 | 2020-01-04 | IOS      | Canada         |
| 5C1ED286-BA84-434B-8FC7-97AF5235D051 | 2020-01-04 | IOS      | Indonesia      |
+--------------------------------------+------------+----------+----------------+

我想从用户所属的每个国家进行映射，我可以制作这样的映射

SELECT COALESCE(country, 'Total') AS `country`, 
COUNT(a.user_id) AS `Count_User`,
round(COUNT(a.user_id) / any_value(totalcount) * 100, 1) AS `Count_User(%)`
FROM Prime_Table a
JOIN ( SELECT
COUNT(user_id) totalcount 
FROM Prime_Table
) totals
GROUP BY a.country WITH ROLLUP;
and this is the output
+----------------+------------+---------------+
|    country     | Count_User | Count_User(%) |
+----------------+------------+---------------+
| Canada         |          1 |          11.1 |
| Indonesia      |          2 |          22.2 |
| Lebanon        |          1 |          11.1 |
| United Kingdom |          1 |          11.1 |
| United States  |          4 |          44.4 |
| Total          |          9 |         100.0 |
+----------------+------------+---------------+

但是如何将所有这些国家分为两类，如美国和非美国，这样预期的结果就会变成这样

+-------------------+-------------+----------------+
|      country      | count_users | count_users(%) |
+-------------------+-------------+----------------+
| United States     |           4 | 44.44%         |
| Non-United States |           5 | 55,56%         |
| Total             |           9 | 100            |
+-------------------+-------------+----------------+

Answer 1

最简单的更改就是将FROM表替换为更改非美国国家/地区的子查询：

SELECT COALESCE(country, 'Total') AS `country`, 
COUNT(a.user_id) AS `Count_User`,
round(COUNT(a.user_id) / any_value(totalcount) * 100, 1) AS `Count_User(%)`
FROM (
SELECT user_id, CASE WHEN country != 'United States' THEN 'Non-United States' ELSE country END AS country
FROM Prime_Table a
) a
JOIN ( SELECT
COUNT(user_id) totalcount 
FROM Prime_Table
) totals
GROUP BY a.country WITH ROLLUP;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5492fdcf37a63e65773aa8a6d146144d

Answer 2

可能不是最有效的方法，但却是一种方法。扩展您当前的查询。将第一个结果转储到临时表中

CREATE TEMPORARY TABLE stats
SELECT COALESCE(country, 'Total') AS `country`, 
COUNT(a.user_id) AS `Count_User`,
round(COUNT(a.user_id) / any_value(totalcount) * 100, 1) AS `Count_User(%)`
FROM Prime_Table a
JOIN ( SELECT
COUNT(user_id) totalcount 
FROM Prime_Table
) totals
GROUP BY a.country WITH ROLLUP;

然后这个表上的group by case：

select 
(case 
when country like '%United States%' then 'United States' 
when country like '%Total%' then 'Total' 
else 'Non-United States' end) as country, 
sum(Count_User), 
sum(`Count_User(%)`
from stats 
group by 
(case 
when country like '%United States%' then 'United States' 
when country like '%Total%' then 'Total' 
else 'Non-United States' end
) order by 3,1 desc;