从表中多次选择记录 [英] Select Records multiple times from table

问题描述

我有一个这样的 SQL 语句:

I have a SQL statement like this:

SELECT 
    location as Location 
FROM
    Table1 
WHERE 
    OnsiteOffshore = 'Offshore' AND Acc_Code = 'ABC' 

UNION 

SELECT 
    Country 
FROM 
    Table1 
WHERE
    OnsiteOffshore = 'Onsite' AND Acc_Code = 'ABC'

此 SQL 查询给出以下结果:

This SQL query gives these results:

Chennai
Bangalore
USA
NewZealand

但由于某些要求，我需要这样的输出:

But due to some requirement I need the output like this:

Chennai
Chennai
Chennai
Chennai
Bangalore
Bangalore
Bangalore
Bangalore
USA
USA
USA
USA
NewZealand
NewZealand
NewZealand
NewZealand

意思是说每个位置需要输出4次.

Mean to say each location needs to be output 4 times.

请帮助如何获得相同的结果.

Pls help how to get the same.

推荐答案

SELECT Location 
FROM Table1
  CROSS JOIN
    ( VALUES (1),(2),(3),(4)
    ) AS four(dummy)

如果 4 不是常量而是(如@xQbert 注意到/询问的那样)是表的行数，则可以使用:

If the 4 is not a constant but (as @xQbert noticed/asked) is the number of rows of the table, you can use this:

SELECT a.Location 
FROM Table1 AS a
  CROSS JOIN
     Table1 AS b

<小时>

如果您没有 Table1 但有任何(无论多么复杂)查询，您都可以将其用于 4 个副本:

---

If you don't have Table1 but any (however complex) query, you could use this for 4 copies:

SELECT Location 
FROM (
       SELECT Location       --- complex query here
       ...                   --- inside parenthesis
     UNION 
       SELECT Country
       ...
     ) AS Table1
  CROSS JOIN
    ( VALUES (1),(2),(3),(4)
    ) AS four(dummy)

或者对于 n 个副本:

WITH cte AS
  ( SELECT Location       --- complex query here
    ...                   --- inside parenthesis
    UNION 
    SELECT Country
    ...
  )
SELECT a.Location 
FROM cte AS a
  CROSS JOIN
     cte AS b

这篇关于从表中多次选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！