MySQL连接水平与垂直表

Question

我有以下结构:

CREATE TABLE person {
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    email VARCHAR(30) NOT NULL UNIQUE
}

CREATE TABLE field {
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
}

CREATE TABLE fieldPerson {
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fieldId INT(11) UNSIGNED,
    personId INT(11) UNSIGNED,
    value VARCHAR(30) NOT NULL,
    FOREIGN KEY (fieldId) REFERENCES field(id),
    FOREIGN KEY (personId) REFERENCES person(id)
}

总结的很好。基本上我们有默认字段 name 和 email，我们可以选择创建自定义字段，例如 telephone、address等我们可以有 n 字段。我正在尝试找出一种方法来执行 SELECT 查询，该查询返回所有字段而不使用子查询或数据库外部的额外工作(尽管使用连接没问题)。

示例(电话是自定义字段):

TABLE person:

id    name       email
1     Test 1     test1@gmail.com

---- 

TABLE field:

id    name
1     telephone
2     address

----

TABLE fieldPerson:

id    fieldId    personId    value
1     1          1           +1 555 555 555
2     2          1           First St.

----   

RESULTING QUERY

personId   name          email            telephone       Address
1          Test 1        test1@gmail.com  +1 555 555 555  First St.

这可能吗？

谢谢。

编辑:

如果不可能，您可以建议使用子查询的最佳解决方案，但只能使用 SQL。

Answer 1

因此，根据您对查询生成的控制程度，如果您可以按照与 的 PRIMARY KEY 相同的顺序生成 SELECT 列字段，您可以尝试以下操作。我正在设想一些你知道 field 中有 2 个键的东西，以及它们是什么(来自早期的查询？)。

SELECT fp.personId,p.name,p.email,fp.value as telephone,fp2.value as Address 
    FROM fieldPerson fp INNER JOIN person p ON p.id = fp.personId 
                        INNER JOIN field f ON f.id = fp.fieldId 
                        INNER JOIN fieldPerson fp2 
    WHERE fp.fieldId = 1 and fp2.fieldId = 2;

根据问题中的样本数据，这给了我结果:

personId | name   | email           | telephone      | Address   |
       1 | Test 1 | test1@gmail.com | +1 555 555 555 | First St. |

这取决于您通过较早地进行初始查询来了解 fields 表中定义的任意字段来避免子查询的概念。您没有指定此表的预期动态程度，因此这可能不是一个不合理的简化假设。

除此之外，您可以尝试以下方法:

SELECT fp.personId,p.name,p.email,fp.value as telephone,fp2.value as Address 
    FROM fieldPerson fp INNER JOIN person p ON p.id = fp.personId 
                        INNER JOIN field f ON f.id = fp.fieldId 
                        INNER JOIN fieldPerson fp2 
    WHERE fp.fieldId != fp2.fieldId LIMIT 1,1;

这为我提供了相同的输出，而无需知道字段 ID 号，但我确实需要知道字段名称才能生成列名称。 LIMIT 用于删除其中一个 JOIN 产品，我同意它有一种明显的邪恶感觉。我觉得方法 2 没有太多优势，因为无论如何您都必须知道列名。

但是，我同意@Mojtaba 的观点，如果您不能忍受通过预查询来了解动态字段名称和 ID，那么我相信您将不得不将 sub-SELECT 语句用于在查询时提取这些语句。