Re: Помогите с запросом - Базы данных

БД Oracle

Есть таблица переходов

  S1 | S2  | QUANT
------------------
  A  |  B  |  50
  A  |  C  | 100
  B  |  C  |  40
  A  |  C  |  12

(из уза A перешло в узел B такое то количество)

нужен запрос показывающий какое всего кол-во вышло из A , B, C и какое вошло в них, т.е. итог чтоб бы примерно такой

S |Q_OUT| Q_IN
-----------------------
A | 162 |  0
B | 40  |  50
C |  0  | 152

(либо какое кол-во на текущий момент находится в узле)

Здравствуйте, nashav0, Вы писали:

N> БД Oracle
N>Есть таблица переходов
N>нужен запрос показывающий какое всего кол-во вышло из A , B, C и какое вошло в них, т.е. итог чтоб бы примерно такой

Так это ж классические бухгалтерские проводки — дебит, кредит, сумма.
Про Oracle не скажу, а на T-SQL типа так:

SELECT ISNULL(a.S1, b.S2) AS S, ISNULL(a.Q, 0) AS Q_OUT, ISNULL(b.Q, 0) AS Q_IN
FROM
 (SELECT S1, SUM(QUANT) AS Q FROM ... GROUP BY S1) a
FULL JOIN
 (SELECT S2, SUM(QUANT) AS Q FROM ... GROUP BY S2) b
 ON a.S1 = b.S2

Здравствуйте, algol, Вы писали:

A>Так это ж классические бухгалтерские проводки — дебит, кредит, сумма.
A>Про Oracle не скажу, а на T-SQL типа так:
A>

A>SELECT ISNULL(a.S1, b.S2) AS S, ISNULL(a.Q, 0) AS Q_OUT, ISNULL(b.Q, 0) AS Q_IN
A>FROM
A> (SELECT S1, SUM(QUANT) AS Q FROM ... GROUP BY S1) a
A>FULL JOIN
A> (SELECT S2, SUM(QUANT) AS Q FROM ... GROUP BY S2) b
A> ON a.S1 = b.S2
A>

В тот то и дело что в Oracle у меня такой запрос возвращает

S | Q_OU | Q_IN
--------------
B | 40   | 50
A | 162  |  0
C | 0    |  0
C | 0    |  0
C | 0    |  0

Здравствуйте, nashav0, Вы писали:

N> БД Oracle

N>Есть таблица переходов

N>

N>  S1 | S2  | QUANT
N>------------------
N>  A  |  B  |  50
N>  A  |  C  | 100
N>  B  |  C  |  40
N>  A  |  C  |  12

(из уза A перешло в узел B такое то количество)

N>нужен запрос показывающий какое всего кол-во вышло из A , B, C и какое вошло в них, т.е. итог чтоб бы примерно такой

N>

N>S |Q_OUT| Q_IN
N>-----------------------
N>A | 162 |  0
N>B | 40  |  50
N>C |  0  | 152

N>(либо какое кол-во на текущий момент находится в узле)

CREATE TABLE temp
(
s1 VARCHAR2(1), s2 VARCHAR2(1), quant NUMBER
);

INSERT INTO temp
   SELECT 'A', 'B', 50
     FROM DUAL
   UNION ALL
   SELECT 'A', 'C', 100
     FROM DUAL
   UNION ALL
   SELECT 'B', 'C', 40
     FROM DUAL
   UNION ALL
   SELECT 'A', 'C', 12
     FROM DUAL
   UNION ALL
   SELECT 'C', 'B', 52
     FROM DUAL;

COMMIT ;

SELECT *
  FROM temp;

SELECT   r.s, (SELECT SUM (DISTINCT t.quant)
                 FROM temp t
                WHERE t.s1 = r.s) AS q_out,
         (SELECT SUM (DISTINCT t.quant)
            FROM temp t
           WHERE t.s2 = r.s) AS q_in
    FROM (SELECT DISTINCT z.s1 AS s
                     FROM temp z
          UNION
          SELECT DISTINCT z.s2 AS s
                     FROM temp z) r
GROUP BY r.s

Здравствуйте, nashav0, Вы писали:

N>В тот то и дело что в Oracle у меня такой запрос возвращает
N>

N>S | Q_OU | Q_IN
N>--------------
N>B | 40   | 50
N>A | 162  |  0
N>C | 0    |  0
N>C | 0    |  0
N>C | 0    |  0
N>

Наверное у вас какой-то неправильный Oracle (и мед у него тоже неправильный). Как там могут повторяться C, если везде стоит GROUP BY?

Здравствуйте, algol, Вы писали:

A>Наверное у вас какой-то неправильный Oracle (и мед у него тоже неправильный). Как там могут повторяться C, если везде стоит GROUP BY?

Да наверно, запостила на сайте оракла как баг, просто проверяла на 10-ке все нормально

Здравствуйте, nashav0, Вы писали:

N>нужен запрос показывающий какое всего кол-во вышло из A , B, C и какое вошло в них, т.е. итог чтоб бы примерно такой

N>

N>S |Q_OUT| Q_IN
N>-----------------------
N>A | 162 |  0
N>B | 40  |  50
N>C |  0  | 152

SQL>create table test (s1 char(1), s2 char(1), quant integer);

Table created.

SQL>insert into test values ('A', 'B', 50);

1 row created.

SQL>insert into test values ('A', 'C', 100);

1 row created.

SQL>insert into test values ('B', 'C', 40);

1 row created.

SQL>insert into test values ('A', 'C', 12);

1 row created.

SQL>commit;

Commit complete.

SQL>
SQL>select case dir when 'IN' then S2 when 'OUT' then S1 end s,
  2         nvl(sum(case dir when 'OUT' then quant end), 0) q_out,
  3         nvl(sum(case dir when 'IN' then quant end), 0) q_in
  4    from test, (select 'IN' dir from dual union select 'OUT' from dual)
  5   group by case dir when 'IN' then S2 when 'OUT' then S1 end
  6  /

S     Q_OUT      Q_IN
- --------- ---------
A       162         0
B        40        50
C         0       152

SQL>

Здравствуйте, FunnyRabbit, Вы писали:

FR>SELECT   r.s, (SELECT SUM (DISTINCT t.quant)
FR>                 FROM temp t
FR>                WHERE t.s1 = r.s) AS q_out,
FR>         (SELECT SUM (DISTINCT t.quant)
FR>            FROM temp t
FR>           WHERE t.s2 = r.s) AS q_in
FR>    FROM (SELECT DISTINCT z.s1 AS s
FR>                     FROM temp z
FR>          UNION
FR>          SELECT DISTINCT z.s2 AS s
FR>                     FROM temp z) r
FR>GROUP BY r.s
FR>

А я от тебя такого не ожидал :-p

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, FunnyRabbit, Вы писали:

W>

FR>SELECT   r.s, (SELECT SUM (DISTINCT t.quant)
FR>>                 FROM temp t
FR>>                WHERE t.s1 = r.s) AS q_out,
FR>>         (SELECT SUM (DISTINCT t.quant)
FR>>            FROM temp t
FR>>           WHERE t.s2 = r.s) AS q_in
FR>>    FROM (SELECT DISTINCT z.s1 AS s
FR>>                     FROM temp z
FR>>          UNION
FR>>          SELECT DISTINCT z.s2 AS s
FR>>                     FROM temp z) r
FR>>GROUP BY r.s
FR>>

W>А я от тебя такого не ожидал :-p

Почему? Канет ведь?

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, FunnyRabbit, Вы писали:

W>

FR>SELECT   r.s, (SELECT SUM (DISTINCT t.quant)
FR>>                 FROM temp t
FR>>                WHERE t.s1 = r.s) AS q_out,
FR>>         (SELECT SUM (DISTINCT t.quant)
FR>>            FROM temp t
FR>>           WHERE t.s2 = r.s) AS q_in
FR>>    FROM (SELECT DISTINCT z.s1 AS s
FR>>                     FROM temp z
FR>>          UNION
FR>>          SELECT DISTINCT z.s2 AS s
FR>>                     FROM temp z) r
FR>>GROUP BY r.s
FR>>

W>А я от тебя такого не ожидал :-p

В моем варианте четко прослеживается логика.

Выбираем все возможные узлы.

FROM (SELECT DISTINCT z.s1 AS s
                     FROM temp z
          UNION
          SELECT DISTINCT z.s2 AS s
                     FROM temp z) r

Показываем каждый узел

SELECT r.s,

Для всех исходящих находим сумму в таблице

(SELECT SUM (DISTINCT t.quant)
                 FROM temp t
                WHERE t.s1 = r.s) AS q_out,

И сумму входящих

         (SELECT SUM (DISTINCT t.quant)
            FROM temp t
           WHERE t.s2 = r.s) AS q_in

Ну и в конце группируем

GROUP BY r.s

Здравствуйте, FunnyRabbit, Вы писали:

FR>В моем варианте четко прослеживается логика.

Логику с трудом, но проследил. Но зачем столько раз шерстить одну бедную табличку?

Да вообще не бери в голову. это просто подкол был.

Здравствуйте, FunnyRabbit, Вы писали:

FR>В моем варианте четко прослеживается логика.

GROUP BY в этой логике явно лишний.

Здравствуйте, wildwind, Вы писали:

А как лучше поступить, если количество полей и их данных зависит от переменной величины.
Т.е. пользователь ввел размерность М. Она хранится в таблице T1 (is number primary key, size number).
В таблице T2 храняться данные в формате T2 (id number primary key, field_1 varchar2(1000), field_2 varchar2(1000), t1_id number FOREIGN KEY)

Так вот вывести в первый столбец типа

SELECT DISTINCT field_1 FROM T2
UNION 
SELECT DISTINCT field_2 FROM T2

А все пересечения field_1 / field_2 c одним и тем же t1_id в другие столбцы.

Читал здесь, что на www.sql.ru есть вариант для MSSQL для подобного случая, но мне интересно на SQL с возможностью использования фичек Oracla, т.к. на PL/SQL я уже реализовал.

Писал в MSSQL, но думаю, что Oracle это съест

select s, sum(case a when -1 then q end) as s_in, sum(case a when 1 then q end) as s_out
from
(
select s1 s, q, -1 a from @t
union all
select s2, q, 1 from @t
) t
group by s

Здравствуйте, FunnyRabbit, Вы писали:

FR>А как лучше поступить, если количество полей и их данных зависит от переменной величины.
FR>Т.е. пользователь ввел размерность М. Она хранится в таблице T1 (is number primary key, size number).
FR>В таблице T2 храняться данные в формате T2 (id number primary key, field_1 varchar2(1000), field_2 varchar2(1000), t1_id number FOREIGN KEY)

FR>Так вот вывести в первый столбец типа

FR>А все пересечения field_1 / field_2 c одним и тем же t1_id в другие столбцы.

Что-то никак не въеду в постановку задачи... Дай пример что ли.

Здравствуйте, Хемуль, Вы писали:

Х>Писал в MSSQL, но думаю, что Oracle это съест

Съест конечно. Это лучше, чем здесь, но все же два просмотра таблицы, хотя одного достаточно.

Здравствуйте, algol, Вы писали:

A>Здравствуйте, nashav0, Вы писали:

N>> БД Oracle
N>>Есть таблица переходов
N>>нужен запрос показывающий какое всего кол-во вышло из A , B, C и какое вошло в них, т.е. итог чтоб бы примерно такой

A>Так это ж классические бухгалтерские проводки — дебит, кредит, сумма.
A>Про Oracle не скажу, а на T-SQL типа так:
A>

A>SELECT ISNULL(a.S1, b.S2) AS S, ISNULL(a.Q, 0) AS Q_OUT, ISNULL(b.Q, 0) AS Q_IN
A>FROM
A> (SELECT S1, SUM(QUANT) AS Q FROM ... GROUP BY S1) a
A>FULL JOIN
A> (SELECT S2, SUM(QUANT) AS Q FROM ... GROUP BY S2) b
A> ON a.S1 = b.S2
A>

Всем привет! Может уже поздно, но вот запрос на ORACLE:

select nvl(q1.s1, q2.s2) as S, nvl(q1.Q, 0) as Q_OUT, nvl(q2.Q, 0) as Q_IN
from
(select v1.s1, sum(v1.qnt) as Q
from vvu01 v1
group by v1.s1) q1
full join
(select v2.s2, sum(v2.qnt) as Q
from vvu01 v2
group by v2.s2) q2 on q1.s1=q2.s2
order by S

Лучше поздно, чем никогда, сказал мужик, ложась на рельсы и смотря вслед уходящему поезду

Здравствуйте, vvu, Вы писали:

vvu>Всем привет! Может уже поздно, но вот запрос на ORACLE:

vvu>select nvl(q1.s1, q2.s2) as S, nvl(q1.Q, 0) as Q_OUT, nvl(q2.Q, 0) as Q_IN
vvu>from
vvu> (select v1.s1, sum(v1.qnt) as Q
vvu> from vvu01 v1
vvu> group by v1.s1) q1
vvu> full join
vvu> (select v2.s2, sum(v2.qnt) as Q
vvu> from vvu01 v2
vvu> group by v2.s2) q2 on q1.s1=q2.s2
vvu>order by S

Я уже писала, что с full join у меня не работает, остановилась на варианте wildwind. Всем спасибо!

Здравствуйте, wildwind, Вы писали:

W>Что-то никак не въеду в постановку задачи... Дай пример что ли.

Задача такова, что есть спортивный турнир турнир, в котором участники команд разбиты на группы, групп может быть несколько и количество участников в зависимости от турнира различно в каждой группе. Я определил таблицу

CREATE TABLE CHAMPIONSHIPS
(
id NUMBER PRIMARY KEY,
text VARCHAR2, -- заголовок
group_count NUMBER,
teams_per_group NUMBER
)

.
Для списка участников и результатов игр я создаю таблицу GAMES

CREATE TABEL GAMES
(
id NUMBER PRIMARY KEY,
game_date DATE,
first_player NUMBER, 
second_player NUMBER,
fp_score NUMBER,
sp_score NUMBER
)

Так вот надо по GAMES создать турнирную таблицу. Т.е. таблицу вида (примерный вид)

Команда | 1 | 2 | 3 | Очки | Место |
-----------------------------------------
Спартак | X | 1:2 | 2:2 | 3 | 1 |
------------------------------------------
Динамо | 2:1 | X | 0:0 | 1 | 2 |
-----------------------------------------
Локомотив | 2:2 | 0:0 | X | 0 | 3 |

Здесь играли друг против друга три команды

Спартак — Динамо
Спартак — Локомотив
Локомотив — Динамо

Здравствуйте, FunnyRabbit, Вы писали:

FR>Так вот надо по GAMES создать турнирную таблицу. Т.е. таблицу вида (примерный вид)

FR>Команда | 1 | 2 | 3 | Очки | Место |
FR>-----------------------------------------
FR>Спартак | X | 1:2 | 2:2 | 3 | 1 |
FR>------------------------------------------
FR>Динамо | 2:1 | X | 0:0 | 1 | 2 |
FR>-----------------------------------------
FR>Локомотив | 2:2 | 0:0 | X | 0 | 3 |

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

	От:	nashav0
	Дата:	31.10.06 08:39
	Оценка:

От:	algol	about:blank
Дата:	31.10.06 09:12
Оценка:

	От:	nashav0
	Дата:	31.10.06 09:55
	Оценка:

	От:	FunnyRabbit
	Дата:	31.10.06 10:12
	Оценка:

От:	algol	about:blank
Дата:	31.10.06 11:18
Оценка:

	От:	wildwind
	Дата:	31.10.06 14:08
	Оценка:	2 (1) +1

	От:	vvu
	Дата:	01.11.06 08:51
	Оценка: