SQL:多次重复结果行，并对行编号 [英] SQL: Repeat a result row multiple times, and number the rows

问题描述

我有一个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，请使用穷人的四大中唯一没有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表.数据库可移植性是梦dream以求的:-)

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

Oracle: http://www.sqlfiddle.com/#!4/26c05 /1

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

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

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

这篇关于SQL:多次重复结果行，并对行编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！