mysql的SELECT在一列中唯一的值

Question

这是查询：

SELECT a.id, a.userName,if(o.userId=1,'C',if(i.userId=1,'I','N')) AS relation 
     FROM tbl_users AS a 
     LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
     LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
    ORDER BY relation 

这将返回的输出如下：

+----+--------------+-------------+ 
| ID | USERNAME  | RELATION | 
+----+--------------+-------------+ 
| 1 |   ray |   C | 
+----+--------------+-------------+ 
| 2 |   john |   I | 
+----+--------------+-------------+ 
| 1 |   ray |   N | 
+----+--------------+-------------+ 

我需要删除的第三行从选择查询中检查是否可能该id是重复的。优先级如下：

Ç - > I - > N.如此以来，已经有一个“雷”了C，我不再次与我或N.

我尝试添加不同的希望（a.id）但它不起作用。我该怎么做呢？

为什么DISTINCT不能为此工作？

Answer 1

从你给的规格，所有你需要做的就是通过ID和用户名组，然后选择关系的最低值，你可以找到（因为C < I < N）

SELECT a.id, a.userName, MIN(if(o.userId=1,'C',if(i.userId=1,'I','N'))) AS relation 
    FROM tbl_users AS a 
    LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
    LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
GROUP BY a.id, a.username 

Answer 2

有多种如您在manual page中看到的那样获得组方式最大/最小值的方法。

如果行的顺序不能通过字母顺序定义，那么最适合您的是第一个，。

在这种情况下，如果希望的顺序是z-a-m（参见公羊的评论），则需要FIELD()函数。

所以你的答案是

SELECT 
a.id, 
a.userName, 
if(o.userId=1,'C',if(i.userId=1,'I','N')) AS relation 
FROM tbl_users a 
LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
WHERE 
if(o.userId=1,'C',if(i.userId=1,'I','N')) = (
    SELECT 
    if(o.userId=1,'C',if(i.userId=1,'I','N')) AS relation 
    FROM tbl_users aa 
    LEFT JOIN tbl_contacts AS o ON aa.id = o.contactId 
    LEFT JOIN tbl_invites AS i ON aa.id = i.invitedId 
    WHERE aa.id = a.id AND aa.userName = a.userName 
    ORDER BY FIELD(relation, 'N', 'I', 'C') DESC 
    LIMIT 1 
) 

注意，你也可以像ORDER BY FIELD(relation, 'C', 'I', 'N')有更可读/直观。我反过来转向它，因为如果你有可能在关系中有'X'，那么FIELD（）函数将返回0，因为X没有被指定为参数。因此它会在'C'之前排序。通过排序降序和围绕这个参数的顺序是不可能发生的。