在表中生成固定数量的行 [英] generate fixed number of rows in a table

问题描述

无法正确表达问题，因此无法搜索我想要的内容.我所需要的只是一个带有一列say guids的虚拟表，我将它用于其他目的.没有实际编写相同的 insert .. newID() n 次，想知道是否有一个优雅的解决方案.

Not able to word the question properly, so couldn't search what I want. All I need is a dummy table with a single column of say guids, which I use it for some other purposes. Without actually writing same insert .. newID() n times, wondering if there is an elegant solution.

类似的问题是如何用一个 int 列填充一个空白表，比如 1-n 个数字.

Similar question would be how do I populate a blank table with a int column with say 1-n numbers.

Row1: 1
Row2: 2
.......
Row100:100

推荐答案

这种方法非常快.如果您需要从无到有生成数字表，这可能是可用的最佳"方法.

This method is blisteringly fast. If you need to generate a numbers table from nothing, it's probably the "best" means available.

WITH
  t0(i) AS (SELECT 0 UNION ALL SELECT 0),
  t1(i) AS (SELECT 0 FROM t0 a, t0 b),
  t2(i) AS (SELECT 0 FROM t1 a, t1 b),
  t3(i) AS (SELECT 0 FROM t2 a, t2 b),
  n(i) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM t3)
SELECT i FROM n WHERE i BETWEEN 1 AND 100

这篇关于在表中生成固定数量的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！