유니언 mysql 선택 쿼리를 사용하여 그룹화

유니언 mysql 선택 쿼리를 사용하여 그룹화

(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

누가 나 좀 도와줄래?

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

그 후는 다음과 같습니다.

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

편집한 내용을 시험해 보십시오.

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

언급URL : https://stackoverflow.com/questions/8572821/group-by-with-union-mysql-select-query