我的数据库里有这样的数据
---------------------------------------------------------------
shopid | shopname | keyword | date | filename
---------------------------------------------------------------
1 | shop1 | A_key |2020-04-24| A
---------------------------------------------------------------
1 | shop1 | A_key |2020-04-24| B
---------------------------------------------------------------
1 | shop1 | B_key |2020-04-25| B
---------------------------------------------------------------
1 | shop1 | B_key |2020-04-25| C
---------------------------------------------------------------
2 | shop2 | C_key |2020-04-24| A
---------------------------------------------------------------
2 | shop2 | C_key |2020-04-24| B
---------------------------------------------------------------
3 | shop3 | D_key |2020-04-24| A
---------------------------------------------------------------
3 | shop3 | D_key |2020-04-24| B
---------------------------------------------------------------
3 | shop3 | E_key |2020-04-24| A
---------------------------------------------------------------
3 | shop3 | E_key |2020-04-25| B
---------------------------------------------------------------
我想要的结果是来自最新文件名的最新数据
---------------------------------------------------------------
shopid | shopname | keyword | date | filename
---------------------------------------------------------------
1 | shop1 | A_key |2020-04-24| B
---------------------------------------------------------------
1 | shop1 | B_key |2020-04-25| C
---------------------------------------------------------------
2 | shop2 | C_key |2020-04-24| B
---------------------------------------------------------------
3 | shop3 | D_key |2020-04-24| B
---------------------------------------------------------------
3 | shop3 | E_key |2020-04-24| A
---------------------------------------------------------------
3 | shop3 | E_key |2020-04-25| B
---------------------------------------------------------------
我想知道如何用sql实现这一点我的sql
select *,rank() over (partition by date,keyword order by filename desc) rank
from table
order by date asc;
2条答案
按热度按时间pnwntuvh1#
我想要的结果是来自最新文件名的最新数据
我不完全明白这一点。下面返回每个商店的最新日期行:
yqlxgs2m2#
看起来您希望每个商店/关键字/日期有一行,如果有多行符合此条件,请使用文件名来决定哪个是最新的(c晚于b等)
您可以使用分析工具:
但我敢说,你也可以用标准分组:
如果您想在中开始添加其他详细列,则此方法将失败,但据我从您的q中看到的,没有任何详细列