MySQL SQL：多次重复结果行，并对行进行编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10423767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Repeat a result row multiple times, and number the rows
提问by cygri
I have a SQL query with a result like this:
我有一个 SQL 查询，结果如下：
value | count
------+------
foo | 1
bar | 3
baz | 2
Now I want to expand this so that each row with a countlarger than 1 occurs multiple times. I also need these rows to be numbered. So I would get:
现在我想扩展它，以便count大于 1 的每一行出现多次。我还需要对这些行进行编号。所以我会得到：
value | count | index
------+-------+------
foo | 1 | 1
bar | 3 | 1
bar | 3 | 2
bar | 3 | 3
baz | 2 | 1
baz | 2 | 2
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.
我必须在所有主要数据库（Oracle、SQL Server、MySQL、PostgreSQL 等等）上进行这项工作。因此，适用于不同数据库的解决方案将是理想的，但可以使用巧妙的方法使其适用于任何数据库。
采纳答案by Michael Buen
For MySQL, use the poor man's generate_series, which is done via views. MySQL is the only RDBMS among big fourthat don't has any CTE feature.
对于 MySQL，使用穷人的generate_series，这是通过视图完成的。MySQL 是四大中唯一没有任何 CTE 功能的RDBMS 。
Actually you can use this technique on database that supports view. So that's virtually all database
实际上，您可以在支持视图的数据库上使用此技术。所以这就是几乎所有的数据库
Generator technique sourced here: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code
生成器技术来源：http: //use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code
The only minor modification we made is we replace the bitwise (shift leftand bitwise or) technique from the original technique with mere multiplication and addition respectively; as Sql Server and Oracle has no shift left operator.
我们所做的唯一小修改是我们分别用乘法和加法替换了原始技术中的按位（左移和按位或）技术；因为 Sql Server 和 Oracle 没有左移运算符。
This abstraction is 99% guaranteed to work on all database, except Oracle; Oracle's SELECTcan't function without any table, in order to do this, one need to select from dummy table, Oracle provided one already, it's called DUALtable. Database portability is a pipe dream :-)
这种抽象保证 99% 适用于所有数据库，Oracle 除外；OracleSELECT没有任何表就无法运行，为了做到这一点，需要从虚拟表中进行选择，Oracle 已经提供了一个，称为DUAL表。数据库可移植性是一个白日梦:-)
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 REPLACEon CREATE VIEW, only Postgresql and MySQL supports them) among all major database.
这是适用于所有 RDBMS 的抽象视图，在所有主要数据库中都没有按位操作（无论如何在这种情况下都不是必需的）和功能细微差别（我们删除OR REPLACE了CREATE VIEW，只有 Postgresql 和 MySQL 支持它们）。
Oracle caveat: Just put FROM DUALafter each SELECTexpression
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;
Then use this query:
然后使用这个查询：
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
甲骨文：http: //www.sqlfiddle.com/#!4/26c05/1
Sql Server: http://www.sqlfiddle.com/#!6/84bee/1
Sql 服务器：http://www.sqlfiddle.com/#!6/84bee /1
回答by Justin Pihony
You could use a numbers table
你可以使用数字表
SELECT value, count, number
FROM table
JOIN Numbers
ON table.count >= Numbers.number
回答by Michael Buen
MySQL is really the IE of the database world, it's such a holdout when it comes to standards and features.
MySQL 真的是数据库世界的 IE，它在标准和功能方面非常顽固。
Works on all major RDBMS except MySQL:
适用于除 MySQL 之外的所有主要 RDBMS：
with
-- Please add this on Postgresql:
-- RECURSIVE
tbl_populate(value, cnt, ndx) as
(
select value, cnt, 1 from tbl
union all
select t.value, t.cnt, tp.ndx + 1
from tbl t
join tbl_populate tp
on tp.value = t.value
and tp.ndx + 1 <= t.cnt
)
select * from tbl_populate
order by cnt, ndx
SQL Server : http://www.sqlfiddle.com/#!6/911a9/1
SQL 服务器：http: //www.sqlfiddle.com/#!6/911a9/1
Oracle : http://www.sqlfiddle.com/#!4/198cd/1
甲骨文：http: //www.sqlfiddle.com/#!4/198cd/1
Postgresql: http://www.sqlfiddle.com/#!1/0b03d/1
回答by Erwin Brandstetter
You asked for a db-agnostic solution and @Justin gave you a nice one.
You also asked for
你要求一个数据库不可知的解决方案，@Justin 给了你一个很好的解决方案。
你还要求
clever ways to make it work on any database
让它在任何数据库上工作的聪明方法
There is one for PostgreSQL: generate_series()does what you asked for out of the box:
PostgreSQLgenerate_series()有一个：开箱即用：
SELECT val, ct, generate_series(1, ct) AS index
FROM tbl;
BTW, I'd rather not use valueand countas column names. It's bad practice to use reserved wordsas identifiers. Using valand ctinstead.
顺便说一句，我宁愿不使用value和count作为列名。使用保留字作为标识符是不好的做法。使用valandct代替。
回答by Aaron Bertrand
Create a numbers table - its definition may vary slightly depending on platform (this is for SQL Server):
创建一个数字表 - 它的定义可能因平台而异（这是针对 SQL Server 的）：
CREATE TABLE Numbers(Number INT PRIMARY KEY);
INSERT Numbers
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns;
Now this temp is also SQL Server, but demonstrates the join syntax that should be valid across the RDBMSes you specify (though I will confess I don't use them so I can't test):
现在这个 temp 也是 SQL Server，但演示了在您指定的 RDBMS 中应该有效的连接语法（尽管我承认我不使用它们所以我无法测试）：
DECLARE @foo TABLE(value VARCHAR(32), [count] INT);
INSERT @foo SELECT 'foo', 1
UNION ALL SELECT 'bar', 3
UNION ALL SELECT 'baz', 2;
SELECT f.value, f.[count], [index] = n.Number
FROM @foo AS f, Numbers AS n
WHERE n.Number <= f.[count];
Results (again, SQL Server):
结果（再次，SQL Server）：
value | count | index
------+-------+------
foo | 1 | 1
bar | 3 | 1
bar | 3 | 2
bar | 3 | 3
baz | 2 | 1
baz | 2 | 2
回答by HABO
For appreciation only, SQL Server 2005 and later can handle this recursively:
仅供欣赏，SQL Server 2005 及更高版本可以递归处理：
declare @Stuff as Table ( Name VarChar(10), Number Int )
insert into @Stuff ( Name, Number ) values ( 'foo', 1 ), ( 'bar', 3 ), ( 'baz', 2 )
select * from @Stuff
; with Repeat ( Name, Number, Counter ) as (
select Name, Number, 1
from @Stuff
where Number > 0
union all
select Name, Number, Counter + 1
from Repeat
where Counter < Number
)
select *
from Repeat
order by Name, Counter -- Group by name.
option ( maxrecursion 0 )
回答by Siyavash Hamdi
By a simple JOINyou can reach to the aim of repeating records n times.
The following query repeats each record 20 times.
通过一个简单的方法，JOIN您可以达到重复记录 n 次的目的。
以下查询将每条记录重复 20 次。
SELECT TableName.*
FROM TableName
JOIN master.dbo.spt_values on type = 'P' and number < 20
Note for master.dbo.spt_values on type = 'P':
This table is used for getting a series of number which is hard-coded in it by condition of type='P'.
注意master.dbo.spt_values on type = 'P'：
此表用于获取由 的条件硬编码在其中的一系列数字type='P'。
回答by Ebrahim Sabeti
You can use of CTE :
您可以使用 CTE ：
WITH Numbers(Num) AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers c
WHERE c.Num < 1000
)
SELECT VALUE,COUNT, number
FROM TABLE
JOIN Numbers
ON TABLE.count >= Numbers.Num
OPTION(MAXRECURSION 1000)
