从另一个表中按字段对查询结果进行排序(MySQL) [英] Ordering query result by field from a different table (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.

文件夹:

+--------------+--------------+
| 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)       |
+---------------------+---------------+

现在我遇到一种情况，我想按特定顺序从文件夹中检索 id 字段.我需要的前几对排序很容易实现，因为顺序是由表中的字段确定的.例如

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;

但是，我需要另一种排序方式，其中 id 由 werte 表中的 werte 列中的值排序.为了使其更抽象一些， werte 表包含参数值，例如本示例中的日期. 文件夹可以包含多个 forms ，而表单又包含多个 werte .所以我的目标是:从文件夹中获取所有ID，按werte表中werte列的顺序排列.期望 werte 列包含一个日期字符串，因此需要使用 STR_TO_DATE(werte，'％d.％m.％Y').

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.

检索包含日期的所有 werte 字段的有效查询将是

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;

我只是不知道如何将其与我所谈论的第一个查询(从文件夹中选择所有 id s)结合起来.

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

推荐答案

如果我理解正确，则可以使用join:

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');

请注意:如果您要以字符串形式存储日期，请使用ISO标准格式YYYY-MM-DD.如果使用此格式，则str_to_date()将是不必要的，因为字符串表示形式的字母顺序是正确的.

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.

此外，如果werte表中可能没有匹配项，则应使用left join而不是inner join.上面假设有一个匹配项(或者您只想要有日期的行).

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

这篇关于从另一个表中按字段对查询结果进行排序(MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！