Вывести одной строкой несколько значений из таблицы

Есть запрос

select * from shop_product_props
where prop_id in (559, 799)

1 2	select * from shop_product_props where prop_id in (559, 799)

А нужно сгруппировать результат по товару, и получить его значения

Запрос должен быть следующим (Запрос занял 0.0118 сек.)

select product_id, 
MAX(if(prop_id=559,value,NULL)) as brand, 
MAX(if(prop_id=799,value,NULL)) as mark 
from shop_product_props
where prop_id in (559, 799) GROUP BY 1

select product_id,

MAX(if(prop_id=559,value,NULL)) as brand,

MAX(if(prop_id=799,value,NULL)) as mark

from shop_product_props

where prop_id in (559, 799) GROUP BY 1

Еще вариант (Запрос занял 0.2479 сек.)

select
    spp.product_id,
    spp1.value as brand,
    spp2.value as mark
from (
    select distinct product_id
    from shop_product_props
    where prop_id in (559, 799)
) as spp
    left join shop_product_props as spp1 on spp.product_id = spp1.product_id and spp1.prop_id = 559
    left join shop_product_props as spp2 on spp.product_id = spp2.product_id and spp2.prop_id = 799

select

spp.product_id,

spp1.value as brand,

spp2.value as mark

from (

select distinct product_id

from shop_product_props

where prop_id in (559, 799)

) as spp

left join shop_product_props as spp1 on spp.product_id = spp1.product_id and spp1.prop_id = 559

left join shop_product_props as spp2 on spp.product_id = spp2.product_id and spp2.prop_id = 799

Добавить комментарий Отменить ответ