按联合mysql分组查询 [英] Group by with union mysql select query

问题描述

 （SELECT COUNT（摩托车.`所有者_id`）作为计数，owner.`name`，transport.`type`从运输，所有者，摩托车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`）
  

上面的查询返回如下结果，

 计数名称
 1 Linda 
 2 Mary 
 1 Steve 
 1 Linda 
  

该查询的目的是计算许多由拥有者拥有的运输工具。琳达有一辆车和一辆摩托车，所以结果应该是：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Mary
1 Steve

我试过这个查询，但返回错误：

 （SELECT COUNT（motorbike.` owner）'，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 
  

< >

解决方案

  select sum（qty），name 
 from（
 select count（m.owner_id）as qty，o.name 
 from transport t，车主o，摩托车m 
其中t.type ='motobike'和o.owner_id = m.owner_id 
和t.type_id = m.motorbike_id 
组别由m.owner_id 
 
 union all 
 
 select count（c.owner_id）as qty，o.name，$ b $ 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 
 by c.owner_id 
）t 
按名称分组
  

(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?

解决方案

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

这篇关于按联合mysql分组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！