英文:
SQL multiple tables count by single field
问题
以下是 SQL 查询，用于获取拥有正好 1 辆汽车且汽车不是 Mazda 的人：
SELECT p.id AS person_id
FROM Person p
JOIN (
SELECT person_id
FROM Car
GROUP BY person_id
HAVING COUNT(*) = 1
) c ON p.id = c.person_id
JOIN (
SELECT DISTINCT person_id
FROM Car
WHERE car <> 'Mazda'
) d ON p.id = d.person_id;
这个查询会返回 person id 为 1 的结果，满足条件。请注意，查询使用了多个子查询来满足要求，以避免 "only_full_group_by" 错误。
英文:
MySQL / MariaDB structure:
Person
| id |
|----|
| 1 |
| 2 |
| 3 |
Car
| person_id | car |
|-----------|-------|
| 1 | Ford |
| 2 | Mazda |
| 3 | Ford |
| 3 | Mazda |
| 3 | BMW |
What query can get the Persons with exactly 1 car, and car is not Mazda ?
expected output is person id 1.
Note:
if GROUP BY is used, query should be full group by, otherwise getting the following error, and changing the SQL configuration is not wanted.
"In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'X'; this is incompatible with sql_mode=only_full_group_by"
答案1
得分: 2
可以使用聚合和 having 进行筛选：
select person_id
from car
group by person_id
having count(*) = 1 -- 仅有一辆汽车
and max(car = 'Mazda') = 0 -- 不是马自达的汽车
我们可以直接从 car 表中获得结果。如果由于某种原因您需要从 person 表中获取相应的行，我们可以使用 join：
select p.*
from person p
inner join (
select person_id
from car
group by person_id
having count(*) = 1 and max(car = 'Mazda') = 0
) c on c.person_id = p.id
英文:
You can use aggregation and having to filter:
select person_id
from car
group by person_id
having count(*) = 1 -- has just one car
and max(car = 'Mazda') = 0 -- which is not a Mazda
We can get the result directly from the car table. If for some reason you need to bring the corresponding row from person, we can join:
select p.*
from person p
inner join (
select person_id
from car
group by person_id
having count(*) = 1 and max(car = 'Mazda') = 0
) c on c.person_id = p.id
答案2
得分: 0
另一个选项是在相关子查询上使用EXISTS运算符的否定形式，如下所示：
select * -- 您可以用您的列列表替换 *
from Car t1
where car <> 'Mazda' and
not exists(
select 1 from Car t2
where t1.person_id = t2.person_id and
t1.car <> t2.car)
英文:
Another option, use the negate of the EXISTS operator on a correlated subquery as follows:
select * -- you may replace * with your columns list
from Car t1
where car <> 'Mazda' and
not exists(
select 1 from Car t2
where t1.person_id = t2.person_id and
t1.car <> t2.car)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论