使 SQL 多次选择同一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2472662/
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
Make SQL Select same row multiple times
提问by Snoop Dogg
I need to test my mail server. How can I make a Select statement that selects say ID=5469 a thousand times.
我需要测试我的邮件服务器。我怎样才能做出一个 Select 语句来选择说 ID=5469 一千次。
回答by Robin Day
If I get your meaning then a very simple way is to cross join on a derived query on a table with more than 1000 rows in it and put a top 1000 on that. This would duplicate your results 1000 times.
如果我明白你的意思，那么一个非常简单的方法是在一个表中的派生查询上交叉联接，其中的行超过 1000 行，并在其上放置前 1000。这将重复您的结果 1000 次。
EDIT: As an example (This is MSSQL, I don't know if Access is much different)
编辑：举个例子（这是MSSQL，我不知道Access是否有很大不同）
SELECT
MyTable.*
FROM
MyTable
CROSS JOIN
(
SELECT TOP 1000
*
FROM
sysobjects
) [BigTable]
WHERE
MyTable.ID = 1234
回答by TLiebe
You can use the UNION ALL statement.
您可以使用 UNION ALL 语句。
Try something like:
尝试类似：
SELECT * FROM tablename WHERE ID = 5469
UNION ALL
SELECT * FROM tablename WHERE ID = 5469
You'd have to repeat the SELECT statement a bunch of times but you could write a bit of VB code in Access to create a dynamic SQL statement and then execute it. Not pretty but it should work.
您必须多次重复 SELECT 语句，但您可以在 Access 中编写一些 VB 代码来创建动态 SQL 语句，然后执行它。不漂亮，但它应该工作。
回答by van
Create a helper table for this purpose:JUST_NUMBER(NUM INT primary key)
Insert (with the help of some (VB) script) numbers from 1 to N. Then execute this unjoined query:
为此创建一个辅助表：JUST_NUMBER(NUM INT primary key)
插入（借助一些（VB）脚本）从 1 到 N 的数字。然后执行这个未连接的查询：
SELECT MYTABLE.*
FROM MYTABLE,
JUST_NUMBER
WHERE MYTABLE.ID = 5469
AND JUST_NUMBER.NUM <= 1000
回答by spender
Here's a way of using a recursive common table expression to generate some empty rows, then to cross join them back onto your desired row:
这是一种使用递归公用表表达式生成一些空行，然后将它们交叉连接回所需行的方法：
declare @myData table (val int) ;
insert @myData values (666),(888),(777) --some dummy data
;with cte as
(
select 100 as a
union all
select a-1 from cte where a>0
--generate 100 rows, the max recursion depth
)
,someRows as
(
select top 1000 0 a from cte,cte x1,cte x2
--xjoin the hundred rows a few times
--to generate 1030301 rows, then select top n rows
)
select m.* from @myData m,someRows where m.val=666
substitute @myDatafor your real table, and alter the final predicate to suit.
替换@myData您的真实表，并更改最终谓词以适应。
回答by Elton Joani
easy way...
简单的方法...
This exists only one row into the DB
这仅存在于数据库中的一行
sku = 52 , description = Skullcandy Inkd Green ,price = 50,00
Try to relate another table in which has no constraint key to the main table
尝试将另一个没有约束键的表与主表相关联
Original Query
原始查询
SELECT Prod_SKU , Prod_Descr , Prod_Price FROM dbo.TB_Prod WHERE Prod_SKU = N'52'
The Functional Query ...adding a not related table called 'dbo.TB_Labels'
功能查询 ...添加一个名为“dbo.TB_Labels”的不相关表
SELECT TOP ('times') Prod_SKU , Prod_Descr , Prod_Price FROM dbo.TB_Prod,dbo.TB_Labels WHERE Prod_SKU = N'52'
回答by Krauss
In postgres there is a nice function called generate_series. So in postgreSQLit is as simple as:
在 postgres 中有一个很好的函数叫做 generate_series。所以在postgreSQL 中它很简单：
select information from test_table, generate_series(1, 1000) where id = 5469
In this way, the query is executed 1000 times.
这样，查询执行了 1000 次。
Example for postgreSQL:
PostgreSQL 示例：
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; --To be able to use function uuid_generate_v4()
--Create a test table
create table test_table (
id serial not null,
uid UUID NOT NULL,
CONSTRAINT uid_pk PRIMARY KEY(id));
-- Insert 10000 rows
insert into test_table (uid)
select uuid_generate_v4() from generate_series(1, 10000);
-- Read the data from id=5469 one thousand times
select id, uid, uuid_generate_v4() from test_table, generate_series(1, 1000) where id = 5469;
As you can see in the result below, the data from uid is read 1000 times as confirmed by the generation of a new uuid at every new row.
正如你在下面的结果中看到的，来自 uid 的数据被读取了 1000 次，这通过在每一个新行生成一个新的 uuid 来确认。
id |uid |uuid_generate_v4
----------------------------------------------------------------------------------------
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"5630cd0d-ee47-4d92-9ee3-b373ec04756f"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"ed44b9cb-c57f-4a5b-ac9a-55bd57459c02"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"3428b3e3-3bb2-4e41-b2ca-baa3243024d9"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"7c8faf33-b30c-4bfa-96c8-1313a4f6ce7c"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"b589fd8a-fec2-4971-95e1-283a31443d73"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"8b9ab121-caa4-4015-83f5-0c2911a58640"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"7ef63128-b17c-4188-8056-c99035e16c11"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"5bdc7425-e14c-4c85-a25e-d99b27ae8b9f"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"9bbd260b-8b83-4fa5-9104-6fc3495f68f3"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"c1f759e1-c673-41ef-b009-51fed587353c"
5469|"10791df5-ab72-43b6-b0a5-6b128518e5ee"|"4a70bf2b-ddf5-4c42-9789-5e48e2aec441"
Of course other DBs won't necessarily have the same function but it could be done:
当然，其他数据库不一定具有相同的功能，但可以做到：
See here.
见这里。
回答by Joe Pitz
If your are doing this in sql Server
如果您在 sql Server 中执行此操作
declare @cnt int
set @cnt = 0
while @cnt < 1000
begin
select '12345'
set @cnt = @cnt + 1
end
select '12345'can be any expression
select '12345'可以是任何表达式
回答by Selim Reza
Repeat rows based on column value of TestTable. First run the Create table and insert statement, then run the following query for the desired result. This may be another solution:
根据 TestTable 的列值重复行。首先运行 Create table 和 insert 语句，然后运行以下查询以获得所需的结果。这可能是另一种解决方案：
CREATE TABLE TestTable
(
ID INT IDENTITY(1,1),
Col1 varchar(10),
Repeats INT
)
INSERT INTO TESTTABLE
VALUES ('A',2), ('B',4),('C',1),('D',0)
WITH x AS
(
SELECT TOP (SELECT MAX(Repeats)+1 FROM TestTable) rn = ROW_NUMBER()
OVER (ORDER BY [object_id])
FROM sys.all_columns
ORDER BY [object_id]
)
SELECT * FROM x
CROSS JOIN TestTable AS d
WHERE x.rn <= d.Repeats
ORDER BY Col1;
回答by Alekzander
create table #tmp1 (id int, fld varchar(max)) insert into #tmp1 (id, fld) values (1,'hello!'),(2,'world'),(3,'nice day!')
创建表 #tmp1 (id int, fld varchar(max)) 插入 #tmp1 (id, fld) 值 (1,'hello!'),(2,'world'),(3,'nice day!')
select * from #tmp1 go
select * from #tmp1 go
select * from #tmp1 where id=3 go 1000
select * from #tmp1 where id=3 go 1000
drop table #tmp1
删除表#tmp1
回答by Gabe
The easy way is to create a table with 1000 rows. Let's call it BigTable. Then you would query for the data you want and join it with the big table, like this:
简单的方法是创建一个包含 1000 行的表。让我们称之为BigTable。然后，您将查询所需的数据并将其与大表连接，如下所示：
SELECT MyTable.*
FROM MyTable, BigTable
WHERE MyTable.ID = 5469
