本文介绍了当另一列具有特定值时，MySQL表按一列排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两个具有以下结构的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表按一列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！