mysql将表以某一列排序,当另一列具有特定值时，MySQL表按一列排序

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 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.

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;

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?

解决方案

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

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