Question

我想在《邮政和电信》中建立一个“”的“密码”,从一个编号开始到再次出现。举例来说,我要把第79号的窗口开到第79号,然后重新计算。

number        must be        row_number number
  50                             ?        50
  79                             1        79
  85                             2        85 
  74                             3        74 
  14                             4        14
  79                             1        79
  46                             2        46
  85                             3        85   
  79                             1        79
  45                             2        45

我如何能够这样做?

Answer 1

考虑:

-- temporary test table
CREATE TEMP TABLE tbl (id serial, nr int);
INSERT INTO tbl(nr) VALUES
 (50),(79),(85),(74),(14)
,(79),(46),(85),(79),(45);

SELECT id, nr
       ,CASE WHEN grp > 0 THEN
          row_number() OVER (PARTITION BY grp ORDER BY id)::text
        ELSE  ?  END AS rn      
FROM  (
    SELECT id, nr
          ,sum(CASE WHEN nr = 79 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS grp
    FROM   tbl) x
-- WHERE grp > 0  -- to exclude numbers before the first 79

产生你的结果。

Answer 2

有时会发生一起CTE酒吧......。

-- temporary test table
-- (thanks for that)
CREATE TEMP TABLE tbl (id serial, nr int);
INSERT INTO tbl(nr) VALUES
 (50),(79),(85),(74),(14)
,(79),(46),(85),(79),(45);

-- EXPLAIN ANALYZE
WITH next AS (
    SELECT t1.id AS id
         , t2.id AS next
    FROM tbl t1
    JOIN tbl t2 ON (t2.nr = t1.nr AND t2.id > t1.id)
        WHERE NOT EXISTS ( SELECT *
            FROM tbl nx
            WHERE nx.nr = t1.nr
            AND nx.id > t1.id
            AND nx.id < t2.id
            )
     )
SELECT t0.id
        , t0.nr
        , next.next AS next
FROM tbl t0
LEFT JOIN next ON (next.id=t0.id)
ORDER BY id
    ;

友情链接