Dense_rank 和 sqlite - 可能吗? [英] dense_rank and sqlite - possible?

问题描述

我的情况是我有一个非常小的 PostgresSQL 数据库(大约 10 个表，其中最大的有几千行)，我想以单用户风格使用它应用程序，因此必须安装整个服务器感觉不必要的繁重.所以，我想，SQLite 听起来就像是票.

My situation is that I have a PostgresSQL database that is pretty small (about 10 tables, biggest of which is a couple of thousand rows) and I want to use it from a single-user style application so having to install a whole server feels unnecessarily onerous. So, I thought, well SQLite sounds like it will be just the ticket.

我移植了架构，导入了数据，然后我卡在了一个视图上(实际上，它只有一个视图).我的问题是我正在使用非常方便的 dense_rank 函数.我读了这个:sqlite - Unsupported Sql Analytical Functions 并且可以看到它不是可用，但它提示我它可能在 SQLite::更多但它似乎也不在那里.

I ported over the schema, imported the data and then I got stuck on a view (actually, the only view it has). My problem is that I am using the ever so handy dense_rank function. I read this: sqlite - Unsupported Sql Analytical Functions and can see that it is not available but it tipped me off that it may be in SQLite::More but it doesn't seem to be in there either.

那么，有谁知道我可以在 SQLite 数据库内的视图中使用 dense_rank 函数的任何方法吗?也许我可以使用另一个图书馆?

So, does anyone know of any way I can get the dense_rank function to be available in a view inside a SQLite database? Is there another library I can use perhaps?

推荐答案

您可以使用子查询模拟 DENSE_RANK 函数:

You can emulate DENSE_RANK function with sub-queries:

示例:

SELECT Products.Product,
DENSE_RANK() OVER (ORDER BY Products.Code DESC) AS Rank
FROM Products;

在 SQLite 中:

In SQLite:

SELECT Product,
(SELECT COUNT()+1 FROM (
    SELECT DISTINCT Code FROM Products AS t WHERE Code < Products.Code)
) AS Rank
FROM Products;

这篇关于Dense_rank 和 sqlite - 可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！