在rails 4应用程序中，我试图通过组合多个表来获取一些数据。
SELECT keywords.name, DATE(keyword_histories.created_at) as c, position, keyword_id FROM 'keyword_histories' join keywords on keywords.id = keyword_histories.keyword_id WHERE (keywords.website_id = 3716 and keyword_histories.status = 'finished') AND ('keyword_histories'.'created_at' BETWEEN '2020-03-16 00:00:00' AND '2020-05-15 00:00:00') GROUP BY DATE(keyword_histories.created_at), keyword_histories.keyword_id ORDER BY keywords.name asc, keywords.id asc, keyword_histories.created_at desc, DATE(keyword_histories.created_at) desc;
此查询的当前输出为(此处采用前几行(，
+---------+------------+----------+------------+
| name | c | position | keyword_id |
+---------+------------+----------+------------+
| az | 2020-05-08 | 1 | 3360 |
| bags | 2020-05-08 | 100 | 3314 |
| bags | 2020-04-27 | 100 | 3314 |
| bags | 2020-04-09 | 100 | 3314 |
| bags | 2020-04-08 | 100 | 3314 |
| battery | 2020-05-08 | 100 | 3337 |
| battery | 2020-04-27 | 100 | 3337 |
| books | 2020-05-08 | 100 | 3313 |
| books | 2020-04-27 | 100 | 3313 |
| books | 2020-04-09 | 100 | 3313 |
| books | 2020-04-08 | 100 | 3313 |
在为created_at数据添加GROUP BY之前是这样的(列c已经随着时间的推移而花费以更好地理解(，
+---------+---------------------+----------+------------+
| name | c | position | keyword_id |
+---------+---------------------+----------+------------+
| az | 2020-05-08 11:48:01 | 1 | 3360 |
| az | 2020-05-08 10:30:06 | 1 | 3360 |
| bags | 2020-05-08 11:48:01 | 39 | 3314 |
| bags | 2020-05-08 10:30:06 | 45 | 3314 |
| bags | 2020-05-08 10:24:21 | 46 | 3314 |
| bags | 2020-05-08 10:20:16 | 35 | 3314 |
| bags | 2020-05-08 10:03:55 | 100 | 3314 |
| bags | 2020-04-27 12:45:20 | 100 | 3314 |
| bags | 2020-04-09 08:25:20 | 100 | 3314 |
| bags | 2020-04-09 06:45:48 | 100 | 3314 |
| bags | 2020-04-08 06:52:08 | 100 | 3314 |
| battery | 2020-05-08 11:48:01 | 14 | 3337 |
| battery | 2020-05-08 10:30:06 | 14 | 3337 |
| battery | 2020-05-08 10:24:21 | 12 | 3337 |
| battery | 2020-05-08 10:20:17 | 12 | 3337 |
| battery | 2020-05-08 10:03:55 | 100 | 3337 |
| battery | 2020-04-27 12:45:20 | 100 | 3337 |
如果没有GROUP BY它按desc顺序排序。但我的要求是，即使我为DATE(keyword_histories.created_at)添加GROUP BY，也应该使用keyword_histories.created_at按降序对数据进行排序。
预期输出应该是这样的，
* With time, added for just to know the `datetime` to sort
+---------+---------------------+----------+------------+
| name | c | position | keyword_id |
+---------+---------------------+----------+------------+
| az | 2020-05-08 11:48:01 | 1 | 3360 |
| bags | 2020-05-08 11:48:01 | 39 | 3314 |
| bags | 2020-04-27 12:45:20 | 100 | 3314 |
| bags | 2020-04-09 08:25:20 | 100 | 3314 |
| bags | 2020-04-08 06:52:08 | 100 | 3314 |
| battery | 2020-05-08 11:48:01 | 14 | 3337 |
| battery | 2020-04-27 12:45:20 | 100 | 3337 |
* Without time, exact output required.
+---------+------------+----------+------------+
| name | c | position | keyword_id |
+---------+------------+----------+------------+
| az | 2020-05-08 | 1 | 3360 |
| bags | 2020-05-08 | 39 | 3314 |
| bags | 2020-04-27 | 100 | 3314 |
| bags | 2020-04-09 | 100 | 3314 |
| bags | 2020-04-08 | 100 | 3314 |
| battery | 2020-05-08 | 14 | 3337 |
| battery | 2020-04-27 | 100 | 3337 |
position值是根据最新created_at显示的主要字段。请帮我解决这个问题。
如果可以的话，可以使用嵌套查询。
查看您给出的第二个输出，您没有分组，即
+---------+---------------------+----------+------------+
| name | c | position | keyword_id |
+---------+---------------------+----------+------------+
| az | 2020-05-08 11:48:01 | 1 | 3360 |
| az | 2020-05-08 10:30:06 | 1 | 3360 |
| bags | 2020-05-08 11:48:01 | 39 | 3314 |
| bags | 2020-05-08 10:30:06 | 45 | 3314 |
| bags | 2020-05-08 10:24:21 | 46 | 3314 |
| bags | 2020-05-08 10:20:16 | 35 | 3314 |
| bags | 2020-05-08 10:03:55 | 100 | 3314 |
| bags | 2020-04-27 12:45:20 | 100 | 3314 |
| bags | 2020-04-09 08:25:20 | 100 | 3314 |
| bags | 2020-04-09 06:45:48 | 100 | 3314 |
| bags | 2020-04-08 06:52:08 | 100 | 3314 |
| battery | 2020-05-08 11:48:01 | 14 | 3337 |
| battery | 2020-05-08 10:30:06 | 14 | 3337 |
| battery | 2020-05-08 10:24:21 | 12 | 3337 |
| battery | 2020-05-08 10:20:17 | 12 | 3337 |
| battery | 2020-05-08 10:03:55 | 100 | 3337 |
| battery | 2020-04-27 12:45:20 | 100 | 3337 |
我不知道你是怎么得到的。但我假设你对此有疑问。获得此表后，假设此关系的别名T，则以下查询将提供所需的输出。
select unique_obj.name, unique_obj._date,earlypos.position,unique_obj.id FROM
(
select T1._date,T2.c,T2.position,T1.id from
(
select DATE(c) as _date,max(c) as maxc,id from T
GROUP BY DATE(c), id
) T1 join
(select c,position,id from T) T2
ON (T1.maxc=T2.c) AND (T1.id=T2.id)
) earlypos JOIN
(
select name,DATE(c) as _date,id FROM T
group by name,DATE(c),id
) unique_obj
ON (earlypos._date=unique_obj._date) AND (earlypos.id=unique_obj.id)
ORDER BY unique_obj.name,unique_obj._date desc;
也许，您将能够使用主查询进一步优化它，但这个查询会起作用。
earlypos获取每个c,id组合所需的position。
unique_obj是表的简单投影T具有c,id组合的唯一值。
我假设对于给定的c和id值，name是唯一的。因此，我也把name放在unique_obj的group by。否则，它将成为非聚合属性。
我得到的输出是
+---------+------------+----------+------+
| name | _date | position | id |
+---------+------------+----------+------+
| az | 2020-05-08 | 1 | 3360 |
| bags | 2020-05-08 | 39 | 3314 |
| bags | 2020-04-27 | 100 | 3314 |
| bags | 2020-04-09 | 100 | 3314 |
| bags | 2020-04-08 | 100 | 3314 |
| battery | 2020-05-08 | 14 | 3337 |
| battery | 2020-04-27 | 100 | 3337 |
+---------+------------+----------+------+
这与预期的输出类似。 如果您有任何问题，请发表评论。