我有过
name: date:
Mike 02-10-14
Will 03-31-18
Charlie 04-21-18
Mike 05-31-18
Will 04-11-17
Charlie 04-15-18
Mike 08-02-17
Will 09-16-18
Charlie 04-12-17
... ...
对于每个名称,我要按降序排列最近的(2)个日期。
name: date:
Mike 05-31-18
Mike 08-02-17
Will 09-16-18
Will 03-31-18
Charlie 04-21-19
Charlie 04-15-19
我已经尝试了各种group by / order by / max / desc / unique / distinct方法,但找不到保留所有名称的解决方案。我一直在尝试抓取整个表格中最近的两个日期,而不是每个人。
当前尝试:
SELECT NAME, DATE FROM SIGHTINGS ORDER BY SIGHTED DESC LIMIT 2;
--返回整个表的2个最近日期
发布于 2018-12-13 06:48:16
这是可行的,因为它通过将日期重新格式化为YY-MM-DD
进行排序
SELECT
t.NAME, t.DATE
FROM SIGHTINGS AS t
WHERE
t.DATE IN (
SELECT
DATE
FROM SIGHTINGS
WHERE SIGHTINGS.NAME = t.NAME
ORDER BY SUBSTR(DATE, 7, 2) || '-' || SUBSTR(DATE, 1, 5) DESC LIMIT 2
)
ORDER BY t.NAME, SUBSTR(t.DATE, 7, 2) || '-' || SUBSTR(t.DATE, 1, 5) DESC;
https://stackoverflow.com/questions/53752184
复制相似问题