mysql - 对实体属性值模式中的两列进行排序

Question

我们正在使用实体-属性-值架构来存储项目中的对象。基本上是这样的:

实体 - 所有条目原型(prototype)(汽车、房屋等)的虚拟存储

条目(单个实体对象的表示)

• 编号
• 实体编号
• ...

参数(所有实体属性颜色、名称、品牌等)

• 编号
• 姓名
• …

值(一个条目和参数的单个值)

• 编号
• entry_id(entry.id 的外键)
• parameter_id(parameter.id 的外键)
• 值(value)(实际值(value))

这些表代表存储在 MySQL 数据库中的动态表。

此方案的唯一问题是对 2 个(或更多)参数进行排序。例如，按 manufacturing_year DESC 和品牌 ASC 对所有汽车进行排序。

我们需要的最终结果是排序的条目 ID 列表。

对于这些对象:

实体

id | name
------------------------------
1  | Car
------------------------------

条目

id| entity_id
-------------
1 | 1
-------------
2 | 1
-------------
3 | 1
-------------

参数

id| entity_id | name
-----------------------
1 | 1         | Brand
-----------------------
2 | 1         | Year 
-----------------------

值(value)

id | entry_id | parameter_id | value 
----------------------------------------------
1  | 1        | 1            | Tatra
----------------------------------------------
2  | 1        | 2            | 2005
----------------------------------------------
3  | 2        | 1            | Aston Martin
----------------------------------------------
4  | 2        | 2            | 1999
----------------------------------------------
5  | 3        | 1            | Man
----------------------------------------------
6  | 3        | 2            | 2005
----------------------------------------------

根据给定的标准，这些条目的正确顺序应该是 3、1、2。

Answer 1

您可以根据要排序的参数多次连接参数表，参数 ID 作为连接条件，因此您得到一个简单的行集，其中值作为用于排序的列。

SELECT entry.id as entry_id, 
       v_brand.value as brand,
       v_year.value as year 
FROM entity
  JOIN entry 
    ON entity.id = entry.entity_id
  JOIN value v_brand 
    ON v_brand.parameter_id = 1 
    AND v_brand.entry_id = entry.id
  JOIN value v_year 
    ON v_year.parameter_id = 2 
    AND v_year.entry_id = entry.id
WHERE entity.name = 'Car'
ORDER BY year DESC, brand ASC