选择具有其他表列的最小日期(Select min date with another table columns)

我想为用户费率选择上次创建的字段。 下面是我尝试选择数据的表截图的链接。

利率

领域

我的疑问是

select fields.content, min(fields.creation_date) as created from fields left join rates on fields.rate_id=rates.rate_id where rates.user_id=4 group by fields.content

结果是

内容 创建 dolor坐 2016-10-28 19:33:50 lorem ipsum 2016-10-28 19:33:49

如何修改我的查询以获得如下结果？

内容 创建 lorem ipsum 2016-10-28 19:33:49

I want to select last created field for user rate. Below are links to screenshots of tables from which i try to select data.

rates

fields

My query is

select fields.content, min(fields.creation_date) as created from fields left join rates on fields.rate_id=rates.rate_id where rates.user_id=4 group by fields.content

and result is

content              created                             dolor sit             2016-10-28 19:33:50        lorem ipsum      2016-10-28 19:33:49       

How to modify my query to have result like below?

content              created                             lorem ipsum      2016-10-28 19:33:49       

最满意答案

使用order by和limit ：

select f.* from fields f left join rates r on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

请注意，如果存在tie（两个记录具有相同的creation_date ），那么这将任意选择一个。

另外，我怀疑你想要：

select f.* from rates r left join fields f on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

在您的版本中， left join不是必需的 - 它由where子句转换为内连接。 此外，我希望f.rate_id始终匹配r.rate_id ，特别是如果你有一个正确声明的外键关系。 即使fields中没有记录（这似乎是您的意图），这也将返回一行。

Use order by and limit:

select f.* from fields f left join rates r on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

Note that if there are ties (two records with the same creation_date), then this will choose one arbitrarily.

Also, I suspect that you want:

select f.* from rates r left join fields f on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

In your version, the left join is not necessary -- it is turned to an inner join by the where clause. Further, I would expect f.rate_id to always match r.rate_id, especially if you have a properly declared foreign key relationship. This will return a row even if there are no records in fields (which appears to be your intention).

选择具有其他表列的最小日期(Select min date with another table columns)

我想为用户费率选择上次创建的字段。 下面是我尝试选择数据的表截图的链接。

利率

领域

我的疑问是

select fields.content, min(fields.creation_date) as created from fields left join rates on fields.rate_id=rates.rate_id where rates.user_id=4 group by fields.content

结果是

内容 创建 dolor坐 2016-10-28 19:33:50 lorem ipsum 2016-10-28 19:33:49

如何修改我的查询以获得如下结果？

内容 创建 lorem ipsum 2016-10-28 19:33:49

I want to select last created field for user rate. Below are links to screenshots of tables from which i try to select data.

rates

fields

My query is

select fields.content, min(fields.creation_date) as created from fields left join rates on fields.rate_id=rates.rate_id where rates.user_id=4 group by fields.content

and result is

content              created                             dolor sit             2016-10-28 19:33:50        lorem ipsum      2016-10-28 19:33:49       

How to modify my query to have result like below?

content              created                             lorem ipsum      2016-10-28 19:33:49       

最满意答案

使用order by和limit ：

select f.* from fields f left join rates r on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

请注意，如果存在tie（两个记录具有相同的creation_date ），那么这将任意选择一个。

另外，我怀疑你想要：

select f.* from rates r left join fields f on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

在您的版本中， left join不是必需的 - 它由where子句转换为内连接。 此外，我希望f.rate_id始终匹配r.rate_id ，特别是如果你有一个正确声明的外键关系。 即使fields中没有记录（这似乎是您的意图），这也将返回一行。

Use order by and limit:

select f.* from fields f left join rates r on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

Note that if there are ties (two records with the same creation_date), then this will choose one arbitrarily.

Also, I suspect that you want:

select f.* from rates r left join fields f on f.rate_id = r.rate_id where r.user_id = 4 order by f.creation_date asc limit 1;

In your version, the left join is not necessary -- it is turned to an inner join by the where clause. Further, I would expect f.rate_id to always match r.rate_id, especially if you have a properly declared foreign key relationship. This will return a row even if there are no records in fields (which appears to be your intention).