mysql - SQL查询找到只会说英语的ID？

Question

找到所有只会说英语的 id 我无法做到这一点。第一个表是语言

+------+---------+----------+
| Id   | User_id | Language |
+------+---------+----------+
|    1 |     111 | english  |
|    2 |     112 | hindi    |
|    3 |     113 | odia     |
|    4 |     114 | english  |
|    5 |     115 | english  |
|    2 |     112 | english  |
|    3 |     113 | english  |
+------+---------+----------+

2-秒表用户

+------+------------+-----------+
| Id   | FIRST_NAME | LAST_NAME |
+------+------------+-----------+
|    1 | sirjit     | bit       |
|    2 | jit        | bi        |
|    3 | ji         | bi        |
|    4 | i          | b         |
|    5 | it         | ba        |
|    1 | sirjit     | bit       |
|    2 | jit        | bi        |
|    4 | i          | b         |
|    5 | it         | ba        |
|    3 | ji         | bi        |
+------+------------+-----------+

这是我的代码：-

Select First_name,User_id 
from user inner join language 
on user.Id=language.Id groupby User_id 
having count(language)=1;

标签： mysql

Answer 1

只说英语意味着表中的用户只有 1 行，language并且该行在列中包含“英语” language：

select u.id, u.First_name, u.Last_Name 
from user u inner join language l
on u.Id=l.user_Id 
group by u.id, u.First_name, u.Last_Name  
having count(*)=1 and max(l.language)='english';