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

一尘不染

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

mysql

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

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

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

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，甚至更多）上都可以完成这项工作。因此，一个可以在不同数据库上工作的解决方案将是理想的选择，但是希望能使它在任何数据库上都能工作的聪明方法。

阅读 476

2020-05-17

共1个答案

一尘不染

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

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

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

我们所做的唯一较小的修改就是我们分别用单纯的乘法和加法替换了原始技术中的按位（左移和 按位or ）技术。因为Sql
Server和Oracle没有左移运算符。

保证99％的抽象都可以在除Oracle之外的所有数据库上运行；Oracle
SELECT不能没有任何表，为了做到这一点，需要从虚拟表中选择一个，Oracle已经提供了一个表，称为DUAL表。数据库可移植性是梦dream以求的:-)

这是适用于所有RDBMS的抽象视图，在所有主要数据库中都没有按位操作（在这种情况下，这实际上并不是必需的）和细微差别（我们删除OR REPLACE了CREATE VIEW，仅Postgresql和MySQL支持它们）。

Oracle警告：FROM DUAL在每个SELECT表达式之后

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

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

SQL
Server：http：//www.sqlfiddle.com/#！6 /
84bee / 1

MySQL：http：//www.sqlfiddle.com/#！2 /
78f5b / 1

2020-05-17