在SQL中需要一个结果集的多个副本,而不使用循环

不知道 更好的 但这确实更具创造性!你可以使用 CROSS JOIN .
编辑: 输入一些代码来生成日期范围,您可以更改日期范围,日期中的行是您的乘数。

  declare @startdate datetime
, @enddate datetime
create table #data1 ([id] int , [name] nvarchar(100))
create table #dates ([date] datetime)


INSERT #data1 SELECT 42,  'South Yorkshire'
INSERT #data1 SELECT 43,  'Lancashire'
INSERT #data1 SELECT 44,  'Norfolk'


set @startdate = '1Jan2010'
set @enddate = '3Jan2010'

WHILE (@startdate <= @enddate)
BEGIN   
INSERT #dates SELECT @startdate
set @startdate=@startdate+1
END


SELECT [id] , [name] from #data1 cross join #dates

drop table #data1
drop table #dates

你可以一直使用 CTE

更换 WHERE Counter < 4 你需要的副本数量。

CREATE TABLE City (ID INTEGER PRIMARY KEY, Name VARCHAR(32))

INSERT INTO City VALUES (42, 'South Yorkshire')
INSERT INTO City VALUES (43, 'Lancashire')
INSERT INTO City VALUES (44, 'Norfolk')

/*
  The CTE duplicates every row from CTE for the amount
  specified by Counter
*/
;WITH CityCTE (ID, Name, Counter) AS 
(
  SELECT  c.ID, c.Name, 0 AS Counter
  FROM    City c
  UNION ALL 
  SELECT  c.ID, c.Name, Counter + 1
  FROM    City c  
          INNER JOIN CityCTE cte ON cte.ID = c.ID
  WHERE   Counter < 4
)
SELECT  ID, Name
FROM    CityCTE
ORDER BY 1, 2

DROP TABLE City

这可能不是最有效的方法,但应该有效。

(select ....)
union all
(select ....)
union all
(select ....)

假设表名为CountyPopulation:

SELECT * FROM CountyPopulation
UNION ALL
SELECT * FROM CountyPopulation
UNION ALL
SELECT * FROM CountyPopulation

分享和享受。

不需要使用光标。基于集合的方法是使用日历表。因此,首先我们制作日历表,它只需要做一次,并且具有一定的永久性:

Create Table dbo.Calendar ( Date datetime not null Primary Key Clustered )
GO
; With Numbers As
(
Select ROW_NUMBER() OVER( ORDER BY S1.object_id ) As [Counter]
From sys.columns As s1
 Cross Join sys.columns As s2
)
Insert dbo.Calendar([Date])
Select DateAdd(d, [Counter], '19000101')
From Numbers
Where [Counter] <= 100000
GO

我在里面放了10万个日期,一共是2300个。显然,你可以一直扩展它。接下来,我们生成测试数据:

Create Table dbo.Data(Id int not null, [Name] nvarchar(20) not null)
GO
Insert dbo.Data(Id, [Name]) Values(42,'South Yorkshire')
Insert dbo.Data(Id, [Name]) Values(43, 'Lancashire')
Insert dbo.Data(Id, [Name]) Values(44, 'Norfolk')
GO

现在问题变得微不足道:

Declare @Start datetime
Declare @End datetime
Set @Start = '2010-01-01'
Set @End = '2010-01-03'
Select Dates.[Date], Id, [Name]
From dbo.Data
 Cross Join (
              Select [Date]
              From dbo.Calendar
              Where [Date] >= @Start
              And [Date] <= @End
             ) As Dates

到目前为止,最好的解决方案是交叉连接。最自然的。

请看我的答案: How to retrieve rows multiple times in SQL Server?

如果你周围有一张数字表,那就更容易了。您可以对日期进行日期比较,以便对数字表进行筛选