Logo MurCode
PostgreSQL Новое сообщение

Нумерация по второй таблице.

Alex_Wong
Дата: 14.11.2014 17:31:11
Добрый день.

Есть две таблицы ta и tb :

+
-- DROP TABLE ta;

CREATE TABLE ta
(
  idta serial NOT NULL,
  txt character varying(10),
  isno boolean,
  CONSTRAINT ta_pkey PRIMARY KEY (idta)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ta
  OWNER TO postgres;

-- DROP TABLE tb;

CREATE TABLE tb
(
  idtb serial NOT NULL,
  mynum smallint,
  txt character varying(10),
  CONSTRAINT tb_pkey PRIMARY KEY (idtb)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tb
  OWNER TO postgres;


INSERT INTO ta (txt, isno) VALUES ('b1', TRUE);
INSERT INTO ta (txt, isno) VALUES ('b2', TRUE);
INSERT INTO ta (txt, isno) VALUES ('a3', TRUE);
INSERT INTO ta (txt, isno) VALUES ('b2', TRUE);
INSERT INTO ta (txt, isno) VALUES ('c8', TRUE);
INSERT INTO ta (txt, isno) VALUES ('c9', FALSE);
INSERT INTO ta (txt, isno) VALUES ('b7', TRUE);
INSERT INTO ta (txt, isno) VALUES ('k9', TRUE);
INSERT INTO ta (txt, isno) VALUES ('e7', TRUE);
INSERT INTO ta (txt, isno) VALUES ('b7', FALSE);
INSERT INTO ta (txt, isno) VALUES ('a3', TRUE);
---
INSERT INTO tb (mynum, txt) VALUES (5, 'a3');
INSERT INTO tb (mynum, txt) VALUES (1, 'b1');
INSERT INTO tb (mynum, txt) VALUES (4, 'k9');
INSERT INTO tb (mynum, txt) VALUES (2, 'b2');
INSERT INTO tb (mynum, txt) VALUES (3, 'j4');
INSERT INTO tb (txt) VALUES ('c8');
INSERT INTO tb (mynum, txt) VALUES (6, 'm0');
INSERT INTO tb (txt) VALUES ('b7');
INSERT INTO tb (txt) VALUES ('e7');

Хочу получить записи из табл. ta с нумерацией num, коррелировано существующей нумерации mynum в табл. tb, а там, где
в tb.mynum нумерация отсутствует, чтобы пронумеровало в конце по нарастающей.
Т.е. получить вот так :
+

---------------------
num	mynum	txt
---------------------
1	1	 b1

2	2	 b2

3	4	 k9

4	5	 a3

5		 c8

6		 b7

7		 e7
---------------------



Мои попытки :

SELECT mynum, ta.txt FROM ta LEFT JOIN tb ON ta.txt = tb.txt

WHERE tb.txt IN (SELECT ta.txt FROM ta WHERE isno = TRUE GROUP BY ta.txt) ORDER BY mynum;

--
сгруппировать и вставить row_number() OVER() не получается.

Спасибо.
Alex_Wong
Дата: 14.11.2014 18:01:21
Alex_Wong,

похоже вот так :

SELECT row_number() OVER(ORDER BY mynum), mynum, ta.txt FROM ta LEFT JOIN tb ON ta.txt = tb.txt 
WHERE tb.txt IN (SELECT ta.txt FROM ta WHERE isno = TRUE GROUP BY ta.txt) GROUP BY tb.mynum, ta.txt;
Alex_Wong
Дата: 14.11.2014 18:10:58
Alex_Wong,

внутри группировка не нужна :

   SELECT row_number() OVER(ORDER BY mynum), mynum, ta.txt FROM ta LEFT JOIN tb ON ta.txt = tb.txt 
   WHERE tb.txt IN (SELECT ta.txt FROM ta WHERE isno = TRUE) GROUP BY tb.mynum, ta.txt;
Alex_Wong
Дата: 14.11.2014 18:38:42
Alex_Wong,

читал, что IN - затратная операция, но уйти от нее не получилось.
Добрый Э - Эх
Дата: 14.11.2014 19:44:23
Alex_Wong,

IN эквивалентно заменяем на EXISTS, а при небольшом шаманстве и на [lateral] JOIN
Alex_Wong
Дата: 14.11.2014 20:03:07
Добрый Э - Эх,

шаманство не получилось ...
Alex_Wong
Дата: 14.11.2014 20:20:43
Добрый Э - Эх,

точнее, не получилось ни с EXISTS, ни с JOIN
Alex_Wong
Дата: 14.11.2014 20:53:19
Добрый Э - Эх,

Вот при таком запросе :

SELECT row_number() OVER(ORDER BY mynum)as num, mynum, ta.txt FROM ta LEFT JOIN tb ON ta.txt = tb.txt 
WHERE EXISTS (SELECT ta.txt FROM ta WHERE isno = TRUE) GROUP BY tb.mynum, ta.txt;


получаю лишнюю строку -- не отсеялась c9 по FALSE

1   1   b1
2   2   b2
3   4   k9
4   5   a3
5       b7
6       c9  <--
7       c8
8       e7
Добрый Э - Эх
Дата: 14.11.2014 21:30:21
в exists-подзапросе как минимум не хватает условия связи между внешней и внутренней таблицами...
Alex_Wong
Добрый Э - Эх,

Вот при таком запросе :

SELECT row_number() OVER(ORDER BY mynum)as num, mynum, ta.txt FROM ta LEFT JOIN tb ON ta.txt = tb.txt 
WHERE EXISTS (SELECT null FROM ta WHERE isno = TRUE and ta.txt = tb.txt) GROUP BY tb.mynum, ta.txt;


получаю лишнюю строку -- не отсеялась c9 по FALSE
Alex_Wong
Дата: 14.11.2014 21:41:17
Добрый Э - Эх,

да, спасибо, так работает.