Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 запрос  [new]
domka
Guest
Всем привет. Вопрос в запросе. Суть следующая:
имеется
таблица location
ID
MASTER_ID -- ссылка на родителя (для региона - родитель страна, для города - регион)
NAME
LEVEL (1-Страна, 2-Регион, 3-Город)
Мне нужно посчитать количество регионов,городов в стране
т.е должна быть такая таблица

Страна |Кол.регионов|Кол городов|
--------------------------------------------------
Страна1         1               2 
Страна2         3               4    
и т.д

Заранее пасибо!
26 сен 07, 15:59    [4718896]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
_Alex_SMIRNOV_
Member

Откуда: Киев
Сообщений: 1519
select distinct NAME,
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=2) rg,
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=3) gor
from location a
26 сен 07, 16:18    [4719050]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
_Alex_SMIRNOV_
Member

Откуда: Киев
Сообщений: 1519
А можно еще и иерархическим запросом :)
26 сен 07, 16:21    [4719074]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
Дубовая голова
Member [заблокирован]

Откуда: с цепи сорвался
Сообщений: 1821
_Alex_SMIRNOV_

from location a


Ничего не забыл ?
26 сен 07, 16:25    [4719100]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
OraDen
Member

Откуда:
Сообщений: 828
_Alex_SMIRNOV_
select distinct NAME,
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=2) rg,
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=3) gor
from location a
вы уверены, что ваш вариант вернет то что требуется?
26 сен 07, 16:25    [4719106]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
_Alex_SMIRNOV_
Member

Откуда: Киев
Сообщений: 1519
OraDen
_Alex_SMIRNOV_
select distinct NAME,
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=2) rg,
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=3) gor
from location a
вы уверены, что ваш вариант вернет то что требуется?



нет :) со второй частью лажанулся... там где "gor"
26 сен 07, 16:27    [4719119]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
Дубовая голова
Member [заблокирован]

Откуда: с цепи сорвался
Сообщений: 1821
_Alex_SMIRNOV_

нет :) со второй частью лажанулся... там где "gor"


Вы уже трижды лажанулись, включая эту фразу. А если не торопиться и подумать ?
26 сен 07, 16:29    [4719133]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
_Alex_SMIRNOV_
Member

Откуда: Киев
Сообщений: 1519
Дубовая голова
_Alex_SMIRNOV_

from location a


Ничего не забыл ?

where LEVEL=1
26 сен 07, 16:31    [4719148]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
OraDen
Member

Откуда:
Сообщений: 828
_Alex_SMIRNOV_
нет :) со второй частью лажанулся... там где "gor"
На самом деле еще большой вопрос почему при такой четкой структуре с тремя уровнями не используется три таблицы вместо иерархии, боюсь что Level в табличке автора топика может отличаться от реального уровня в иерархии
26 сен 07, 16:32    [4719158]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
_Alex_SMIRNOV_
Member

Откуда: Киев
Сообщений: 1519
OraDen
_Alex_SMIRNOV_
нет :) со второй частью лажанулся... там где "gor"
На самом деле еще большой вопрос почему при такой четкой структуре с тремя уровнями не используется три таблицы вместо иерархии, боюсь что Level в табличке автора топика может отличаться от реального уровня в иерархии


Тогда это уже будет просто ошибка данных
26 сен 07, 16:36    [4719180]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
Дубовая голова
Member [заблокирован]

Откуда: с цепи сорвался
Сообщений: 1821
_Alex_SMIRNOV_
where LEVEL=1


Ну и есть подозрение, что DISTINCT также не требуется ;) (надеюсь, у автора нет
дублирования данных).
26 сен 07, 16:37    [4719197]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
_Alex_SMIRNOV_
Member

Откуда: Киев
Сообщений: 1519
select NAME, 
(select count(1) from location b where a.id = b.MASTER_ID and b.LEVEL=2) rg, 
(select count(1) from location b where b.MASTER_ID in (select c.ID from location c where a.id = c.MASTER_ID and b.LEVEL=2)  and b.LEVEL=3) gor
from location a
where a.level=1

26 сен 07, 16:39    [4719204]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
Дубовая голова
Member [заблокирован]

Откуда: с цепи сорвался
Сообщений: 1821
Учитывая "магию данных", возможно так:

SQL> select * from x order by 1;

        ID  MASTER_ID NAME              LVL
---------- ---------- ---------- ----------
         1            Country1            1
         2            Country2            1
         3          1 Reg11               2
         4          1 Reg12               2
         5          2 Reg21               2
         6          2 Reg22               2
         7          3 City1               3
         8          3 City2               3

8 rows selected.

SQL> SELECT a.NAME country, count(unique b.id) reg, count(c.id) city
  2  FROM x a, x b, x c
  3  WHERE a.lvl = 1 and a.id = b.master_id(+) and
  4  b.id = c.master_id(+)
  5  GROUP BY a.id, a.name
  6  /

COUNTRY           REG       CITY
---------- ---------- ----------
Country1            2          2
Country2            2          0
26 сен 07, 16:47    [4719285]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
OraDen
Member

Откуда:
Сообщений: 828
Дубовая голова
Учитывая "магию данных", возможно так:

SQL> select * from x order by 1;

        ID  MASTER_ID NAME              LVL
---------- ---------- ---------- ----------
         1            Country1            1
         2            Country2            1
         3          1 Reg11               2
         4          1 Reg12               2
         5          2 Reg21               2
         6          2 Reg22               2
         7          3 City1               3
         8          3 City2               3

8 rows selected.

SQL> SELECT a.NAME country, count(unique b.id) reg, count(c.id) city
  2  FROM x a, x b, x c
  3  WHERE a.lvl = 1 and a.id = b.master_id(+) and
  4  b.id = c.master_id(+)
  5  GROUP BY a.id, a.name
  6  /

COUNTRY           REG       CITY
---------- ---------- ----------
Country1            2          2
Country2            2          0
b.master_id(+) - разве будет работать
26 сен 07, 17:11    [4719570]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
Дубовая голова
Member [заблокирован]

Откуда: с цепи сорвался
Сообщений: 1821
OraDen
b.master_id(+) - разве будет работать


Я не понял вопроса ?
26 сен 07, 17:18    [4719632]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
OraDen
Member

Откуда:
Сообщений: 828
b.master_id(+) and
b.id =
Если не ошибаюсь в таком виде (+) что есть что нет, он работает только если прописан во всех условиях, т.е.
b.master_id(+) and
b.id(+) =
26 сен 07, 17:22    [4719661]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
Дубовая голова
Member [заблокирован]

Откуда: с цепи сорвался
Сообщений: 1821
OraDen
Если не ошибаюсь в таком виде (+) что есть что нет, он работает только если прописан во всех условиях, т.е.
b.master_id(+) and
b.id(+) =


Ошибаетесь. :)

В первом случае - a.id = b.master_id (+) таблица b - внутренняя во внешнем соединении с a,
во втором случае b.id = c.master_id(+) таблица b является внешней по отношению к таблице c
в соединении.

Иными словами это эквивалентно

a left join b on (a.id = b.master_id) left join c on (b.id = c.master_id)

Трюк с (+) имеет место быть, если бы я захотел написать:

a left join b on (a.id = b.master_id and b.lvl = 2) left join c on (b.id = c.master_id)

Тогда надо было бы

a.id = b.master_id (+) and b.lvl (+) = 2. (+) в данном случае означает, что b.lvl=2 является
частью условия соединения, а не ограничивающего выражения WHERE которое применяется к
результату соединения ;)

Матчасть
26 сен 07, 17:32    [4719749]     Ответить | Цитировать Сообщить модератору
 Re: запрос  [new]
OraDen
Member

Откуда:
Сообщений: 828
Дубовая голова
Ошибаетесь. :)
Действительно :(

Когда написано через left outer join все таки понятнее :)
with location as (
select 1  ID, null MASTER_ID, 'Страна1' NAME, 1 LLEVEL FROM DUAL union all
select 2  ID, 1    MASTER_ID, 'Регион1' NAME, 2 LLEVEL FROM DUAL union all
select 3  ID, 2    MASTER_ID, 'Город1'  NAME, 3 LLEVEL FROM DUAL union all
select 4  ID, 2    MASTER_ID, 'Город2'  NAME, 3 LLEVEL FROM DUAL union all
select 5  ID, null MASTER_ID, 'Страна2' NAME, 1 LLEVEL FROM DUAL union all
select 6  ID, 5    MASTER_ID, 'Регион2' NAME, 2 LLEVEL FROM DUAL union all
select 7  ID, 6    MASTER_ID, 'Город3'  NAME, 3 LLEVEL FROM DUAL union all
select 8  ID, 5    MASTER_ID, 'Регион3' NAME, 2 LLEVEL FROM DUAL union all
select 9  ID, 8    MASTER_ID, 'Город4'  NAME, 3 LLEVEL FROM DUAL union all
select 10 ID, 5    MASTER_ID, 'Регион4' NAME, 2 LLEVEL FROM DUAL union all
select 11 ID, 10   MASTER_ID, 'Город5'  NAME, 3 LLEVEL FROM DUAL union all
select 12 ID, 10   MASTER_ID, 'Город6'  NAME, 3 LLEVEL FROM DUAL union all
select 13 ID, 5    MASTER_ID, 'Регион5' NAME, 2 LLEVEL FROM DUAL union all
select 14 ID, 13   MASTER_ID, 'Город7'  NAME, 3 LLEVEL FROM DUAL union all
select 15 ID, null MASTER_ID, 'Страна3' NAME, 1 LLEVEL FROM DUAL
)
SELECT a.NAME COUNTRY, count(unique b.id) REG, count(c.id) city
FROM location a 
left outer join location b on a.id = b.MASTER_ID and b.llevel = 2
left outer join location c on b.id = c.MASTER_ID and c.llevel = 3
WHERE a.LLEVEL=1
GROUP BY a.id, a.name
26 сен 07, 17:57    [4719964]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить