mysql - 如何在 mysql 中自连接多个值？

Question

具体来说，我想根据表的键/值类型中的 N 个元值来查询单词新闻帖子。

我根本找不到有效查询的好示例。

用简单的英语来说，查询就像这样。

选择 city=Dallas、style=ranch、价格在 100k 到 200k 之间、pool=true 的所有帖子

我可能需要比较或多或少的元值。

对于非文字新闻用户，元值位于一个表中，每个元与帖子表中的帖子 ID 相关联。

Answer 1

啊，EAV 的乐趣。简而言之，您需要执行多个子查询或交叉表。

Select ...
From Posts
Where post_id In    (
                    Select post_id
                    From Meta
                    Where Attribute = 'City'
                        And Value = 'Dallas'
                    )
    And post_id In  (
                    Select post_id
                    From Meta
                    Where Attribute = 'Style'
                        And Value = 'Ranch'
                    )
    And post_id In  (
                    Select post_id
                    From Meta
                    Where Attribute = 'Price'
                        And Cast(Value As int) Between 100000 And 200000
                    )
    And post_id In  (
                    Select post_id
                    From Meta
                    Where Attribute = 'Pool'
                        And Value = 'True'
                    )   

这是构建交叉表的另一种形式。它更紧凑，但性能可能不佳:

Select ...
From Posts As P
    Join    (
            Select post_id
                , Min( Case When Attribute = 'City' Then Value End ) As City
                , Min( Case When Attribute = 'Style' Then Value End ) As Style
                , Min( Case When Attribute = 'Price' Then Cast(Value As int) End ) As Price
                , Min( Case When Attribute = 'Pool' Then Value End ) As Pool
            From Meta
            Where Attribute In('City','Style','Price','Pool')
            Group By post_id
            ) As Attributes
        On Attributes.post_id = P.post_id
Where Attributes.City = 'Dallas'
    And Attributes.Style = 'Ranch'
    And Attributes.Price Between 100000 And 200000
    And Attributes.Pool = 'True'