Как сделать выборку из одной таблицы и сгруппировать по колонке?

Question

[Aljo]

Здравствуйте!

Есть условная таблица:

object_id	field_name            value
1	        latitude	              23.00001
1	        longitude               45.23445
1	        category                 office
2	        latitude	               13.21451
2	        longitude                25.34355
2	        category                  center

Как сделать выборку, чтобы запись сгруппировались по object_id? При этом надо выбрать только записи с field_name = latitude и field_name = longitude. Т.е. чтобы в итоге было что-то наподобие:

object_id	   latitude	         longitude    
1	        23.00001           45.23445
2	        13.21451           25.34355

Заранее спасибо!

Comments

[Akina]

Искать по термину "условная агрегация".
Или просто JOIN трёх копий таблицы (кстати, при правильном индексе так, вероятно, будет даже быстрее).

Answer 1

[Сергей c0re]

Просто джойнишь таблицу саму с собой и для каждой "таблицы" в where пишешь field_name = 'longitude'и field_name = 'latitude'

select
    lng.object_id,
    ltd.value as latitude,
    lng.value as longitude
  from obj_table lng, obj_table ltd
  where lng.field_name = 'longitude'
    and ltd.field_name = 'latitude'
    and lng.object_id = ltd.object_id
;

см. пример на dbfiddle

UPDATE:

еще можно так:

select
    object_id,
    max(case field_name when 'latitude' then value else null end) as latitude,
    max(case field_name when 'longitude' then value else null end) as latitude,
    max(case field_name when 'category' then value else null end) as category
  from obj_table
  group by object_id;

или еще...
собрать строки в json, а затем извлечь обратно нужные поля...

-- собираем в json:
select
    object_id,
	cast(replace(group_concat(json_object(field_name,value)),'},{',',') as json) json_obj
  from obj_table
  group by object_id
;

-- или так:
select
    object_id,
    cast(concat('{',group_concat( concat('"',field_name,'":"',value,'"') separator ',' ), '}') as json) as json_obj
  from obj_table
  group by object_id
;

-- извлекаем поля из json:
select
    object_id,
    json_extract(json_obj,'$.latitude') as latitude,
    json_extract(json_obj,'$.longitude') as latitude,
    json_extract(json_obj,'$.category') as category
  from (
select
    object_id,
    cast(replace(group_concat(json_object(field_name,value)),'},{',',') as json) json_obj
  from obj_table
  group by object_id
  ) t
;

см. пример на dbfiddle

PS: JSON работает начиная с версии 5.7