mysql按列查询排序,从另一个表中按字段排序查询结果（MySQL）

I have a MySQL table structure and need to get a specific query working but I just can't wrap my head around it somehow. First off, here's the relevant table structure.

folders:

+--------------+--------------+

| Field | Type |

+--------------+--------------+

| id | int(11) |

| project_id | int(11) |

| finished | int(11) |

| approved | int(11) |

| comment | varchar(500) |

+--------------+--------------+

werte:

+---------------------+---------------+

| Field | Type |

+---------------------+---------------+

| id | int(11) |

| folder_id | int(11) |

| form_id | int(11) |

| werte | varchar(5000) |

| number | int(11) |

+---------------------+---------------+

Now I have a situation where I want to retrieve the id field from folders in a certain order. The first couple of sortings I need are easy to achieve, since the order is determined by fields in the table. For example

SELECT id FROM folders ORDER BY approved ASC;

However I need another ordering where the ids are sorted by a value in the werte column from the werte table. To make it a bit less abstract, the werte table holds parameter values, like dates in this example. Folders can contain multiple forms, which in turn contain multiple werte. So my goal is this: Get me all ids from folders, ordered by the werte column from the werte table. Expect the werte column to contain a date string, so using STR_TO_DATE(werte, '%d.%m.%Y') is what is needed there.

A valid query to retrieve all werte fields containing a date would be

SELECT STR_TO_DATE(werte, '%d.%m.%Y') FROM werte where number = 2;

I just can't figure out how to put that together with the first query I talked about (selecting all ids from folders).

解决方案

If I understand correctly, you would use join:

select f.*

from folders f join

werte w

on w.folder_id = f.id

where w.number = 2

order by f.approved, str_to_date(werte, '%d.%m.%Y');

As a note: if you are doing to store dates in strings, then use the ISO standard format YYYY-MM-DD. If you used this format, the str_to_date() would be unnecessary, because an alphabetic ordering of the string representation would be correct.

Also, if there might not be a match in the werte table, then you should use left join instead of inner join. The above assumes there is a match (or you only want rows that have a date).