英文:
mySQL Check if all link items are a specified value
问题
我想检查所有链接的项目是否具有指定的值。我不确定如何正确解释它，但在我看来，示例已经相当自明。
示例数据库（简化）
Table Groups
PK_Groups int
Groupname varchar
Table Person
PK_Person int
Name varchar
isAdult tinyint(1)
FK_Groups int
如何检查只有成年人的组（1个查询返回所有组）？
在SQL中是否有方法可以做到这一点，还是我必须"手动"处理？
提前致谢
SELECT PK_Groups
FROM Groups
INNER JOIN Person ON PK_Groups = FK_Groups
WHERE isAdult = 0
WHERE子句不起作用，因为有一些是成年人
以及按名称分组并检查是否成年人也不起作用，因为它是在组内的
为每个组计算非成年人的数量将起作用（如果计数<= 0），但仅适用于每个组，而不是所有组一次。
英文:
I want to check if all linked Items that are a specified value. I am not sure how to properly explain it but the example is pretty self-explanatory in my opinion.
Example Database (simplified)
Table Groups
PK_Groups int
Groupname varchar
Table Person
PK_Person int
Name varchar
isAdult tinyint(1)
FK_Groups int
How can I check in which groups are only adults (1 query that returns all groups)?
Is there a way to do it in SQL or do I have to do "manually"?
Thanks in advance
SELECT PK_Groups
FROM Groups
INNER JOIN Person on PK_Group = FK_Groups
WHERE isAdult = 0
WHERE doesn´t work because there are some that are adult
and grouping by name and checking if adult doesnt work either because its inside the group
Couting non adults for each group would work (If Count <=0) but only for each group and not all groups at once
答案1
得分: 2
检查每个组的isAdult的最小值。如果它们都是成年人，最小值是1。
SELECT PK_Groups
FROM Groups
INNER JOIN Person ON PK_Groups = FK_Groups
GROUP BY PK_Groups
HAVING MIN(isAdult) = 1
请注意，这不是一般测试集合中所有值是否为特定值的方法。它仅在您要检查的值是可能的最大值时有效（您还可以通过将MIN()更改为MAX()来测试最小值）。
更一般的条件可以通过以下方式测试：
HAVING SUM(column = value) = COUNT(*)
英文:
Check the minimum value of isAdult for each group. If they're all adults, the minimum value is 1.
SELECT PK_Groups
FROM Groups
INNER JOIN Person on PK_Groups = FK_Groups
GROUP BY PK_Groups
HAVING MIN(isAdult) = 1
Note that this isn't the general way to test that all values in a set are a specific value. It only works when the value you're trying to check for is the highest possible value (you can also test for the lowest value by changing MIN() to MAX()).
The more general condition can be tested with:
HAVING SUM(column = value) = COUNT(*)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论