[mysql] Задачка

SCIF32

Есть таблица
create table tmp (id int, idref int, param int);
нужно для каждого элемента таблицы найти количество ссылающихся на него через idref (дочерних) элементов, а также максимальное значение param для таких элементов.
Интуитивно хочется написать запрос:
select E.id, sum(1max(C.param) from tmp E, tmp C where E.id=C.idref group by E.id;
Но проблема в том, что элементы для которых нет дочерних просто не будут включены в результаты такого запроса.
Можно ли написать запрос для mysql, выполняющий поставленную задачу?

kruzer25

select `tmp_outer`.`id`,`res`.`count` from `tmp` as `tmp_outer`select count(*) as `count` from `tmp` as `tmp_inner` where `tmp_outer`.`id`=`tmp_inner`.`idref`) as `res` group by `tmp_outer`.`id`
подойтёт?

SCIF32

select `tmp_outer`.`id`,`res`.`count` from `tmp` as `tmp_outer`select count(*) as `count` from `tmp` as `tmp_inner` where `tmp_outer`.`id`=`tmp_inner`.`idref`) as `res` group by `tmp_outer`.`id`

ERROR 1054 (42S22): Unknown column 'tmp_outer.id' in 'where clause'

ruler

`да` `блин, как` `пенартура можно` `читать-то?`

6yrop

outer join

SCIF32

А как бороться с sum(1 который считает количество строк, а не вхождений, и в результате выдает 1 даже для элементов не имеющих дочерних?(пока вариант есть, но извратный)

zya369

select E.id, sum(1max(C.param) from tmp E, tmp C where E.id=C.idref group by E.id

тоже можно проще сделать так (или я не прав?):
select refid, count(* max(param) from tmp group by parentid
а потом можно сделать left join TMP и этого селекта
а можно сделать
select E.id, count(*max(C.param) from tmp E left join tmp C on E.id=C.idref group by E.id
и дальше уже при получении смотреть - если max(C.param) - null, то count(*) надо считать нулем
а еще можно к count прибавить и вычесть max(C.param) - если он нулл, то резултат будет нулл, а если нет, то результат не изменится

(это при условии что запрос не соптимизируют) - но это уже извращение

а вообще, я в мускуле не силен, так что если там есть функция, которая при входном параметре == нулл нает нулл, а иначе 0 то запрос будет такой:
select E.id, count(*) + FUNC(max(C.parammax(C.param) from tmp E left join tmp C on E.id=C.idref group by E.id
PS а еще можно поизвращаться с union'ами и ecxept'ами

Geddi-S

Если union работает, то можно сделать так:



select E.id as id, sum(1) as summ, max(C.param) as maxx from tmp E, tmp C where E.id=C.idref group by E.id

union

select id as id, 0 as summ, 0 as maxx

from tmp D

where (select count(*) from tmp A, tmp B where A.id=B.idref and A.id = D.id) = 0

Не проверял, мб сглючил

6yrop

select id as id, 0 as summ, 0 as maxx
from tmp D
where (select count(*) from tmp A, tmp B where A.id=B.idref and A.id = D.id) = 0

проще написать через NOT EXISTS

Geddi-S

Не знал про такое

SCIF32

Всем спасибо,
сначала сделал примерно так, как сказал ,
на счет not exist - это то что надо.

zya369

тогда уж лучше через NOT IN - там вложенный запрос не будет зависеть от внешней таблицы:
select E.id as id, sum(1) as summ, max(C.param) as maxx from tmp E, tmp C where E.id=C.idref group by E.id
union
select id as id, 0 as summ, 0 as maxx
from tmp D where D.id not in (select [distinct] refid from tmp)

6yrop

там вложенный запрос не будет зависеть от внешней таблицы:

это не аргумент, который стоит принимать во внимание. Надо сравнивать планы запросов. У меня была ситуация, когда эквивалентный запрос с in-ам на MySQL отрабатывал в разы медленнее, об это даже в документации было написано.

Оставить комментарий