SQL:多次重复结果行，并对行进行编号

2021-11-20 00:00:00 postgresql sql mysql oracle sql-server

我有一个 SQL 查询，结果如下:

I have a SQL query with a result like this:

value | count
------+------
foo   |     1
bar   |     3
baz   |     2

现在我想扩展它，以便 count 大于 1 的每一行出现多次.我还需要对这些行进行编号.所以我会得到:

Now I want to expand this so that each row with a count larger than 1 occurs multiple times. I also need these rows to be numbered. So I would get:

value | count | index
------+-------+------
foo   |     1 |     1
bar   |     3 |     1
bar   |     3 |     2
bar   |     3 |     3
baz   |     2 |     1
baz   |     2 |     2

我必须在所有主要数据库(Oracle、SQL Server、MySQL、PostgreSQL 等等)上进行这项工作.因此，适用于不同数据库的解决方案将是理想的选择，但也欢迎使用巧妙的方法使其适用于任何数据库.

I have to make this work on all the major databases (Oracle, SQL Server, MySQL, PostgreSQL, and maybe more). So a solution that works across different databases would be ideal, but clever ways to make it work on any database are appreciated.

推荐答案

对于 MySQL，使用穷人的 generate_series，这是通过视图完成的.MySQL 是四大中唯一没有任何 CTE 功能的 RDBMS.

For MySQL, use the poor man's generate_series, which is done via views. MySQL is the only RDBMS among big four that don't has any CTE feature.

实际上您可以在支持视图的数据库上使用此技术.所以这几乎就是所有的数据库

Actually you can use this technique on database that supports view. So that's virtually all database

生成器技术来源:http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

我们所做的唯一小修改是我们分别用乘法和加法替换了原始技术中的按位(左移和按位或)技术；因为 Sql Server 和 Oracle 没有左移运算符.

The only minor modification we made is we replace the bitwise (shift left and bitwise or) technique from the original technique with mere multiplication and addition respectively; as Sql Server and Oracle has no shift left operator.

这个抽象99%保证适用于所有数据库，Oracle除外；Oracle的SELECT在没有任何表的情况下无法运行，为此，需要从虚拟表中进行选择，Oracle已经提供了一个，称为DUAL表.数据库可移植性是一个白日梦:-)

This abstraction is 99% guaranteed to work on all database, except Oracle; Oracle's SELECT can't function without any table, in order to do this, one need to select from dummy table, Oracle provided one already, it's called DUAL table. Database portability is a pipe dream :-)

这是适用于所有 RDBMS 的抽象视图，没有按位操作(无论如何在这种情况下这并不是必需的)和特征细微差别(我们在 CREATE VIEW 上删除了 OR REPLACE，所有主流数据库中只有 Postgresql 和 MySQL 支持).

Here's the abstracted views that works on all RDBMS, devoid of bitwise operations(which is not really a necessity anyway in this scenario) and feature nuances(we remove OR REPLACE on CREATE VIEW, only Postgresql and MySQL supports them) among all major database.

Oracle 警告:只需将 FROM DUAL 放在每个 SELECT 表达式之后

Oracle caveat: Just put FROM DUAL after each SELECT expression 

CREATE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE VIEW generator_256
AS SELECT ( ( hi.n * 16 ) + lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE VIEW generator_4k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE VIEW generator_64k
AS SELECT ( ( hi.n * 256 ) + lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE VIEW generator_1m
AS SELECT ( ( hi.n * 65536 ) + lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

然后使用这个查询:

SELECT t.value, t.cnt, i.n
FROM tbl t
JOIN generator_64k i 
ON i.n between 1 and t.cnt
order by t.value, i.n

Postgresql:http://www.sqlfiddle.com/#!1/1541d/1

Postgresql: http://www.sqlfiddle.com/#!1/1541d/1

甲骨文:http://www.sqlfiddle.com/#!4/26c05/1

Sql Server:http://www.sqlfiddle.com/#!6/84bee/1

Sql Server: http://www.sqlfiddle.com/#!6/84bee/1

MySQL:http://www.sqlfiddle.com/#!2/78f5b/1

相关文章