MySQL 连接三表 EAV 模型

Question

请考虑下表:

发票

+-----------+----+------------+--------+---------+
| accountid | id | customerid | total  | balance |
+-----------+----+------------+--------+---------+
| 1         | 2  | 167909     | 120060 | 120060  |
+-----------+----+------------+--------+---------+

发票属性

+-----------+----+--------------+
| accountid | id | name         |
+-----------+----+--------------+
| 1         | 1  | registration |
+-----------+----+--------------+
| 1         | 2  | claimnumber  |
+-----------+----+--------------+
| 1         | 3  | jobid        |
+-----------+----+--------------+

发票属性值

+------------------+-------------+-----------+---------------+
| attributevalueid | attributeid | invoiceid | value         |
+------------------+-------------+-----------+---------------+
| 1                | 1           | 2         | ABC 126L      |
+------------------+-------------+-----------+---------------+
| 2                | 2           | 2         | ABZ123        |
+------------------+-------------+-----------+---------------+
| 3                | 3           | 2         | MARY DOE      |
+------------------+-------------+-----------+---------------+

通过 Eugen Rieck's 的帮助原始答案我能够进行以下查询

SELECT
  invoices.accountid,
  invoices.id AS invoiceid,
  invoices.customerid,
  invoices.total,
  registration.value AS registration,
  claimnumber.value AS claimnumber,
  jobid.value as jobid
  FROM
  invoices
  LEFT JOIN invoice_attributes ON invoices.accountid=invoice_attributes.accountid
  LEFT JOIN invoice_attribute_values AS registration ON registration.attributeid = invoice_attributes.id AND invoices.id = registration.invoiceid AND invoice_attributes.name = 'registration'
  LEFT JOIN invoice_attribute_values AS claimnumber ON claimnumber.attributeid = invoice_attributes.id AND invoices.id = claimnumber.invoiceid AND invoice_attributes.name = 'claimnumber'
  LEFT JOIN invoice_attribute_values AS jobid ON jobid.attributeid = invoice_attributes.id AND invoices.id = jobid.invoiceid AND invoice_attributes.name = 'jobid'

得到以下结果

+-----------+-----------+------------+--------+--------------+-------------+----------+
| accountid | invoiceid | customerid | total  | registration | claimnumber | jobid    |
+-----------+-----------+------------+--------+--------------+-------------+----------+
| 1         | 2         | 167909     | 120060 | NULL         | NULL        | MARY DOE |
+-----------+-----------+------------+--------+--------------+-------------+----------+
| 1         | 2         | 167909     | 120060 | NULL         | ABZ123      | NULL     |
+-----------+-----------+------------+--------+--------------+-------------+----------+
| 1         | 2         | 167909     | 120060 | ABC 126L     | NULL        | NULL     |
+-----------+-----------+------------+--------+--------------+-------------+----------+

当我按invoices.id 进行分组时，某些列(registration、claimnumner 或job)将变为NULL。我希望结果是这样的:

+-----------+-----------+------------+--------+--------------+-------------+----------+
| accountid | invoiceid | customerid | total  | registration | claimnumber | jobid    |
+-----------+-----------+------------+--------+--------------+-------------+----------+
| 1         | 2         | 167909     | 120060 | ABC 126L     | ABZ123      | MARY DOE |
+-----------+-----------+------------+--------+--------------+-------------+----------+

如何修改查询以获得上面的结果？

Answer 1

SQL 没有规定使列依赖于数据。但是，您可以创建一个包含所有可能属性的查询，类似于

基本上你想重新规范化 EVA 结构 - 这当然是可能的:

SELECT
  invoices.accountid,
  invoices.id AS invoiceid
  invoices.customerid,
  invoices.total,
  jobids.value AS jobid -- one of these lines per attriubute
FROM
  invoices
  LEFT JOIN invoices_attributes ON invoices.accountid=invoices_attributes.accountid
  -- One of the following joins per attribute
  LEFT JOIN invoices_attributes_values AS jobids
    ON jobids.attr_id=invoices_attributes.attr_id
    AND jobids.accountid=invoices.accountid
    AND jobids.invoiceid=invoices.id
    AND invoices_attributes.attr_name='jobid'