mysql - 定义用于 GROUP BY 的条件

Question

在下面的数据库中，我需要所有用户和他们的位置，没有重复，并且首先列出新泽西州的用户。
(如果他们有 NJ 地址，我不需要他们的其他地址。为清楚起见简化了数据库)。

> table address,

|  id  |  state |   City   |
|------|--------|----------|
|  01  |   NY   |  Gotham  |
|  02  |   NY   |  Uye     |
|  03  |   NJ   |  Hoboken |
|  04  |   NJ   |  Newark  |

> table contact

|  user  |  address  |
|--------|-----------|
|   01   |     01    |
|   02   |     02    |
|   02   |     03    |
|   03   |     04    |

以下是我的一些尝试及其输出。从我读到的[我已经花了几个小时在这上面]，我认为这被称为不明确的 GROUP BY 查询，并且只被 MySQL 允许。

不过，我想不出正确的方法。请帮忙!

期望的结果:

|  user  |  state  |
|--------|---------|
|   02   |   NJ    |
|   03   |   NJ    |
|   01   |   NY    |

其他尝试:

SELECT user, state FROM contact, address WHERE id = address;
// Duplicate users, and addresses I do not need.

|  user  |  state  |
|--------|---------|
|   01   |   NY    |
|   02   |   NY    |
|   02   |   NJ    |
|   03   |   NJ    |  

SELECT user, state FROM contact, address WHERE id = address GROUP BY user;
// NY address. I need the NJ address.

|  user  |  state  |
|--------|---------|
|   01   |   NY    |
|   02   |   NY    |
|   03   |   NJ    |

SELECT user, state FROM contact, address WHERE id = address GROUP BY user HAVING state = 'NJ';
//Worse, now I lose my NY users, and one of my NJ users doesn't even show 

|  user  |  state  |
|--------|---------|
|   03   |   NJ    |

Answer 1

它可能不漂亮，但这个解决方案有效。我在这里更新了查询以反射(reflect) fiddle 中的变化:

SELECT user, state, `state` LIKE 'NJ' ismatch
FROM contact, address WHERE id = address
AND (state = 'NJ' OR `user` NOT IN 
    (SELECT `user` FROM contact, address WHERE address = id AND `state` LIKE 'NJ'))
GROUP BY user
ORDER BY ismatch DESC;

简单的解释:将每个用户与他在新泽西州的地址连接起来，或者如果他在新泽西州没有地址，则与他的任何地址连接起来。需要 GROUP BY 来防止重复用户出现在结果中。

本质上，问题归结为想要检索用户的匹配地址(如果存在)，否则只是获取该用户的任何地址。因此 where 子句中的第二个条件，只有当该用户有 NJ 地址时才会提取 NJ 地址，否则它只会正常使用第一个。 ismatch 字段用于排序目的。

注意:我最初是使用表之间的 JOIN 编写的。为清楚起见，它们已被删除。

Link to SQL Fiddle

编辑:有人向我指出，此解决方案并非在所有情况下都有效(请参阅评论)。为了解决这个问题，我添加了一个 GROUP BY，现在它似乎可以工作了。我还用简单的比较替换了 CASE 以提高性能:SQLFiddle

此外，我认识的一位在数据库管理方面有经验的人向我建议了一个替代答案。他的建议是(至少)在两个查询中执行此操作，因此我构建了这些查询并使用了 UNION:

SELECT user, state, 1 ismatch FROM contact, address
WHERE id = address AND state = 'NJ' GROUP BY user
UNION
SELECT user, state, 0 ismatch FROM contact, address
WHERE id = address AND `user` NOT IN (SELECT `user` FROM contact, address WHERE address = id AND `state` LIKE 'NJ')
GROUP BY user ORDER BY ismatch DESC

简单解释:取每个有NJ地址的用户，如果没有NJ地址，则取每个任意地址的用户，UNION两个结果集。 GROUP BY 用于两个查询以防止重复。