Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Помогите составить SQL запрос

Автор: PRIZRAK001 24.2.2012, 21:33

Здравствуйте.

Есть таблица  t  с переменным числом строк

|  v1 |  v2 |
-------------
|  A1 |  B1 |
|  A2 |  B1 |
|  A3 |  B1 |
...
|  An |  B1 |
|  A1 |  B2 |
|  A2 |  B2 |
|  A3 |  B2 |
...
|  An |  B2 |
|  A1 |  B3 |
|  A2 |  B3 |
|  A3 |  B3 |
...
|  An |  B3 |
...
...
|  A1 |  Bn |
|  A2 |  Bn |
|  A3 |  Bn |
...
|  An |  Bn |

С помощью ... я обозначил пропущенные строки.

Здесь v1, v2 - название столбцов таблицы.

A1, A2, A3, ..., An, B1, B2, B3, ..., Bn - произвольные значения. n - натуральное число больше нуля.

Число строк в таблице n*n

Необходимо сделать выборку, чтобы получился результат

|  v1 |  v2 |
-------------
|  A1 |  B1 |
|  A2 |  B2 |
|  A3 |  B3 |
...
|  An |  Bn |

Помогите, пожалуйста, составить SQL запрос.

Автор: Akina 24.2.2012, 23:45

Цитата(PRIZRAK001 @ 24.2.2012, 22:33

)

Необходимо сделать выборку, чтобы получился результат

Дайте чёткое объяснение, по какой причине из группы записей с v1=А1 выбрана именно запись с v2=B1 и отброшены все остальные. И то же по остальным записям результирующего набора...

Автор: PRIZRAK001 26.2.2012, 21:05

Попробую объяснить словами. Выборка происходит следующим образом.

Перемещаемся по таблице от первой до последней записи.
Мы выбираем запись A1,B1 , так как она идёт первой. Далее по ходу, отбрасываем все записи, у которых v1=A1 или v2=B1. Таким образом, следующей выбранной записью становится A2,B2. Продолжаем дальше просматривать таблицу, и отбрасываем записи у которых v1=A1 или v1=A2 или v2=B1 или v2=B2 . Таким образом находим A3,B3 . И так далее.

Получается, что очередная выбранная запись меняет условие отбора.

У меня PostgreSQL 8.2.

В данный момент я рассматриваю вариант с несколькими запросами и временной таблицей.

Автор: skyboy 27.2.2012, 02:01

"первой" при сортировке по какому полю? по дате добавления, по автоинкрементному полю, еще по чем-то?
давай думать в том ключе, что без явно указанной сортировки у нас порядок может быть произвольный.

Автор: Zloxa 27.2.2012, 09:59

PRIZRAK001, помимо уже указанной skyboy неясности с критерием сортировки, по которому запись v1=A1 или v2=B1 оказывается первой, есть еще одна неясность.

Цитата(PRIZRAK001 @ 26.2.2012, 21:05

)

Далее по ходу, отбрасываем все записи, у которых v1=A1 или v2=B1.

Если применить этот критерий, становится не ясным, как в результате оказалась отобрана третья запись, ведь во второй была отобрана v1=A2, а значит третья не должна попасть в выборку. То же и с четвертой записью.У нас отобрана v2=B3, потому четвертая запись долна быть "отброшена"

Цитата(PRIZRAK001 @ 24.2.2012, 21:33

)

|  v1 |  v2 |
-------------
|  A1 |  B1 |
|  A2 |  B2 |
|  A2 |  B3 |
|  A3 |  B3 |

Автор: Zloxa 27.2.2012, 10:17

Цитата(PRIZRAK001 @ 24.2.2012, 21:33

)

Число строк в таблице n*n

Попытаюсь ткнуть пальцем в небо.

Код


SQL> with t as (
  2    select 'A1' v1,'B1' v2 from dual
  3    union all select 'A2' v1,'B1' v2 from dual
  4    union all select 'A3' v1,'B1' v2 from dual
  5    union all select 'A1' v1,'B2' v2 from dual
  6    union all select 'A2' v1,'B2' v2 from dual
  7    union all select 'A3' v1,'B2' v2 from dual
  8    union all select 'A1' v1,'B3' v2 from dual
  9    union all select 'A2' v1,'B3' v2 from dual
 10    union all select 'A3' v1,'B3' v2 from dual
 11  )
 12  select v1,v2
 13  from (
 14    select t.*
 15           ,dense_rank() over (partition by v2 order by v1) v1_rank
 16           ,dense_rank() over (partition by v1 order by v2) v2_rank
 17    from t
 18  ) where v1_rank = v2_rank
 19  ;
 
V1 V2
-- --
A1 B1
A2 B2
A3 B3

Оно?

Автор: PRIZRAK001 27.2.2012, 12:07

Цитата(Zloxa @ 27.2.2012, 09:59

)

Сорри. Я ошибся. Должно быть

|  v1 |  v2 |
-------------
|  A1 |  B1 |
|  A2 |  B2 |
|  A3 |  B3 |
...
|  An |  Bn |

Добавлено @ 12:14

Цитата(skyboy @ 27.2.2012, 02:01

)

Не имеет значения, какая запись выбрана первой. Вот например, такой результат тоже устраивает

|  v1 |  v2 |
-------------
|  A1 |  B2 |
|  A2 |  B1 |
|  A3 |  B3 |
...
|  An |  Bn |

Добавлено @ 12:17

Цитата(Zloxa @ 27.2.2012, 10:17

)

Попытаюсь ткнуть пальцем в небо.
Выделить всёкод SQL
1:
25:

SQL> with t as (
  2    select 'A1' v1,'B1' v2 from dual
  3    union all select 'A2' v1,'B1' v2 from dual
  4    union all select 'A3' v1,'B1' v2 from dual
  5    union all select 'A1' v1,'B2' v2 from dual
  6    union all select 'A2' v1,'B2' v2 from dual
  7    union all select 'A3' v1,'B2' v2 from dual
  8    union all select 'A1' v1,'B3' v2 from dual
  9    union all select 'A2' v1,'B3' v2 from dual
10    union all select 'A3' v1,'B3' v2 from dual
11  )
12  select v1,v2
13  from (
14    select t.*
15           ,dense_rank() over (partition by v2 order by v1) v1_rank
16           ,dense_rank() over (partition by v1 order by v2) v2_rank
17    from t
18  ) where v1_rank = v2_rank
19  ;

V1 V2
-- --
A1 B1
A2 B2
A3 B3

Оно?

Не уверен, что сработает, так как у меня PostgreSQL 8.2.

with появился в 8.4.

Автор: Zloxa 27.2.2012, 12:59

Цитата(PRIZRAK001 @ 27.2.2012, 12:07

)

with появился в 8.4.

with тут используется лишь для иммитации исходного набора данных.
А вот оконных функций, которые используются в самом запросе, на сколько я могу судить, в 8,2 тоже не было. smile

тогда по старинке

Код


 12  select t.*
 13   from t
 14   where (select count(*) from t t1 where t.v1=t1.v1 and t.v2>t1.v2)
 15         =(select count(*) from t t1 where t.v2=t1.v2 and t.v1>t1.v1)
 16  ;
 
V1 V2
-- --
A1 B1
A2 B2
A3 B3

Но вы так и не смогли внятно объяснить что именно вам надо, потому не ясно - то ли это. smile

Автор: Zloxa 27.2.2012, 13:14

Цитата(Zloxa @ 27.2.2012, 12:59

)

то ли это

Оба моих решения строятся на предположении что мы имеем n уникальных значений v1 и n уникальных значений v2 и исходный набор содержит все уникальные комбинации сочетаний этих значений. Если это не так, то предложенное мной решение будет выдавать чушь.

Автор: Akina 27.2.2012, 13:25

Zloxa, я знаю, ты любишь идиотские решения...

Код


SELECT q1.v1, q2.v2
FROM
(
  SELECT @i := @i + 1 AS n, t1.v1
  FROM 
  (
    SELECT DISTINCT t01.v1 
    FROM t AS t01
  ) AS t1
  , 
  (
    SELECT @i:=0
  ) AS dummy1
) AS q1 
,
(
  SELECT @j := @j + 1 AS n, t2.v2
  FROM 
  (
    SELECT DISTINCT t02.v2 
    FROM t AS t02
  ) AS t2
  , 
  (
    SELECT @j:=0
  ) AS dummy2
) AS q2
WHERE q1.n=q2.n;

Тестировано в MySQL - работает. И ему пофиг уникальность сочетаний. Более того, можно играть сортировками в подзапросах t1 и t2, получая разные, но подходящие под описание, результаты... а заменой декарта на left/right join, даже обойти ограничение, что количества уникальных в полях не равны (правда, надо точно знать, где больше а full join в MySQL нету).

Автор: Zloxa 27.2.2012, 13:37

Цитата(Akina @ 27.2.2012, 13:25

)

И ему пофиг уникальность сочетаний.

Если я правильно понял смысл, при условии, когда исходный набор не содержит всех сочетаний, в результате есть шанс получить сочетание, отсутствующее в исходном наборе. Эко я выразился smile

Цитата(Akina @ 27.2.2012, 13:25

)

я знаю, ты любишь идиотские решения...

Спасибо, потешился smile

Автор: Zloxa 27.2.2012, 14:52

Коль уж дошло до извращений, то решение от оракла.
С буквальной реализацией авторского алгоритма. Т.е. берем первую попавшуюся, последующие значения попадают в выборку только только в том случае, если они не отбирались ранее. Для пущей недетерменированности сортировка по рандому.

Код


 12  select v1,v2
 13  from (
 14    select * from t
 15    model
 16      dimension by (row_number() over (order by dbms_random.value) rn
 17                    ,v1
 18                    ,v2)
 19      measures (0 sf)
 20      rules (
 21       sf[rn>1,any,any] order by rn = count(nullif(sign(sf),1))[rn<cv(rn),cv(v1),any]
 22                                     +count(nullif(sign(sf),1))[rn<cv(rn),any,cv(v2)]
 23      )
 24  )
 25  where sf=0
 26  order by rn
 27  /
 
V1 V2
-- --
A1 B1
A3 B2
A2 B3
 
SQL> /
 
V1 V2
-- --
A3 B1
A1 B3
A2 B2
 
SQL> /
 
V1 V2
-- --
A2 B2
A3 B3
A1 B1

Таки оракл знайт толк в извращениях smile

Автор: PRIZRAK001 28.2.2012, 21:41

Вот такое решение наваял для PostgreSQL 8.2

Код

DROP SEQUENCE IF EXISTS t_uniq_id;
CREATE TEMPORARY SEQUENCE t_uniq_id;

CREATE TEMPORARY TABLE t_uniq
(
    id integer PRIMARY KEY DEFAULT nextval('t_uniq_id'::regclass),
    v1 character varying NOT NULL UNIQUE,
    v2 character varying NOT NULL UNIQUE
)
ON COMMIT DROP;

CREATE OR REPLACE FUNCTION insertIntoTUniq(character varying, character varying)
RETURNS void AS
$BODY$
DECLARE
BEGIN
    INSERT INTO t_uniq (v1, v2) VALUES ($1, $2);
EXCEPTION
    WHEN unique_violation THEN -- ignore error
END;
$BODY$
LANGUAGE 'plpgsql';

SELECT insertIntoTUniq(v1, v2) FROM t;

DROP FUNCTION insertIntoTUniq(character varying, character varying);

SELECT v1, v2 FROM t_uniq;