Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Вывод количества дочерних элементов в таблице (AL-дерево) / 10 сообщений из 10, страница 1 из 1
19.08.2013, 04:23:18
    #38370239
ruser
ruser
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
Добрый день!
Имеется таблица домов (houses) в сети провайдера, каждый дом может подключаться от другого дома (idhousefrom):
ididhousefrom1021314250637487
Необходимо найти количество подключенных домов к каждому дому (3 уровня). Для решения задачи составил запрос:
Код: sql
1.
2.
3.
4.
5.
SELECT h1.id, count(h1.id) AS cnt FROM houses h1
LEFT JOIN houses h2 ON h2.idhousefrom = h1.id
LEFT JOIN houses h3 ON h3.idhousefrom = h2.id
WHERE h1.idhousefrom = 0
GROUP BY h1.id


Однако считает он неверно. Где подвох, понять не могу.
Прошу помощи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 05:02:23
    #38370244
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
ruser,

>> Где подвох, понять не могу.

Подвох в том, что вы не выдали
полученый результат и не указали
чем он вас не устраивает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 05:36:49
    #38370246
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
 
sqlfiddle.com/#!2/e8f9c/11
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
create table pr(
  id int auto_increment primary key,
  pid int references pr(id));

insert pr(id,pid)values(
1,null),(
2,1),(
3,1),(
4,2),(
5,null),(
6,3),(
7,4),(
8,7);

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select id, count(*)qty
from(
  select p1.id, p2.id i2, null i3
  from pr p1
  join pr p2 on p2.pid=p1.id
    union all
  select p1.id i1, p2.id i2, p3.id i3
  from pr p1
  join pr p2 on p2.pid=p1.id
  join pr p3 on p3.pid=p2.id
  )u
group by id
;

ID QTY1 42 23 14 27 1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 06:21:45
    #38370249
ruser
ruser
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
Cygapb-007, спасибо, работает.

Однако, я не совсем понимаю, почему count(h1.id) и count(h2.id) неверно считают (для четырех уровней).
count(h3.id),count(h4.id) - верно:
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT h1.id,count(h1.id),count(h2.id),count(h3.id),count(h4.id)  FROM houses h1
  left JOIN houses h2 ON h2.idhousefrom = h1.id 
  left JOIN houses h3 ON h3.idhousefrom = h2.id
  left JOIN houses h4 ON h4.idhousefrom = h3.id
  left JOIN houses h5 ON h5.idhousefrom = h4.id
where h1.idhousefrom=0
GROUP BY h1.id;


вывод из реальной базы:
h1.idcount(h1.id)count(h2.id)count(h3.id)count(h4.id)171 10 10 6 0
Здесь показан этот дом со всеми своими зависимостями:
 

Видно, что count(h3.id) посчитан верно (6 зависимых домов на третьем уровне). Второй уровень неверен (во всех строках вывода он равен count(h1.id).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 06:31:19
    #38370250
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
sqlfiddle.com/#!2/e8f9c/14 :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
-- select id, count(*)qty from(
select p1.id, p2.id i2, null i3, null i4
from pr p1
join pr p2 on p2.pid=p1.id 
union all
select p1.id, p2.id i2, p3.id i3, null i4
from pr p1
join pr p2 on p2.pid=p1.id
join pr p3 on p3.pid=p2.id
union all
select p1.id i1, p2.id i2, p3.id i3, p4.id p4
from pr p1
join pr p2 on p2.pid=p1.id
join pr p3 on p3.pid=p2.id
join pr p4 on p4.pid=p3.id
-- )u group by id
;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 06:33:18
    #38370251
ruser
ruser
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
Cygapb-007, да, спасибо. Просто я хочу понять, что не так с моим запросом, где косяк? Или не стоит вообще его использовать и остановиться на вашем?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 06:41:18
    #38370252
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
ruserCygapb-007, да, спасибо. Просто я хочу понять, что не так с моим запросом, где косяк? Или не стоит вообще его использовать и остановиться на вашем?Запрос строит всю цепочку сразу, докуда дотянется. При этом будет трудно посчитать число разных элементов, например, втрого уровня при длине цепочки больше двух.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 06:45:11
    #38370254
ruser
ruser
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
Cygapb-007, ок, спасибо за разъяснение.
Тема закрыта.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 07:04:24
    #38370255
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
ruserCygapb-007, ок, спасибо за разъяснение.
Тема закрыта.Хммм... Не совсем... :-)
sqlfiddle.com/#!2/d9e0b/2 :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select p1.id, 
  count(distinct p2.id)+
  count(distinct p3.id)+
  count(distinct p4.id)qty
from pr p1
left join pr p2 on p2.pid=p1.id
left join pr p3 on p3.pid=p2.id
left join pr p4 on p4.pid=p3.id
group by p1.id

дает тот же результат...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.08.2013, 07:38:59
    #38370263
ruser
ruser
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывод количества дочерних элементов в таблице (AL-дерево)
Cygapb-007, крутота) благодарю. Про distinct как-то не подумал
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Вывод количества дочерних элементов в таблице (AL-дерево) / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 441ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1836224]:
 0ms
get settings:
 10ms
get forum list:
 19ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 67ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 58ms
get tp. blocked users:
 2ms
others: 262ms
total:  441ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]