SQL SQL如何创建重复记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10070816/
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
How can SQL create duplicate records?
提问by ypercube??
I was having a look at this question: Getting random value from a SQLite tableand it got me wondering if one can duplicate records using SQL. More specifically, is there a construct that allows to transform these records:
我正在看这个问题：从 SQLite 表中获取随机值，这让我想知道是否可以使用 SQL 复制记录。更具体地说，是否存在允许转换这些记录的构造：
id| name| count
---------------
1| Anne| 3
2| Joe| 2
into:
进入：
id| name
--------
1| Anne
1| Anne
1| Anne
2| Joe
2| Joe
where the count of Anneand Joerecords in the latter matches the corresponding countvalue in the former set of records.
其中后者中的Anne和Joe记录数与count前一组记录中的相应值相匹配。
Can it be done with SQL? If so, how?
可以用SQL来完成吗？如果是这样，如何？
EDIT:
编辑：
Lucero asks what SQL dialect; any really, but ideally the most agnostic approach would be the best.
Lucero 询问什么 SQL 方言；任何真的，但理想情况下，最不可知的方法将是最好的。
ANOTHER EDIT:
另一个编辑：
Can it be done within a single SQL statement in say sqlite?
可以在说 sqlite 的单个 SQL 语句中完成吗？
回答by ypercube??
You can use a "numbers" table (it's handy for various operations):
您可以使用“数字”表（它对各种操作都很方便）：
CREATE TABLE num
( i UNSIGNED INT NOT NULL
, PRIMARY KEY (i)
) ;
INSERT INTO num (i)
VALUES
(1), (2), ..., (1000000) ;
Then:
然后：
SELECT
t.id, t.name
FROM
tableX AS t
JOIN
num
ON num.i <= t."count"
Warning: There is of course a limitation on this approach. The query will not produce all the wanted rows, as soon as you have a value in the countcolumn that exceeds the maximum value stored in the Numbers table. If the values in the countcolumn are unbounded, then only an iterative or recursive solution (like the other two answers) can work.
警告：这种方法当然有局限性。只要count列中的值超过 Numbers 表中存储的最大值，查询就不会生成所有需要的行。如果count列中的值是无界的，则只有迭代或递归解决方案（如其他两个答案）可以工作。
回答by Lucero
What SQL dialect and version? It can eaily be done with a recursive CTE.
什么 SQL 方言和版本？它可以通过递归 CTE 轻松完成。
WITH cte AS (
SELECT id, name, count FROM tbl
UNION ALL
SELECT id, name, count-1 FROM cte WHERE count>1
)
SELECT id, name FROM cte
回答by Jon Egerton
This can be done using a recursive CTE:
这可以使用递归 CTE 来完成：
;WITH CTE_Data as (
select id=1, name='Anne',[COUNT]=3
union select id=2, name='Joe',[COUNT]=2
),
CTE_List as (
select
id,
name,
ind=1
from CTE_Data
union all
select
l.id,
l.name,
ind=ind+1
from CTE_List l
join CTE_Data d on l.id = d.id
where
l.ind < d.[count]
)
select id,name from CTE_List
order by id,ind
NOTE: CTE_List is the interesting one, CTE_Data is just the source data for testing.
注意：CTE_List 是有趣的，CTE_Data 只是用于测试的源数据。
