我在这里看到过类似的问题,但一直找不到符合我特定场景的问题。我有以下三张表:
content
id, [other fields]
attributes
id, title
attributes_values
id, attribute_id[foreign to attributes.id], content_id[foreign to content.id], value
我想要的是一个单独的查询(或一组子查询),它可以返回适当的数据,以避免在编程中处理这些数据。
关于表格的更多信息:属性有三个记录(型号、零件号和在购物车中显示)。
我需要从购物车中显示的attribute_value=1的内容中选择所有记录。除此之外,我希望其他相关的attribute_values作为它们的相关attribute.title返回
id [other fields] Model # [from attributes.title field] Part # [from attributes.title field]
1 [any data] value from attributes_values value from attributes_values
2 [any data] value from attributes_values value from attributes_value
到目前为止,我实现这一点的唯一方法不是很优雅,因为我必须对同一个表执行多个联接:
SELECT * FROM content AS a
LEFT JOIN attributes_values AS b ON (b.content_id = a.id AND b.attribute_id = [Model # ID])
LEFT JOIN attributes_values AS c ON (c.content_id = a.id AND c.attribute_id = [Part # ID])
WHERE a.id IN (
SELECT content_id FROM attributes_values WHERE attribute_id = [Show in Cart ID] AND value = 1
)
正如您所看到的,我不得不将相关的键硬编码到JOIN语句中,这并不能使其具有很强的可扩展性(而且感觉很脏)。
最后几点注意:我正在使用PHP和Joomla!,因此,如果这会影响你的答案(或者如果有一些Joomla的秘密提示),请随意加入它。此外,这是一个预先建立的表模式,我无法更改,所以我需要一个针对上述表的查询解决方案,而不是一个更好的表设计建议。
任何帮助都将不胜感激。
干杯!
您可以将聚合函数(如MAX())与CASE WHEN语句结合使用,而不是为每个属性多次连接表:
SELECT
content.id,
[other_fields],
MAX(CASE WHEN attributes.title='Model #' THEN attributes_values.value END) AS Model_N,
MAX(CASE WHEN attributes.title='Part #' THEN attributes_values.value END) AS Part_N
FROM
content INNER JOIN attributes_values
ON content.id = attributes_values.content_id
INNER JOIN attributes
ON attributes_values.attribute_id = attributes.id
GROUP BY
id,
[other_fields]
HAVING
MAX(CASE WHEN attributes.title='Show in Cart' THEN attribute_values.value END)='1'
一个小小的解释:
CASE WHEN attributes.title='Model #' THEN attributes.value END
将在属性为"Model#"时返回值,否则为NULL,并且
MAX(...)
将返回最大非NULL值,该值为attributes.title='Model#'所在的值。
动态属性
MySQL没有Pivot函数,所以如果你想让你的列表是动态的,你必须创建一个具有所有属性的SQL字符串并执行这个字符串。
您可以从attributes
表中获取所有属性:
SELECT
CONCAT(
'MAX(CASE WHEN attributes.title=''',
REPLACE(attributes.title, '''', ''''''),
''' THEN attributes_values.value END) AS `',
REPLACE(attributes.title, '`', '``'),
'`'
)
FROM
attributes;
并将所有内容与GROUP_CONCAT:组合在一起
SELECT GROUP_CONCAT(...the concat above...)
FROM attributes;
所以你的最终查询是这样的:
SELECT
CONCAT('SELECT content.id,',
GROUP_CONCAT(
CONCAT(
'MAX(CASE WHEN attributes.title=''',
REPLACE(attributes.title, '''', ''''''),
''' THEN attributes_values.value END) AS `',
REPLACE(attributes.title, '`', '``'),
'`'
)
),
' FROM content INNER JOIN attributes_values ',
'ON content.id = attributes_values.content_id ',
'INNER JOIN attributes ',
'ON attributes_values.attribute_id = attributes.id ',
'GROUP BY id HAVING ',
'MAX(CASE WHEN attributes.title=''Show in Cart'' THEN attributes_values.value END)=''1'''
)
FROM
attributes
INTO @SQL;
现在,@SQL变量将保存要执行的查询的值,您可以使用执行它
PREPARE stmt FROM @sql;
EXECUTE stmt;
这里唯一的问题是GROUP_CONCT有一个最大长度限制,如果表包含许多属性,则可以增加它。
请看这里的小提琴示例。