(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`)
上面的查询返回如下结果,
count name
1 Linda
2 Mary
1 Steve
1 Linda
此查询是计算拥有者拥有的传输量。琳达有一辆车和一辆摩托车,所以结果应该是:
count name
2 Linda
2 Mary
1 Steve
我已经尝试过这个查询,但返回错误:
(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
任何人可以帮助我吗?