我有一个用于邮件列表的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将其扁平化为一行,只需要与键firstname、lastname、town和country相关的值
数据库不是很大,fieldsdata表是最大的,大约有5500行。
似乎是一个真正的痛苦,所以任何指针将感激地接受。div !
对于任何人(像我一样),发现这个问题,你的查询涉及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(从上面的sqlfiddle工作-谢谢)
或在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");
如果我正确理解你的问题,你可以使用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
- 简化SQL查询
假设列表表中的id字段是您的唯一标识符。如果没有,则需要在GROUP BY中添加额外的字段(很可能是listmembers表中的id字段)。
EAV表是一个糟糕的设计选择,只有在您无法提前知道需要哪些字段时才应该使用。像firstname这样的字段可以提前知道您将需要它们,并且应该在适当的相关表中。如果您希望在所有方面都使用EAV,那么至少停止使用关系数据库,而使用为此设计的NOSQL数据库。
要得到您想要的结果,您需要为您想要的每种类型的数据连接EAV表一次。