使用union mysql select查询分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8572821/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 22:07:04 来源:igfitidea点击:
Group by with union mysql select query
提问by user1103332
(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike'
AND owner.`owner_id`=motorbike.`owner_id`
AND transport.`type_id`=motorbike.`motorbike_id` GROUP BY motorbike.owner_id)
UNION ALL
(SELECT COUNT(car.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type='car'
AND owner.`owner_id`=car.`owner_id`
AND transport.`type_id`=car.`car_id` GROUP BY car.`owner_id`)
The query above returns a result like this below,
上面的查询返回如下结果，
count name
1 Linda
2 Mary
1 Steve
1 Linda
This query is to count how many transport that owned by an owner. Linda have one car and one motorcycle,so the result should:
此查询用于计算所有者拥有的交通工具数量。琳达有一辆汽车和一辆摩托车，所以结果应该：
count name
2 Linda
2 Mary
1 Steve
I have tried this query,but return error:
我试过这个查询，但返回错误：
(SELECT COUNT(motorbike.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike'
AND owner.`owner_id`=motorbike.`owner_id`
AND transport.`type_id`=motorbike.`motorbike_id`)
UNION ALL
(SELECT COUNT(car.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type='car'
AND owner.`owner_id`=car.`owner_id`
AND transport.`type_id`=car.`car_id`) GROUP BY motorbike.owner_id
Can anyone help me please?
有人可以帮我吗？
回答by Micha? Powaga
select sum(qty), name
from (
select count(m.owner_id) as qty, o.name
from transport t,owner o,motorbike m
where t.type='motobike' and o.owner_id=m.owner_id
and t.type_id=m.motorbike_id
group by m.owner_id
union all
select count(c.owner_id) as qty, o.name,
from transport t,owner o,car c
where t.type='car' and o.owner_id=c.owner_id and t.type_id=c.car_id
group by c.owner_id
) t
group by name
回答by Matt Donnan
This may be what your after:
这可能是您的追求：
SELECT Count(Owner_ID), Name
FROM (
SELECT M.Owner_ID, O.Name, T.Type
FROM Transport As T, Owner As O, Motorbike As M
WHERE T.Type = 'Motorbike'
AND O.Owner_ID = M.Owner_ID
AND T.Type_ID = M.Motorbike_ID
UNION ALL
SELECT C.Owner_ID, O.Name, T.Type
FROM Transport As T, Owner As O, Car As C
WHERE T.Type = 'Car'
AND O.Owner_ID = C.Owner_ID
AND T.Type_ID = C.Car_ID
)
GROUP BY Owner_ID
回答by Authman Apatira
Try this EDITED:
试试这个编辑：
(SELECT COUNT(motorbike.owner_id),owner.name,transport.type FROM transport,owner,motorbike WHERE transport.type='motobike' AND owner.owner_id=motorbike.owner_id AND transport.type_id=motorbike.motorbike_id GROUP BY motorbike.owner_id)
UNION ALL
(SELECT COUNT(car.owner_id),owner.name,transport.type FROM transport,owner,car WHERE transport.type='car' AND owner.owner_id=car.owner_id AND transport.type_id=car.car_id GROUP BY car.owner_id)
