Logo Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
Архив форумов ЦИТФорума
Море(!) вопросов - Море(!) ответов
 
 FAQFAQ   ПоискПоиск   ПользователиПользователи   ГруппыГруппы   РегистрацияРегистрация 
 ПрофильПрофиль   Войти и проверить личные сообщенияВойти и проверить личные сообщения   ВходВход 
Как правильно задавать вопросы

как правильно использовать JOIN в данной ситуации

 
Перейти:  
Этот форум закрыт, вы не можете писать новые сообщения и редактировать старые.   Эта тема закрыта, вы не можете писать ответы и редактировать сообщения.    Список форумов Архив форумов ЦИТФорума -> Базы данных
Предыдущая тема :: Следующая тема  
Автор Сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Пн Июл 24 2006 20:08    Заголовок сообщения: как правильно использовать JOIN в данной ситуации Ответить с цитатой

СУБД MySQL 5.0
Допустим есть три таблицы
mens - список людей
charge - должны заплатить за услуги
payment - оплачивали за услуги
Код:
-----------------------
         MENS
-----------------------
score_num | men_name
-----------------------
1         | Иванов А.А.
2         | Петров И.В.
----------------------------------------------
              CHRAGE
----------------------------------------------
id | score_num | tr_month | tr_year | tr_value
----------------------------------------------
234    1            1       2006       20.0
235    1            2       2006       20.0
236    1            3       2006       18.0
237    1            4       2006       21.0

tr_month, tr_year - за какой месяц какого года
                    ему это начислили за услугу
tr_value - сколько ему начислили
-------------------------------------
             PAYMENT
-------------------------------------
id | score_num | tr_date  | tr_value
-------------------------------------
234    1        14.01.2006     20.0
234    1        17.02.2006     22.0
234    1        20.04.2006     23.0


я хочу получить данные для абанента
за весь 2006 год
charge.tr_year = 2006
Year(payment.tr_date) = 2006
примерно в таком виде

Код:
score_num | men_name  | charge | payment
------------------------------------------
 1         Иванов А.А.    20.0 
 1         Иванов А.А.    20.0
 1         Иванов А.А.    18.0
 1         Иванов А.А.    21.0
 1         Иванов А.А.             20.0
 1         Иванов А.А.             22.0
 1         Иванов А.А.             23.0

пробовал для начала соеденить просто две таблицы
charge и payment
Код:
SELECT
A.score_num AS S1, A.tr_value AS charge,
B.score_num AS S2, B.tr_value AS payment
FROM
 charge AS A RIGHT OUTER JOIN payment  AS B
ON  A.tr_value > 0 AND B.tr_value > 0

выдаёт примерно такое
Код:
1     20     1     20
1     20     1     20
1     18     1     20
1     21     1     20
1     20     1     22
1     20     1     22
1     18     1     22
1     21     1     22
1     20     1     23
1     20     1     23
1     18     1     23
1     21     1     23

я уже не знаю как сделать по нормальному
ранее это реализовал пользовательскими функциями, по сути это подзапросы по каждому абаненту,
но в дальнейшем мне надо группировать и получать суммы, а когда
абанентов 2000 тыс, то время выполнения запроса стремится к 5-10 минутам, и это на пне 2.8, а у заказчика селерон 800-й Sad
Чую, что всё можно сделать правильным использованием JOIN и определёнными условиями наложенными на него, и возможно UNION
но как не могу понять.
Кто-нибудь укажет путь к истине?
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Lamers



Зарегистрирован: 29.06.2006
Сообщения: 16

СообщениеДобавлено: Вт Июл 25 2006 14:35    Заголовок сообщения: Ответить с цитатой

Код:

Select m.score_num, m.men_name, c.tr.value charge, "" payment
from mens m, charge с
where
c.tr_year = 2006

union all
Select m.score_num, m.men_name, "",p.tr.value payment
from mens m, payment p
where year(p.tr_date)=2006

Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Lamers



Зарегистрирован: 29.06.2006
Сообщения: 16

СообщениеДобавлено: Вт Июл 25 2006 14:36    Заголовок сообщения: Ответить с цитатой

еще забыл m.score_num=c.score_num
и
m.score_num=p.score_num
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Вт Июл 25 2006 17:55    Заголовок сообщения: Ответить с цитатой

немного не то, таким макаром у меня не получается группировать
и получать суммы в одной строке
по каждому "UNION" получается, но это несколько строк.

Возможно ли это сделать через JOIN?
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Lamers



Зарегистрирован: 29.06.2006
Сообщения: 16

СообщениеДобавлено: Ср Июл 26 2006 09:45    Заголовок сообщения: Ответить с цитатой

А можно визуально увидеть, что же ты хочешь получить, потому что я дал тебе вариант, кот. выдает данные так как ты показал в примере или не так?
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Ср Июл 26 2006 18:41    Заголовок сообщения: Ответить с цитатой

визуально я хочу получить так
Код:
score_num |  mens_name  | charge | payment
-------------------------------------------
    1       Иванов А.А.    79.0     65.0
    2       Петров А.В.    44.0     134.0
...


вообще у меня немного сложнее
Код:
         MENS
-------------------------------------
score_num | men_name    | street_id
-------------------------------------
1         | Иванов А.А. |  1
2         | Петров И.В. |  2


есть ещё таблицы streets
Код:
id   | place_id | street_name
------------------------------
 1      1         ул.Ерёмина
 2      1         ул.Мира
 3      2         пер.Боричевского


и places
Код:
id   | place_name
------------------------------
 1     мкр.Северный
 2     Военвед
 3     пос.Дубки


в итоге мне надо группировать суммы по улицам
или микрорайонам
Код:
place_name   |  street_name    | charge | payment
--------------------------------------------------
мкр.Северный   ул.Ерёмина       342.11    544.0
мкр.Северный   ул.Мира          231.50    134.0
Военвед        пер.Боричевского 5731.1    2331.0


когда я делаю это через UNION, то данные не группируются,
при попытке это сделать, сервак выдаёт ошибку
как я понимаю, есть это через JOIN сделать, то
должна прокатить группировка

в первом посте я выложил упрощённую модель данных, не хотелось запутывать уважаемых гуру, и нужно просто разобраться как надо сделать на простом примере, понять суть
да и сперва надо строить список по абанентам, а только потом, убедившись, что выбираем то, что надо группировать и получать суммы
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Ср Июл 26 2006 19:28    Заголовок сообщения: Ответить с цитатой

решение найдено
Код:
SELECT
         m.score_num,
         a.charge,
         b.payment,
         s.street_name
FROM
         streets AS s, mens AS m
              left outer join
              (
                   select
                         sum(c.tr_value) AS charge ,
                         c.score_num
                   from
                         transact_charge c
                   group by c.score_num
             )AS  A on (A.score_num = m.score_num)
             left outer join
             (
                   select
                        sum(p.tr_value) AS payment,
                        p.score_num
                  from
                        transact_payment p
                  group by p.score_num
             )AS B on( B.score_num = m.score_num)
       WHERE s.id = m.street_id
       ORDER BY m.score_num

этот запрос я думаю дальше, доведу до ума сам

есть ещё один вопросик....
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Ср Июл 26 2006 19:49    Заголовок сообщения: Ответить с цитатой

Вопроси такой...
численность абанентов меняется со временем,
абаненты могут становиться и сниматься с учёта,
в их квартирах может меняться численность жильцов,
и чтобы это отразить, помимо таблички MENS, есть табличка COUNTS
Код:
---------------------------------------------
               COUNTS
---------------------------------------------
period_id |  score_num |  c_date     | men_all
---------------------------------------------
1         |  1         |  17.07.2005 | 1        постановка на учёт, 1 чел
3         |  1         |  09.02.2006 | 2        с 03.03.2006 уже 2 чел
3         |  1         |  01.03.2006 | 3        3 чел
3         |  1         |  01.08.2006 | 1        снова 1
2         |  1         |  01.01.2055 | 0        снятие с учёта, 0 чел

есть необходимость узнать, сколько жильцов в квартире абанента
на определённую дату, например на 10 марта 2006 года это 3 чел
а на 12 июня 2008 это 1 чел
для одного человека у меня это получилось сделать так
Код:

  SELECT
  C.men_all,
  C.c_date, mens.score_num
  FROM   mens,
  counts AS A, counts AS B,counts AS C
  WHERE  mens.score_num = A.score_num
  AND    mens.score_num = B.score_num
  AND    mens.score_num = C.score_num
  AND    A.period_id = 1
  AND    B.period_id = 2
  AND    C.c_date >= A.c_date
  AND    C.c_date <= B.c_date
  AND    A.c_date <= 20060310
  AND    B.c_date >= 20060310
  AND    C.c_date <= 20060310
  AND    mens.score_num = 1
  ORDER BY C.c_date DESC
  LIMIT 0,1;

это работает, но я могу получить данные только на одного человека,
получить эти данные для всех - не выйдет помешает LIMIT

как можно получить список вида
Код:
score_num |  men_all
-----------------------
1         |    1
2         |    2
3         |    3
4         |    2
5         |    2
6         |    0

_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Lamers



Зарегистрирован: 29.06.2006
Сообщения: 16

СообщениеДобавлено: Пн Июл 31 2006 10:45    Заголовок сообщения: Ответить с цитатой

а уникальный ключ у тебя в этой табле "COUNTS" есть?
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Пн Июл 31 2006 16:26    Заголовок сообщения: Ответить с цитатой

конечно, я любую таблицу начинаю с поля
id, INTEGER, AUTOINCREMENT
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Пн Июл 31 2006 16:38    Заголовок сообщения: Ответить с цитатой

в принципе вопрос решился
Код:
                SELECT
                       max(C.c_date), m_c.score_num,
                       C.men_all
                FROM   mens AS m_c,
                       counts AS A, counts AS B,counts AS C
                WHERE  m_c.score_num = A.score_num
                AND    m_c.score_num = B.score_num
                AND    m_c.score_num = C.score_num
                AND    A.period_id = 1
                AND    B.period_id = 2
                AND    C.c_date >= A.c_date
                AND    C.c_date <= B.c_date
                AND    A.c_date <= r_date
                AND    B.c_date >= r_date
                AND    C.c_date <= r_date
                GROUP BY m_c.score_num

я чё-то раньше тупанул, начал LIMIT-ом, чтобы выбрать максимальную дату
в общем приведённый выше запрос прокатывает то что надо,
а затем его также (как в предыдущем посте) подрубаю к общему запросу.
раньше выполнялось 13 мин
сейчас от 8 до 15 секунд!
это вполне приемлимо, хотя если кто-то знает как быстрее? буду рад подсказке
и ещё, какие индексы в данной ситуации вы бы посоветовали создать для увеличения быстродействия?
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Lamers



Зарегистрирован: 29.06.2006
Сообщения: 16

СообщениеДобавлено: Вт Авг 01 2006 10:35    Заголовок сообщения: Ответить с цитатой

А если тебе понадобится узнать актуальное кол-во человек не на последнюю дату, а на любую другую? Smile
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Dimasm



Зарегистрирован: 25.04.2005
Сообщения: 454

СообщениеДобавлено: Вт Авг 01 2006 20:02    Заголовок сообщения: Ответить с цитатой

там всё нормально, отображается именно та дата, которая нужна
допустим нужно колличество на 10 июня 2006 года
одним из условия отсекаются все "изменения численности" которые произошли ПОСЛЕ 10 июня 2006
а двумя другими ставится ограничение - между "постановкой на учёт"(period_id = 1) и "снятием с учёта"(period_id = 2)
логично, что ближайшая дата к 10 июню 2006, это и есть искомая запись
Smile
_________________
С уважением Dimasm
Вернуться к началу
Посмотреть профиль Отправить личное сообщение
Показать сообщения:   
Этот форум закрыт, вы не можете писать новые сообщения и редактировать старые.   Эта тема закрыта, вы не можете писать ответы и редактировать сообщения.    Список форумов Архив форумов ЦИТФорума -> Базы данных Часовой пояс: GMT + 3
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах


Powered by phpBB © 2001, 2002 phpBB Group
Русская поддержка phpBB

 

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 495 6608306, ICQ 232284597
Пресс-релизы — pr@citforum.ru
Послать комментарий
Информация для авторов
This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2006 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...