当另一列具有特定值时，MySQL表按一列排序 [英] MySQL table order by one column when other column has a particular value

问题描述

我有两个具有以下结构的mysql表 record_items ， property_values .

I have two mysql tables record_items,property_values with the following structure.

table : property_values (column REC is foreign key to record_items)

id(PK)|REC(FK)| property | value|
 1    | 1     | name     |   A  |
 2    | 1     | age      |   10 |
 3    | 2     | name     |   B  |
 4    | 3     | name     |   C  |
 5    | 3     | age      |   9  |

table: record_items

id(PK) |col1|col2 |col3| 
1      | v11| v12 | v13|
2      | v21| v22 | v23|
3      | v31| v32 | v33|
4      | v41| v42 | v43|
5      | v51| v52 | v53|

record_items表仅包含有关记录的基本信息，其中property_values表将record_item保留为外键，并且每个属性及其值保存在单独的行中.

record_items table contains only basic information about the record, where as property_values table keeps record_item as a foreign key and each property and its value is saved in a separate row.

现在，我想根据特定属性(例如年龄)对record_items进行排序. 我的HQL查询就像

Now I want to get the record_items sorted based on a particular property, say by age. My HQL query will be like

Select distinct rec from PropertyValues where property="age" order by value;

但是此查询将跳过记录2，因为它没有关于财产年龄的条目. 我希望结果具有按排序顺序包含age属性的记录，再加上根本不具有age属性的记录.我该如何查询?

But this query will be skipping record 2 since it don't have an entry for property age. I expect the result to have the records which contains age property in sort order appended by those which don't have age property at all. How can I query that?

推荐答案

这是一个原始的MySQL查询，可以解决这个问题:

Here is a raw MySQL query which should do the trick:

SELECT t1.*
FROM record_items t1
LEFT JOIN property_values t2
    ON t1.id = t2.REC AND
       t2.property = 'age'
ORDER BY CASE WHEN t2.value IS NULL THEN 1 ELSE 0 END, t2.Value

我注意到您在property_values中的Value列正在混合数字和文本数据.这不能很好地用于排序目的.

I notice that your Value column in property_values is mixing numeric and text data. This won't work well for sorting purposes.

此处演示

这篇关于当另一列具有特定值时，MySQL表按一列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！