sql - 更好的 SQL 查询？

Question

数据库结构:

fid  
subid  
fieldname  
fieldval 

为了得到一个人的记录，我做了这样的事情:

$querystr = "
SELECT FN.sub_id, FN.`First Name` , LN.`Last Name` , DOB.`dob` , EMAIL.`email` , PHONE.`phone`  
FROM  
( SELECT sub_id, field_val AS 'First Name'
FROM $db->data
WHERE `field_name` = 'First Name'
)FN,   
(  SELECT sub_id, field_val AS 'Last Name'
FROM $db->data
WHERE `field_name` = 'Last Name'
)LN,  
( SELECT sub_id, field_val AS `Team`
FROM $db->data
WHERE `field_name` = 'Team'
)TEAM,  
( SELECT sub_id, field_val AS `dob`
FROM $db->data
WHERE `field_name` = 'DOB'
)DOB,  
( SELECT sub_id, field_val AS `email`
FROM $db->data
WHERE `field_name` = 'EMail'
)EMAIL,  
( SELECT sub_id, field_val AS `phone`
FROM $db->data
WHERE `field_name` = 'Telephone'
)PHONE  

WHERE FN.sub_id = LN.sub_id  
AND LN.sub_id = DOB.sub_id  
and DOB.sub_id = EMAIL.sub_id  
and EMAIL.sub_id = PHONE.sub_id  
ORDER BY LN.`Last Name`  
 ";

对如何简化此操作有何建议？

Answer 1

您可以使表 data 的这些自联接更加明确，这使查询更具可读性，但很可能不会影响速度。即:

SELECT FN.sub_id, FN.field_val AS `First Name`, 
                  LN.field_val AS `Last Name`, 
                  DOB.field_val AS `dob`, 
                  EMAIL.field_val AS `email`, 
                  PHONE.field_val AS `phone`  
FROM  $db->data FN
JOIN  $db->data LN ON (LN.field_name = 'Last Name' AND LN.sub_id = FN.sub_id)
JOIN  $db->data TEAM ON (TEAM.field_name = 'Team' AND TEAM.sub_id = FN.sub_id)
JOIN  $db->data DOB ON (DOB.field_name = 'DOB' AND DOB.sub_id = FN.sub_id)
JOIN  $db->data EMAIL ON (EMAIL.field_name = 'EMail' AND EMAIL.sub_id = FN.sub_id)
JOIN  $db->data PHONE ON (PHONE.field_name = 'Telephone' AND PHONE.sub_id = FN.sub_id)
WHERE FN.field_name = 'First Name'
ORDER BY LN.field_val

基本上，许多乏味的自联接是您为这种“灵活”的表组织(作为属性名称和值的集合)付出的代价。

顺便说一句，如果某个 sub_id 的某些数据可能丢失，并且您仍希望在输出中看到该行(缺少的数据为 NULL)，请对该字段的实例使用 LEFT JOIN 而不是普通 JOIN上述查询中的数据。