| Предыдущая тема :: Следующая тема |
| Автор |
Сообщение |
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-й 
Чую, что всё можно сделать правильным использованием 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 Заголовок сообщения: |
|
|
А если тебе понадобится узнать актуальное кол-во человек не на последнюю дату, а на любую другую?  |
|
| Вернуться к началу |
|
|
Dimasm
Зарегистрирован: 25.04.2005
Сообщения: 454
|
| Добавлено: Вт Авг 01 2006 20:02 Заголовок сообщения: |
|
|
там всё нормально, отображается именно та дата, которая нужна
допустим нужно колличество на 10 июня 2006 года
одним из условия отсекаются все "изменения численности" которые произошли ПОСЛЕ 10 июня 2006
а двумя другими ставится ограничение - между "постановкой на учёт"(period_id = 1) и "снятием с учёта"(period_id = 2)
логично, что ближайшая дата к 10 июню 2006, это и есть искомая запись
_________________
С уважением Dimasm |
|
| Вернуться к началу |
|
|
|
FAQ 
Поиск 
Пользователи 
Группы
Регистрация
Профиль 
Войти и проверить личные сообщения 
Вход 
