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

Форум программистов > Составление SQL-запросов > Подсчет записей в связанных таблицах БД Paradox.

Автор: tangar777 15.9.2010, 20:22

Всем привет. Столкнулся с проблемой, не могу разобраться.  Использую Delphi + база данных Paradox (dbExpress) Есть 2 таблицы.  Первая - Register ID_Man - int Amb_karta - int Last_Name - string First_Name - string Father_Name - string Born_dat - date Вторая - osmotr_1 ID_Man {Остальные поля} Таблицы в программе связаны связаны связью 1(Register) ко многим(osmotr_1) Задача - вывести таблицу с данными людей из таблицы Register, и добавить колонку с количеством соответствующих записей из таблицы osmotr_1. Пока реализовал 1 метод, как это сделать, но он мне кажется неудобным: Первый метод: Код SELECT   Register.Amb_karta ,Register.Last_Name ,Register.First_Name ,Register.Father_Name ,Register.Born_dat  ,COUNT(osmotr_1.Nom_osm) FROM "register.DB" Register  INNER JOIN "osmotr_1.DB" Osmotr_1 ON  (Register.ID_man = Osmotr_1.ID_man) WHERE (Number > 0)  GROUP BY Register.Amb_karta, Register.Born_dat,Register.Last_Name,Register.First_Name,Register.Father_Name ORDER BY Amb_karta Решил попробовать решить эту задачу через вложенные записи, написал такой код: Код SELECT  Register.Amb_karta  ,(SELECT COUNT(ID_man) FROM "osmotr_1.DB" Osmotr_1 WHERE osmotr_1.ID_man = Register.ID_man ) AS GP ,Register.Last_Name ,Register.First_Name ,Register.Father_Name ,Register.Born_dat  FROM "register.DB" Register  WHERE (Number > 0)  ORDER BY Amb_karta Но, он, к сожалению, нормально не работает - во вложенную запись, по-моему, не передается значение Register.ID_man. Если я его заменяю на цифру, то все работает прекрасно, а так - нет. Прылся в литературе, нашел аналогичный пример. В нем вроде как говорится что все должно работать. Код  SELECT    t.name,    (SELECT COUNT(*) FROM lsn_result r WHERE r.homekey = t.id OR r.awaykey = t.id) AS GP,    (COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)      + COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS W,    (COALESCE((SELECT SUM(IIF(r.homescore = r.awayscore, 1, 0)) FROM lsn_result r       WHERE r.homekey = t.id OR r.awaykey = t.id), 0)) AS D,    (COALESCE((SELECT SUM(IIF(r.homescore < r.awayscore, 1, 0)) FROM lsn_result r WHERE r.homekey = t.id), 0)      + COALESCE((SELECT SUM(IIF(r.homescore > r.awayscore, 1, 0)) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS L,    (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.homekey = t.id), 0)      + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.awaykey = t.id), 0)) AS GS,    (COALESCE((SELECT SUM(r.homescore) FROM lsn_result r WHERE r.awaykey = t.id), 0)      + COALESCE((SELECT SUM(r.awayscore) FROM lsn_result r WHERE r.homekey = t.id), 0)) AS GA  FROM    lsn_team t  ORDER BY    1 http://gsbelarus.com/gs/wiki/index.php/%D0%9A%D1%80%D0%B0%D1%82%D0%BA%D0%B8%D0%B9_%D0%BA%D1%83%D1%80%D1%81_SQL#.D0.9E.D0.B1.D1.8A.D0.B5.D0.B4.D0.B8.D0.BD.D0.B5.D0.BD.D0.B8.D0.B5_.D1.80.D0.B5.D0.B7.D1.83.D0.BB.D1.8C.D1.82.D0.B0.D1.82.D0.BE.D0.B2_.D0.BD.D0.B5.D1.81.D0.BA.D0.BE.D0.BB.D1.8C.D0.BA.D0.B8.D1.85_.D0.B7.D0.B0.D0.BF.D1.80.D0.BE.D1.81.D0.BE.D0.B2 Вообщем, убил пол дня на расхлебывание этой проблемы, и не знаю что делать. Хочется все-таки сделать запрос используя подзапросы, т.к. это будет значительно удобнее в дальнейшем совершенствовании данного запроса - будут добаваляться таблицы, аналогичные osmotr_1 с аналогичными целями. Сейчас склоняюсь к версии, что это ограничение используемого мною инструментария (Delphi+Paradox+dbExpress). Или же это банально кривизна рук и перегретые мозги?

Автор: Akina 15.9.2010, 21:15

А чем плох Код SELECT   Register.Amb_karta ,Register.Last_Name ,Register.First_Name ,Register.Father_Name ,Register.Born_dat  ,COUNT(Osmotr_1.ID_man) as cnt FROM "register.DB" Register  LEFT JOIN "osmotr_1.DB" Osmotr_1  ON  Register.ID_man = Osmotr_1.ID_man WHERE Number > 0  GROUP BY  Register.Amb_karta ,Register.Last_Name ,Register.First_Name ,Register.Father_Name ,Register.Born_dat  ORDER BY Amb_karta ? Добавлено через 2 минуты и 11 секунд Вообще-то обычно не следует использовать подзапросы, когда этого можно избежать.

Автор: Zloxa 15.9.2010, 21:20

local sql, на сколько я помню - вполне себе ограниченный движок. Удивлен что он способен выполнить скалярный подзапрос. Не удивлен, что он не смог его кореллировать. В третий запросе собсна не стал вкуриваться. А чем, позвольте поинтересоваться, вас не устраивает первый вариант? Единственно - оставить группировку по ID, а к остальным полям дать аггрегаты min либо max. Врядли второй вариант будет весомо отличаться по производительности от первого. Добавлено @ 21:21 Akina, теперь ты первый

Автор: tangar777 15.9.2010, 21:25

Наличием дублирующихся названий полей в SELECT и ORDER BY ... Проблема в том, что мне аналогичные сиквеловские запросы, только с значительно большим количеством полей надо составлять в процессе работы программы, грубо говоря выбирая галочками список нужных полей и нужных таблиц. И меня не покидает ощущение, что если я пойду таким путем, то в итоге буду получать "динозаврика" весьма немалых размеров, который с трудом будет поддаваться модификации ручками и осознаванию "что это такое и как оно работает". Возможно, я ввиду малой работы с сиквелом пытаюсь использовать тут другие подходы. Кстати, обнаружил в дельфятине забавную штуку - SQL Builder. В итоге, используя ее простенький интерфейс сумел не заморачиваясь с написанием кодов создать текст нужного мне запроса: Код SELECT Register.ID_man, Register.Amb_karta, Register.First_Name, Register.Last_Name, Register.Father_Name, COUNT( Osmotr_1.ID_man ) Osmotr_1."Количество осмотров", Register.Born_dat FROM "register.DB" Register    INNER JOIN "osmotr_1.DB" Osmotr_1    ON  (Register.ID_man = Osmotr_1.ID_man)   GROUP BY Register.ID_man, Register.Amb_karta, Register.First_Name, Register.Last_Name, Register.Father_Name, Register.Born_dat Все-таки интересно узнать - почему у меня не так как мне хочется работает код из первого примера. Работать-то работает, сообщение об ошибке не выводит... Может кто-то прояснит? Добавлено через 1 минуту и 20 секунд Цитата(Zloxa @  15.9.2010,  21:20 ) local sql, на сколько я помню - вполне себе ограниченный движок. Удивлен что он способен выполнить скалярный подзапрос. Не удивлен, что он не смог его кореллировать. В третий запросе собсна не стал вкуриваться. т.е. это проблема движка, я правильно понял?

Автор: Akina 15.9.2010, 23:07

Цитата(tangar777 @  15.9.2010,  21:22 ) во вложенную запись, по-моему, не передается значение Register.ID_man. А с какого перепугу, позвольте спросить, он ДОЛЖЕН туда передаваться?

Автор: tangar777 16.9.2010, 08:59

Цитата(Akina @  15.9.2010,  23:07 ) А с какого перепугу, позвольте спросить, он ДОЛЖЕН туда передаваться?   В примере, который я нашел в справочнике по SQL (3й пример в 1м посту), подобная конструкция используется. Только если будете вникать в пример, смотрите его лучше не в этой ветке, а в первоисточнике - там есть внятные комментарии о том, что написано. В итоге, решение я реализовал без использования подзапросов, используя дельфовую программку SQL Builder, с последующей незначительной доработкой кода вручную.

Автор: Zloxa 16.9.2010, 09:28

Цитата(tangar777 @  15.9.2010,  21:25 ) т.е. это проблема движка, я правильно понял?   По этому вопросу лучше бы свериться с документацией, но полагаю что - да. Кстати, я не сразу обратил внимание, Akina, Вам предложил использовать внешнее соединение. Я нахожу это разумным. Это позволит не отсекать те случаи, когда отсутствуют записи в связанной таблице и такой вариант более близок ко второму прииведенному Вами варианту запроса. Цитата(tangar777 @  15.9.2010,  21:25 ) в итоге буду получать "динозаврика" Вам не обязательно перечислять в group by все поля, которые вы хотите вывести в select-list, если хотябы одно из них является ключем. Вы можете в group by упомянуть только ключевое поле, а к остальным в селект-листе применить аггрегат min или max. При этом вы избежите громозкой сортировки при группировке но не потеряете в функционале. /*ой, кажись я об этом уже упоминал*/ Единственно, я не вполне уверен сможете ли вы потом отсортироваться по нужным полям. Local sql, на сколько я помню позволяет сортироваться по алиасу в select-листе, но я с этим движком знакомился очень давно и мог чтото запамятовать. Цитата(Akina @  15.9.2010,  23:07 ) А с какого перепугу, позвольте спросить, он ДОЛЖЕН туда передаваться?   Хм... Я эту фразу ТС воспринял как "подзапрос не удалось корелировать" и, в этом контексте, я не понимаю твоего недоумения. Я чтото упустил?

Автор: Akina 16.9.2010, 09:40

Цитата(Zloxa @  16.9.2010,  10:28 ) Я чтото упустил?  Ну... если подойти формально, то для подзапроса использование поля внешнего запроса эквивалентно использованию параметра, верно?не знаю, понимает ли собсно движок параметра, да и во что превратится план исполнения...

Автор: Zloxa 16.9.2010, 10:20

Цитата(Akina @  16.9.2010,  09:40 ) верно? Ну, если я тебя правильно понял, то нет. В случае использования параметра, подзапрос необходимо выполнить лишь единожды и его результат вывести в каждой строке результирующей выборки. В случае использования корелированного подзапроса, подзапрос необходимо выполнить для каждой строки, либо же выполнить весьма нетривиальное преобразование его в джойн. Но вообще использование корелированных подзапросов - общепринятая практика и поддерживается ныне большинством движков. Код SQL> with some_table as (select 1 id from dual union all select 2 from dual union all select 3 from dual)   2  select id   3         ,(select count(*) from some_table t2 where t1.id <= t2.id) cnt   4  from some_table t1   5  ;           ID        CNT ---------- ----------          1          3          2          2          3          1

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)