主页 / dba / 问题 / 160354
Accepted
joanolo
Asked: 2017-01-09 15:21:03 +0800 CST

如何在标准 SQL 或 T-SQL 中生成 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... 系列？

  • 772

给定两个数字n和m，我想生成一系列表格

1, 2, ..., (n-1), n, n, (n-1), ... 2, 1

并重复m几次。

例如，对于n = 3and m = 4，我想要以下 24 个数字的序列：

1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------

我知道如何通过以下两种方法之一在 PostgreSQL 中实现此结果：

使用以下查询，它使用该generate_series函数，以及一些技巧来保证顺序是正确的：

WITH parameters (n, m) AS
(
    VALUES (3, 5)
)
SELECT 
    xi
FROM
(
    SELECT
        i, i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
    UNION ALL
    SELECT
        i + parameters.n, parameters.n + 1 - i AS xi
    FROM
        parameters, generate_series(1, parameters.n) AS x(i)
) AS s0 
CROSS JOIN 
    generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
    j, i ;

...或使用带有伴随和嵌套循环的函数用于相同目的：

CREATE FUNCTION generate_up_down_series(
    _elements    /* n */ integer,
    _repetitions /* m */ integer)
RETURNS SETOF integer AS
$BODY$
declare
    j INTEGER ;
    i INTEGER ;
begin
    for j in 1 .. _repetitions loop
        for i in         1 .. _elements loop
              return next i ;
        end loop ;
        for i in reverse _elements .. 1 loop
              return next i ;
        end loop ;
    end loop ;
end ;
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT ;

我怎么可能在标准 SQL 或 Transact-SQL / SQL Server 中做同样的事情？

sql-server postgresql

12 个回答

  1. Postgres

    您可以使其与单一 generate_series()的基本数学一起工作（请参阅数学函数）。

    包装成一个简单的 SQL 函数：

    CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
   SELECT n2m, n2m % (n*2) AS n2
   FROM   generate_series(0, n*2*m - 1) n2m
   ) sub
ORDER  BY n2m
$func$;

    称呼：

    SELECT * FROM generate_up_down_series(3, 4);

    生成所需的结果。n和m可以是n*2*m不溢出的任何整数。int4

    如何？

    在子查询中：

    • 使用简单的升序生成所需的总行数 ( n*2*m )。我命名它n2m。0到N-1（不是1到N）以简化以下模运算。

    • 取% n*2（%是取模运算符）得到一系列n升序数，m次。我命名它n2。

    在外部查询中：

    • 将 1 加到下半部分（n2 < n）。

    • 对于具有n*2 - n2的下半部分的上半部分 ( n2 >= n ) 镜像。

    • 我添加ORDER BY以保证请求的订单。使用当前版本的 Postgres，它也可以在没有ORDER BY简单查询的情况下工作 - 但不一定适用于更复杂的查询！这是一个实现细节（它不会改变），但不是 SQL 标准规定的。

    不幸的generate_series()是，正如已评论的那样，Postgres 是特定的而不是标准的 SQL。但是我们可以重用相同的逻辑：

    标准 SQL

    您可以使用递归 CTE 而不是 生成序列号generate_series()，或者更有效地重复使用，创建一个包含序列整数的表一次。任何人都可以阅读，没有人可以写！

    CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;

    然后，上面SELECT变得更加简单：

    SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i < n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;
    • 12
  2. Best Answer

    TL;DR

    This is a long one, so I'm putting the best (i.e. fastest of my) methods here first. It makes use of the INTARRAY extension - for parameters of 340 and 570, it takes 21ms*. The second best (22 ms - same parameters) only uses standard PostgreSQL constructs. If anyone else comes up with (a) faster method(s), I will put them here and "retire" mine!

    * 8 GB RAM, Intel i5 11th gen CPU, Quad core - 8 threads, NVMe 256 GB drive

    </TL;DR>

    Introduction:

    This question intrigued me (+1) and I pondered

    • a) how to answer it and

    • b) how could the answer be generalised?

    All of the code below can be found in the various fiddles - per method/contributor.

    Interestingly, nobody who's answered so far (10 answers - and some very good quality SQL/programming to boot!) has made use of ARRAYs (tutorial), which I believe are very helpful in this case.

    In a general sense, my approach has been to generate the first series as an ARRAY ({1, 2,.. n, n,..2, 1}) and then generate m of these for the complete task.

    I took three five approaches:

    • the first is PostgreSQL specific, making use of GENERATE_SERIES() (tutorial) and ARRAYs. There's also a function call that can be replaced with a RECURSIVE CTE ("RCTE" - see tutorial).

    • the second (also PostgreSQL specific) combines an RCTE with a call to GENERATE_SERIES() and ARRAYs. The GENERATE_SERIES() can be replaced with an RCTE - see solution 3.

    • the third solution is "Pure SQL" and should work on any RDBMS that supports RCTEs (SQL Server for example) - can also use a numbers table (i.e. a sequence) Following discussions on the dba chatroom, I have removed the requirement for RCTEs to be used to construct sequences.

    • then there are two "speedy" solutions which rely on the fact that UNNEST() preserves order.

    Preparation step:

    I used a table called param to store the values (3 & 5) as per the question - these can obviously be changed. Also, for the benchmarking steps (see below), I used this param table for all queries tested so that there would be an even playing field. As mentioned above, a numbers sequence table is also permitted.

    --
-- Setup of parameters...
--

CREATE TABLE param(n INT, m INT);
INSERT INTO param (VALUES(3, 5));
SELECT * FROM param;


--
-- Setup of numbers
--

CREATE TABLE numbers (n INT);
INSERT INTO numbers
SELECT GENERATE_SERIES(1, ((SELECT m FROM param))); 
SELECT * FROM numbers LIMIT 5;

    The first method is as follows:

    Method 1 - GENERATE_SERIES (fiddle):

    Step 1:

    --
-- Step 1
--
SELECT
  GENERATE_SERIES(1, (SELECT n FROM param)) AS the_first_series
UNION ALL
SELECT 
  GENERATE_SERIES((SELECT n FROM param), 1, -1);

    Result:

    the_first_series
               1
               2
               3
               3
               2
               1

    Step 2:

    --
--  Two possible Step 2s using a PL/pgSQL function or an RCTE
--
CREATE OR REPLACE FUNCTION fill_array_with_seq(the_array anyarray, seq_num INT)
RETURNS ANYARRAY LANGUAGE PLpgSQL AS $$
DECLARE
BEGIN
  FOR i IN 1..seq_num LOOP
    the_array[i] := i;
    i = i + 1;
  END LOOP;
  RETURN the_array;
end $$;

SELECT fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param));

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);

    Results (same):

    fill_array_with_seq
      {1,2,3,4,5,6}
              f_arr
      {1,2,3,4,5,6}

    Step 3:

    --
-- Step 3
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT 
  ROW_NUMBER() OVER () AS rn,
  (
    SELECT f_arr FROM cte_fill_arr
    WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
  ),
  
  -- could use
  --
  -- fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param))
  --
  
  ARRAY
  (
    SELECT 
      GENERATE_SERIES(1, (SELECT n FROM param))
    UNION ALL
    SELECT 
      GENERATE_SERIES((SELECT n FROM param), 1, -1)
  ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x;

    Result:

    rn         f_arr             arr
 1  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 2  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 3  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 4  {1,2,3,4,5,6}   {1,2,3,3,2,1}
 5  {1,2,3,4,5,6}   {1,2,3,3,2,1}

    Finally:

    --
--  Steps 4 & 5 - separate subquery not shown
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;

    Result:

    the_series
         1
         2
         3
         3
         2
         1
         1
         2
...
... snipped for brevity
...

    Method 2 - Recursive CTE (fiddle):

    Here, I managed to "kill two birds with one stone" by constructing both the desired sequence and its numbering scheme in the same RCTE as follows:

    --
-- Step 1
--
WITH RECURSIVE cte_fill_array AS -- (cnt, val_arr, cnt_arr) AS
(
  SELECT 1 AS i, 1 AS cnt, ARRAY[1] AS val_arr, ARRAY[1] AS cnt_arr
  UNION ALL
  SELECT i + 1, cnt + 1, 
  ARRAY_APPEND
  (
    val_arr,
    (
      SELECT 
        CASE
          WHEN cnt < (SELECT n FROM param) THEN (i + 1)
          WHEN cnt = (SELECT n FROM param) THEN cnt
          WHEN cnt > (SELECT n FROM param) THEN 6 - i
        END
    )
  ),
  ARRAY_APPEND(cnt_arr, cnt + 1)
  FROM cte_fill_array
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;

    Result:

    i   cnt val_arr cnt_arr
1   1   {1} {1}
2   2   {1,2}   {1,2}
3   3   {1,2,3} {1,2,3}
4   4   {1,2,3,3}   {1,2,3,4}
5   5   {1,2,3,3,2} {1,2,3,4,5}
6   6   {1,2,3,3,2,1}   {1,2,3,4,5,6}

    We only want the last record, so we select this by using the CARDINALITY() function - where that is equal to (n * 2) is the last record (step not shown individually).

    Final step - see fiddle for more detail

    --
--  Steps 2 - end
--
WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
  SELECT 1 AS n, ARRAY[1]::INT[] AS f_arr
  UNION ALL
  SELECT n + 1, array_append(f_arr, n + 1)
  FROM cte_fill_arr
  WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
  SELECT 
    ROW_NUMBER() OVER () AS rn,
    UNNEST
    (
      (
        SELECT f_arr FROM cte_fill_arr
        WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
      )
    ) AS seq,  
    UNNEST
    (
      ARRAY
      (
        SELECT 
          GENERATE_SERIES(1, (SELECT n FROM param))
        UNION ALL
        SELECT 
          GENERATE_SERIES((SELECT n FROM param), 1, -1)
      )
    ) AS arr
  FROM
    GENERATE_SERIES(1, (SELECT m FROM param)) AS x
  ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;

    Result (same as others):

    the_series
         1
         2
         3
         3
...
... snipped for brevity
...

    A simpler, (and IMHO) more elegant solution exists - using the GENERATE_SUBSCRIPTS() function (explanation) as follows:

    WITH RECURSIVE cte (i) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr
FROM
(
  SELECT 
    x, 
    UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr, 
    GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

  FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;

    Result (same):

    the_series
1
2
3
3
...
... snipped for brevity
...

    Method 3 - Pure SQL (fiddle):

    Final step:

    Since all of the code below has been seen above in one form or another, I'm just including the final step. No PostgreSQL specific functionality has been used and it also works under SQL Server 2019 (Linux fiddle) - also works back to 2016 - all versions.

    WITH RECURSIVE cte (i, cnt) AS
(
  SELECT 1 AS i, 1 AS cnt
  UNION ALL
  SELECT 
    CASE
      WHEN cnt < (SELECT n FROM param) THEN (i + 1)
      WHEN cnt = (SELECT n FROM param) THEN cnt
      ELSE i - 1
    END AS i,
    cnt + 1
  FROM cte
  WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT 
  n.n, c.i, c.cnt
FROM 
  cte c
CROSS JOIN numbers n
ORDER BY n.n, c.cnt;

    Result (same):

    i
1
2
3
3

    4th solution (and clubhouse leader!) (fiddle):

    SELECT UNNEST(arr)
FROM
(
  SELECT arr, GENERATE_SERIES(1, (SELECT m FROM param)) AS gs
  FROM
  (
    SELECT 
    ARRAY
    (
      SELECT x 
      FROM GENERATE_SERIES(1, (SELECT n FROM param)) x
      UNION ALL
      SELECT x
      FROM GENERATE_SERIES((SELECT n FROM param), 1, -1) x
    ) AS arr
  ) AS t
) AS s;

    Same result as for all the others.

    5th solution (Honourable mention) (fiddle):

    SELECT
  UNNEST
  (
    ARRAY_CAT
    (
      ARRAY
      (
        SELECT GENERATE_SERIES(1, (SELECT n FROM param))::INT
      ), 
      ARRAY
      (
        SELECT GENERATE_SERIES((SELECT n FROM param), 1, -1)::INT
      )
    )
  ) FROM GENERATE_SERIES(1, (SELECT m FROM param));

    Same result as for all the others.

    6th Solution (another scorcher! - uses the INTARRAY extension fiddle):

    WITH cte AS
(
  SELECT
    ARRAY(SELECT GENERATE_SERIES(1, (SELECT n FROM param))) AS arr
)
SELECT UNNEST 
(
  (
    SELECT ARRAY_CAT(c.arr, SORT(c.arr, 'DESC'))
    FROM cte c
  )
) FROM GENERATE_SERIES(1, (SELECT m FROM param));

    Same result!

    Benchmarking:

    I benchmarked all of the PostgreSQL solutions.

    I have done my utmost to be fair in these benchmarks - I used a param table (3, 5) (also (34, 57) and (340, 570) at (home)) in my SQL. For those queries requiring a number table (i.e. a sequence), after discussion, I have included it for those queries requiring it. I'm not entirely sure about this, since consultants are frequently forbidden from creating separate tables, no matter how trivial, but this appears to have been the consensus!

    Please let me know if you are unhappy with any of the tests and I'll gladly rerun them!

    I used db<>fiddle for the tests and the usual caveats apply - I don't know what else is running on that server at any given moment in time - I took an average of several runs for each solution (the vast bulk of the results were within ~ 10% of each other - discarded obvious outliers (longer, not shorter times).

    It was pointed out to me (knew anyway) that 3 & 5 aren't very large numbers - I did try with low 100's for each parameter, but the runs kept failing on db<>fiddle.uk, but I would just say that the all of the runs were remarkably consistent with each other, only varying by ~ +- 10%.

    The second reading runs with a are for values of 34 & 57 - feel free to try yourselves.

    With the (home) tests, I used params of (340, 570) on an 8GB machine (i5 - 10th gen, NVMe 256GB) - nothing else running - variance v. low ~ 1/2%!

    • Vérace's (Another scorcher using INTARRAY!) 6th solution (fiddle) (0.110ms)/0.630 ms/21.5 ms (home) - new leader!

    • Vérace's (ex-Clubhouse leader) 4th solution (fiddle) 0.120 ms/0.625 ms/22.5 ms (home)

    • Vérace's (Honourable mention) 5th solution (fiddle) (0.95ms/0.615 (goes down!)/26 ms (home)

    • Vérace GENERATE_SERIES SQL method (fiddle): 0.195 ms/3.1 ms/140ms (home)

    • Vérace RECURSIVE CTE SQL method (fiddle): 0.200 ms/2.9 ms/145m (home)

    • Vérace GENERATE_SUBSCRIPTS() SQL method (fiddle): 0.110 ms/2.75 ms/130ms (home)

    • Vérace "Pure SQL" method (fiddle): 0.134 ms/2.85ms/190ms (home)

    • OP's SQL method (fiddle): 12.50 ms/18.5ms/190ms (home)

    • OP's PL/pgSQL function method (fiddle): 0.60 ms/0.075ms/86ms (home)

    • ypercube's SQL method (fiddle): 0.175 ms, /4.3 ms /240 ms (home)

    • ypercube's alternative method (fiddle): 0.090 ms/0.95 ms/36ms (home)

    • Erwin Brandtstetter's SQL method (fiddle): 2.15 ms /3.65 ms/160ms (home) (160 drops to ~ 100 without the ORDER BY - home)

    • Erwin Brandtstetter's function method (fiddle): 0.169 ms/2.3 ms/180 ms (home)

    • Abelisto's SQL method (fiddle) 0.145/fails if params changed?

    • Evan Carroll's SQL method (fiddle) 0.125 ms/1.1ms/45ms (home)

    • McNet's PL/pgSQL method (fiddle) 0.075 ms/ 0.075 ms/125ms (home)

    Again, I reiterate (at the risk of repeating myself (multiple times! :-) )), if you are unhappy with your benchmark, please let me know and I will include any amendments here - I would just stress that I am genuinely interested in fairness and actually learning from this process - unicorn points are all very well, but my priority is on increasing my (our) knowledge-base!

    The source code of PostgreSQL is a bit above my pay grade, but I believe that operations with GENERATE_SERIES and ARRAYs preserve order - the WITH ORDINALITY implies this (again, I think) - the correct answers come up even without paying attention to ordering (although this not a guarantee)! @ErwinBrandstetter says that:

    • I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

    My understanding is that ARRAYs are fast in PostgreSQL because much of the backend code is implemented through them - but as I said, I'm not really a C expert.

    Current standings (as of 2021-10-27 13:50 UTC) are:

    • Vérace 1st, 2nd & 3rd,
    • ypercube 4th,
    • Evan Carroll 5th
    • the rest of the field...

    I found these posts on ARRAYs from Erwin Brandstetter (1) & a_horse_with_no_name (2) very helpful! Other's that I found helpful were as follows (1, 2).

    • 10

  3. 在 Postgres 中，使用这个generate_series()函数很容易：

    WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p, 
    generate_series(1, p.n) AS gn (i),
    generate_series(1, 2)   AS g2 (i),
    generate_series(1, p.m) AS gm (i)
ORDER BY
    gm.i, g2.i, gn.i ;

    在标准 SQL 中——假设参数 n、m 的大小有合理的限制，即小于一百万——你可以使用一个Numbers表：

    CREATE TABLE numbers 
( n int not null primary key ) ;

    用您的 DBMS 的首选方法填充它：

    INSERT INTO numbers (n)
VALUES (1), (2), .., (1000000) ;  -- some mildly complex SQL here
                                  -- no need to type a million numbers

    然后使用它，而不是generate_series()：

    WITH 
  parameters (n, m) AS
  ( VALUES (3, 5) )
SELECT 
    CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
    parameters AS p
  JOIN numbers AS gn (i) ON gn.i <= p.n
  JOIN numbers AS g2 (i) ON g2.i <= 2
  JOIN numbers AS gm (i) ON gm.i <= p.m 
ORDER BY
    gm.i, g2.i, gn.i ;
    • 7

  4. 如果您需要纯 SQL。从理论上讲，它应该适用于大多数 DBMS（在 PostgreSQL 和 SQLite 上测试）：

    with recursive 
  s(i,n,z) as (
    select * from (values(1,1,1),(3*2,1,2)) as v  -- Here 3 is n
    union all
    select
      case z when 1 then i+1 when 2 then i-1 end, 
      n+1,
      z 
    from s 
    where n < 3), -- And here 3 is n
  m(m) as (select 1 union all select m+1 from m where m < 2) -- Here 2 is m

select n from s, m order by m, i;

    解释

    1. 生成系列 1..n

      假如说n=3

      with recursive s(n) as (
  select 1
  union all
  select n+1 from s where n<3
)
select * from s;

      它非常简单，几乎可以在任何有关递归 CTE 的文档中找到。但是我们需要每个值的两个实例，所以

    2. 生成系列 1,1,..,n,n

      with recursive s(n) as (
  select * from (values(1),(1)) as v
  union all
  select n+1 from s where n<3
)
select * from s;

      这里我们只是将初始值加倍，它有两行，但我们需要的第二行是相反的顺序，所以我们将稍微介绍一下顺序。

    3. 在我们介绍命令之前，请注意这也是一件事。我们可以在起始条件中有两行，每行三列，我们n<3仍然是单列条件。而且，我们仍然只是增加n.

      with recursive s(i,n,z) as (
  select * from (values(1,1,1),(1,1,1)) as v
  union all
  select
    i,
    n+1,
    z 
  from s where n<3
)
select * from s;

    4. 同样，我们可以将它们混合一下，在这里观察我们的起始条件变化：这里我们有一个(6,2),(1,1) 

      with recursive s(i,n,z) as (
  select * from (values(1,1,1),(6,1,2)) as v
  union all
  select
    i,
    n+1,
    z 
  from s where n<3
)
select * from s;

    5. 生成系列 1..n,n..1

      这里的技巧是生成系列 (1..n) 两次，然后简单地更改第二组的顺序。

      with recursive s(i,n,z) as (
  select * from (values(1,1,1),(3*2,1,2)) as v
  union all
  select
    case z when 1 then i+1 when 2 then i-1 end, 
    n+1,
    z 
  from s where n<3
)
select * from s order by i;

      这i是顺序，z是序列的编号（如果需要，也可以是序列的一半）。因此，对于序列 1，我们将顺序从 1 增加到 3，对于序列 2，我们将顺序从 6 减少到 4。最后

    6. 将系列乘以m

      （请参阅答案中的第一个查询）

    • 6

  5. 在 PostgreSQL 中，这很容易，

    CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS setof int AS $$
SELECT x FROM (
  SELECT 1, ordinality AS o, x FROM generate_series(1,n) WITH ORDINALITY AS t(x)
  UNION ALL
  SELECT 2, ordinality AS o, x FROM generate_series(n,1,-1) WITH ORDINALITY AS t(x)
) AS t(o1,o2,x)
CROSS JOIN (
  SELECT * FROM generate_series(1,m)
) AS g(y)
ORDER BY y,o1,o2
$$ LANGUAGE SQL;
    • 4

  6. If you want a portable solution you need to realize that this is basically a mathematical problem.

    Given @n as the highest number of the sequence and @x as the position of the number in that sequence (starting with zero), the following function would work in SQL Server:

    CREATE FUNCTION UpDownSequence
(
    @n int, -- Highest number of the sequence
    @x int  -- Position of the number we need
)
RETURNS int
AS
BEGIN
    RETURN  @n - 0.5 * (ABS((2*((@x % (@n+@n))-@n)) +1) -1)
END
GO

    You can check it with this CTE:

    DECLARE @n int=3;--change the value as needed
DECLARE @m int=4;--change the value as needed

WITH numbers(num) AS (SELECT 0 
                      UNION ALL
                      SELECT num+1 FROM numbers WHERE num+1<2*@n*@m) 
SELECT num AS Position, 
       dbo.UpDownSequence(@n,num) AS number
FROM numbers
OPTION(MAXRECURSION 0)

    (Quick explanation: the function uses MODULO() to create a sequence of repeating numbers and ABS() to turn it into a zig-zag wave. The other operations transform that wave to match the desired result.)

    • 4

  7. 使用迭代器的基本函数。

    T-SQL

    create function generate_up_down_series(@max int, @rep int)
returns @serie table
(
    num int
)
as
begin

    DECLARE @X INT, @Y INT;
    SET @Y = 0;

    WHILE @Y < @REP
    BEGIN
    
        SET @X = 1;
        WHILE (@X <= @MAX)
        BEGIN
            INSERT @SERIE
            SELECT @X;
            SET @X = @X + 1;
        END
        
        SET @X = @MAX;
        WHILE (@X > 0)
        BEGIN
            INSERT @SERIE
            SELECT @X;
            SET @X = @X -1;
        END
        
        SET @Y = @Y + 1;
    END
    
    RETURN;
end
GO

    Postgres

    create or replace function generate_up_down_series(maxNum int, rep int)
returns table (serie int) as
$body$
declare
    x int;
    y int;
    z int;
BEGIN

    x := 0;
    while x < rep loop
    
        y := 1;
        while y <= maxNum loop
            serie := y;
            return next;
            y := y + 1;
        end loop;
    
        z := maxNum;
        while z > 0 loop
            serie := z;
            return next;
            z := z - 1;
        end loop;
        
        x := x + 1;
    end loop;

END;
$body$ LANGUAGE plpgsql IMMUTABLE STRICT;
    • 3

  8. 这适用于 MS-SQL，我认为可以针对任何 SQL 风格进行修改。

    declare @max int, @repeat int, @rid int

select @max = 3, @repeat = 4

-- create a temporary table
create table #temp (row int)

--create seed rows
while (select count(*) from #temp) < @max * @repeat * 2
begin
    insert into #temp
    select 0
    from (values ('a'),('a'),('a'),('a'),('a')) as a(col1)
    cross join (values ('a'),('a'),('a'),('a'),('a')) as b(col2)
end

-- set row number can also use identity
set @rid = -1

update #temp
set     @rid = row = @rid + 1

-- if the (row/max) is odd, reverse the order
select  case when (row/@max) % 2 = 1 then @max - (row%@max) else (row%@max) + 1 end
from    #temp
where   row < @max * @repeat * 2
order by row
    • 2

  9. 一种使用递归 cte 在 SQL Server 中执行此操作的方法。

    1. 在系列中生成所需数量的成员（对于n =3 和 m=4，它将是 24，即 2 nm ）

    2. 在case表达式中使用逻辑之后，您可以生成所需的系列。

    Sample Demo

    declare @n int=3;--change the value as needed
declare @m int=4;--change the value as needed

with numbers(num) as (select 1 
                      union all
                      select num+1 from numbers where num<2*@n*@m) 
select case when (num/@n)%2=0 and num%@n<>0 then num%@n 
            when (num/@n)%2=0 and num%@n=0 then 1  
            when (num/@n)%2=1 and num%@n<>0 then @n+1-(num%@n)  
            when (num/@n)%2=1 and num%@n=0 then @n
       end as num
from numbers
OPTION(MAXRECURSION 0)

    正如@AndriyM ..所建议的那样，case表达式可以简化为

    with numbers(num) as (select 0
                      union all
                      select num+1 from numbers where num<2*@n*@m-1) 
select case when (num/@n)%2=0 then num%@n + 1
            when (num/@n)%2=1 then @n - num%@n
       end as num
from numbers
OPTION(MAXRECURSION 0)

    Demo

    • 2

  10. Using only basic Math + - * / and Modulo:

    SELECT x
    , s = x % (2*@n) +
         (1-2*(x % @n)) * ( ((x-1) / @n) % 2)
FROM (SELECT TOP(2*@n*@m) x FROM numbers) v(x)
ORDER BY x;

    This doesn't require a specific RDBMS.

    With numbers being a number table:

    ...; 
WITH numbers(x) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n0(x)
    CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n1(x)
    CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n2(x)
)
...

    This generate a number table (1-1000) without using a recursive CTE. See Sample. 2nm must be smaller than the number of row in numbers.

    Output with n=3 and m=4:

    x   s
1   1
2   2
3   3
4   3
5   2
6   1
7   1
8   2
... ...

    This version requires a smaller number table (v >= n and v >= m):

    WITH numbers(v) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM (VALUES(1), (2), (3), (4), (5), (6), ...) AS n(x)
)
SELECT ord = @n*(v+2*m) + n
    , n*(1-v) + ABS(-@n-1+n)*v
FROM (SELECT TOP(@n) v FROM numbers ORDER BY v ASC) n(n)
CROSS JOIN (VALUES(0), (1)) AS s(v)
CROSS JOIN (SELECT TOP(@m) v-1 FROM numbers ORDER BY v ASC) m(m)
ORDER BY ord;

    See Sample.

    • 2

相关问题