MySQL 在 group by 子句上使用多个条件？

发布于 2025-01-09 11:51:05 字数 775 浏览 1 评论 0原文

我有一个邻里表，由邻里 ID 和邮政编码组成。

+--------------+---------+
| Neighborhood | zipcode |
+--------------+---------+
|            1 |   12345 |
|            2 |   12346 |
|            3 |   12357 |
+--------------+---------+

有与邻域相关的 oneToMany Person 记录：

+--------+--------------+-----------+
| Person | neighborhood | eye color |
+--------+--------------+-----------+
|      1 |            1 | blue      |
|      2 |            1 | grey      |
|      3 |            1 | brown     |
|      4 |            2 | blue      |
|      5 |            2 | brown     |
|      6 |            3 | hazel     |
+--------+--------------+-----------+

我正在寻找至少有一个蓝眼睛的人且没有灰眼睛的人的邻域（在本例中，neighborhood=2 将满足条件）。

我将如何构建查询以按行中的邻域进行分组？

原文

I have a table of Neighborhoods, comprised of a neighborhood_id and a zip_code.

+--------------+---------+
| Neighborhood | zipcode |
+--------------+---------+
|            1 |   12345 |
|            2 |   12346 |
|            3 |   12357 |
+--------------+---------+

There are oneToMany Person records that relate to a neighborhood:

+--------+--------------+-----------+
| Person | neighborhood | eye color |
+--------+--------------+-----------+
|      1 |            1 | blue      |
|      2 |            1 | grey      |
|      3 |            1 | brown     |
|      4 |            2 | blue      |
|      5 |            2 | brown     |
|      6 |            3 | hazel     |
+--------+--------------+-----------+

I am looking to identify neighborhoods where there is at least one person with blue eyes and no people with grey eyes (in this case, neighborhood=2 would meet the condition).

How would I structure the query to group by neighborhoods across rows?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柠檬 2025-01-16 11:51:05

这很简单：

select neighborhood
from Person
where `eye color` in ('blue','grey')
group by neighborhood
having sum(`eye color`='grey')=0

This is simply:

select neighborhood
from Person
where `eye color` in ('blue','grey')
group by neighborhood
having sum(`eye color`='grey')=0

回复收藏 0 原文

初吻给了烟 2025-01-16 11:51:05

尝试：

SELECT Neighborhood 
FROM Neighborhoods n  
WHERE EXISTS   ( SELECT neighborhood  
                 FROM My_table m
                 WHERE n.Neighborhood = m.neighborhood 
                 AND  eye_color = 'blue'
                 )
AND NOT EXISTS ( SELECT neighborhood  
                 FROM My_table m
                 WHERE n.Neighborhood = m.neighborhood 
                 AND  eye_color = 'grey'
                 );

演示

Try:

SELECT Neighborhood 
FROM Neighborhoods n  
WHERE EXISTS   ( SELECT neighborhood  
                 FROM My_table m
                 WHERE n.Neighborhood = m.neighborhood 
                 AND  eye_color = 'blue'
                 )
AND NOT EXISTS ( SELECT neighborhood  
                 FROM My_table m
                 WHERE n.Neighborhood = m.neighborhood 
                 AND  eye_color = 'grey'
                 );

Demo

回复收藏 0 原文

~没有更多了~