请考虑下表：
发票
+-----------+----+------------+--------+---------+
| accountid | id | customerid | total | balance |
+-----------+----+------------+--------+---------+
| 1 | 2 | 167909 | 120060 | 120060 |
+-----------+----+------------+--------+---------+
invoices_attributes
+-----------+----+--------------+
| accountid | id | name |
+-----------+----+--------------+
| 1 | 1 | registration |
+-----------+----+--------------+
| 1 | 2 | claimnumber |
+-----------+----+--------------+
| 1 | 3 | jobid |
+-----------+----+--------------+
invoices_attributes_values
+------------------+-------------+-----------+---------------+
| 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 |
+-----------+-----------+------------+--------+--------------+-------------+----------+
当我GROUP BY invoices.id时，某些列（注册，索赔人数或工作）将变为NULL。我希望结果是：
+-----------+-----------+------------+--------+--------------+-------------+----------+
| accountid | invoiceid | customerid | total | registration | claimnumber | jobid |
+-----------+-----------+------------+--------+--------------+-------------+----------+
| 1 | 2 | 167909 | 120060 | ABC 126L | ABZ123 | MARY DOE |
+-----------+-----------+------------+--------+--------------+-------------+----------+
如何修改查询以获得上面的结果？
最佳答案
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'
关于mysql - MySQL加入三表EAV模型，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39778359/