Сглаживание SQL-запроса типа EAV

У меня есть база данных MySQL, используемая для списка рассылки, и мне нужно извлечь данные, поэтому одна запись участника представлена ​​одной строкой. Список рассылки хранит атрибуты пользователя как EAV. Я могу извлечь все детали, которые мне нужны, используя следующий запрос SQL, но каждая запись участника занимает несколько строк:

SELECT a.id, a.name, a.email, b.id, b.name, b.email, c.title, d.val
FROM lists a, listmembers b, fields c, fieldsdata d
WHERE a.id = b.nl
AND b.id = d.eid
AND c.id = d.fid
ORDER BY b.id, a.id, b.name

Это возвращает что-то вроде этого:

'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Firstname', 'John'
'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Lastname', 'Smith'
'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Country', 'UK'
'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Town', 'Cambridge'
'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Shoesize', '7'
'6', 'Mailing List name', 'owner@mailinglist.com', '10478', 'username', 'mailinglistmember@emailaddress.com', 'Favourite Colour', 'Purple'

Мне нужно сгладить это в одну строку с помощью SQL, требуя только значения, относящиеся к ключам имя, фамилия, город и страна

База данных невелика, а таблица fieldsdata самая большая с 5500 строками.

Похоже на настоящую боль, поэтому любые указатели будут с благодарностью приняты.!

Источник

3 ответа

Решение

Ты можешь использовать MAX с CASE чтобы развернуть ваши результаты, если я правильно понимаю ваш вопрос:

SELECT l.id, l.name, l.email, lm.id, lm.name, lm.email, 
   MAX(CASE WHEN f.title = 'Firstname' THEN fd.val END) FirstName,
   MAX(CASE WHEN f.title = 'Lastname' THEN fd.val END) Lastname,
   MAX(CASE WHEN f.title = 'Country' THEN fd.val END) Country,
   MAX(CASE WHEN f.title = 'Town' THEN fd.val END) Town
FROM lists l
   JOIN listmembers lm ON l.id=lm.nl
   JOIN fieldsdata fd ON fd.eid = lm.id
   JOIN fields f ON f.id = fd.fid 
GROUP BY l.id, lm.id

Предполагается, что поле id из таблицы списков является вашим уникальным идентификатором. Если нет, вам нужно добавить дополнительные поля в ваш GROUP BY (скорее всего, поле id из таблицы listmembers).

Источник

Для любого (как я), который находит этот вопрос, где ваш запрос относится к SQL Server, а не MySQL, есть вариант запроса Pivot.

SELECT id, name, email, id2, name2, email2, [Firstname], [Lastname], 
    [Country], [Town]
FROM (select id, name, email, id2, name2, email2, title, val from yourresults) ps
PIVOT (
        MAX(val) 
        FOR title IN ([Firstname], [Lastname], [Country], [Town])
    ) as pvt

Смотрите SQL fiddle (работа с sgeddes SQL fiddle выше - спасибо)

Или в MS Access:

TRANSFORM Max(val)
SELECT id, name, email, id2, name2, email2
FROM yourresults
GROUP BY id, name, .email, id2, name2, email2
PIVOT title In ("FirstName","LastName","Country","Town");
Источник

Таблицы EAV являются плохим выбором дизайна и должны использоваться только в том случае, если вы не знаете заранее, какие поля необходимы. Поля, такие как имя, могут знать, что они вам понадобятся заранее и должны быть в соответствующих таблицах. Если вы хотите использовать EAV для всего, по крайней мере, прекратите использовать реляционную базу данных и используйте специально созданную для этого базу данных NOSQL.

Чтобы получить желаемый результат, вам нужно будет присоединиться к таблицам EAV один раз для каждого типа данных, который вы хотите.

Источник
Другие вопросы по тегам