mysql - 多条件sql语句

Question

我有一张包含英国或美国用户和国家/地区的表格，现在一个用户可以多次出现在不同的国家/地区。我需要选择一个列表，其中包含唯一用户，他们的国家等于英国、美国或两者。

这是我正在尝试的方法，但似乎没有用。

select user, 
    case 
        when COUNT(*) <= 1 and count(select where = "UK") = count(*) then "UK"
        when COUNT(*) <= 1 and count(select where = "US") = count(*) then "US" 
        when COUNT(*) <= 1 and count(select where = "US") != count(*) then "BOTH"
    END CASE
as country from users; 

第一次在查询中使用这样的逻辑，很抱歉听起来像菜鸟。

+------------------------------+-------------+
| user                         | country     |
+------------------------------+-------------+
| John                         | US          |
| Jack                         | US          |
| John                         | UK          |
| Joe                          | US          |
| John                         | UK          |
| Jacky                        | US          |
+------------------------------+-------------+

Answer 1

你可以这样做:

SELECT 
    temp.user,
    if(temp.CountryList = "UK,US", "BOTH", temp.CountryList) as country
FROM
(
     SELECT 
         user,
         group_concat(DISTINCT country ORDER BY country) as CountryList
     FROM USERS
     GROUP BY user
) temp