Форум пользователей MySQL

SanyaGarik · 18.11.2011 15:14:28

добрый день. Есть 2 таблицы:
CREATE TABLE `ats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`FromNum` text NOT NULL,
`ToNum` text NOT NULL,
`InternalNum` int(11) NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `InternalNum` (`InternalNum`)
) ENGINE=InnoDB AUTO_INCREMENT=25885 DEFAULT CHARSET=utf8

и

CREATE TABLE `our_numbers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL,
`operator` varchar(40) DEFAULT NULL,
`info` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

Надо сделать выборку : все номера из таблицы our_numbers и во втором столбце- сколько каждый из номеров звонил. Я пишу запрос:
SELECT `our_numbers`.name, (select count(ats.ToNum)) from our_numbers, ats WHERE `our_numbers`.name like `ats`.ToNum, но он выводит только один из номеров и count считает неправильно. Возможно, проблема в индексах

Отредактированно SanyaGarik (18.11.2011 15:14:58)

deadka · 18.11.2011 15:51:52

В индексах проблемы быть не может. Как осуществляется привязка атс к номерам? поле `our_numbers`.`name` ссылается на `ats`.`ToNum`?

SanyaGarik · 18.11.2011 15:56:51

нет, я не понимаю как сделать привязку в этой ситуации, в our_numbers - просто список наших номеров, а в ats - общая статистика, из которой надо выбирать конкретные номера из таблицы our_numbers

deadka · 18.11.2011 15:59:03

Но связать эти таблице нужно, иначе как вычислить статистику по определенному номеру. Попробуйте данные приложить сюда, может по ним понятнее будет.

SanyaGarik · 18.11.2011 16:11:17

ats:
id FromNum ToNum Iternal
------------------------------------------------------------
3 0674444129    380445994444 200 1318318185
4    380504444901    380445994444 200 1318318260
5 0544449901    380577284444 203 1318318305
6    380672444481    380988604444 203 1318318455
7 0672444481    380577284444 203 1318318467
8    380677444486    380577284444 200 1318318615
9 0577054444    380577624444 200 1318318712
10 0445284444    380445994444 200 1318318923
11 0445284444    380445994444 200 1318318950
12    380677464444    380988604444 201 1318319131
13 0644447998    380577284444 203 1318319144
14    380444479217    380664444444 203 1318319226
15 0952444452    380445994444 200 1318319286
16 0577444400    380577624444 203 1318319399
17    380544448534    380577624444 203 1318319556
18 0994844443    380577284444 203 1318319599
и тд

our_numbers:
id name operator info
-----------------------------------------------
1 380445994444 МТС
2 380577284444 Киевстар
3 380963874444 Киевстар
4 380577624444 Life
5 380634394444 Life
6 380664444444 MTC
7 380505074444 MTC
8 380935724444 Life    Визитки
9 380676614444 Киевстар    Визитки
10 380667384444 MTC    Визитки
11 380577624444 CDMA    Харьков 1
12 380577284444 CDMA       Харьков 2
13 380445994444 CDMA    Киев
14 380577624444 CDMA    Запчасти

и надо узнать сколько раз каждый из номеров в our_numbers звонил, тоесть должна получиться таблица вида (например):
id name count
--------------------------------
1 380445994444 2
2 380577284444 10
и тд по остальным

Отредактированно SanyaGarik (18.11.2011 16:14:33)

deadka · 18.11.2011 17:18:47

Я же говорил, что они связаны .
Попробуйте так

SELECT `on`.*, (SELECT count(*) FROM ats WHERE `ToNum`=`on`.`name`) FROM `our_numbers` `on`;

Отредактированно deadka (18.11.2011 17:22:30)

SanyaGarik · 18.11.2011 17:20:51

#1051 - Unknown table 'on'

deadka · 18.11.2011 17:21:53

Да, действительно.

SELECT `on`.*, (SELECT count(*) FROM ats WHERE `ToNum`=`on`.`name`) FROM `our_numbers` `on`;

SanyaGarik · 18.11.2011 17:26:21

выводит только count одного из номеров, но неправильно, проверяю через
SELECT ats.ToNum
FROM ats
WHERE ToNum =380933944444- находит около 10 строк, а через SELECT `on`.*, (SELECT count(*) FROM ats WHERE `ToNum`=`on`.`name`) FROM `our_numbers` `on`; находит 0

deadka · 18.11.2011 17:36:52

хм. приложите пожалуйста данные из табличек - чтобы ошибку можно было воспроизвести. Можно не все - любой минимальный набор, чтоб ошибку увидеть.

SanyaGarik · 18.11.2011 18:14:16

deadka написал:
хм. приложите пожалуйста данные из табличек - чтобы ошибку можно было воспроизвести. Можно не все - любой минимальный набор, чтоб ошибку увидеть.

прошу прощения, оказывается проблема в том, что в ats перед каждым номером стоял пробел, теперь ваш запрос корректно работает, спасибо

deadka · 18.11.2011 18:16:30

Я сердцем чувствовал . Пожалуйста. Только у Вас ведь телефон фигурирует не только в виде "кому звонил", а и "кто звонил" - но тут будет почти то же самое, только в запросе ToNum надо на FromNum поменять.

SanyaGarik · 18.11.2011 18:55:27

и все таки не могу понять как работает SELECT `on`.*, (SELECT count(*) FROM ats WHERE `ToNum`=`on`.`name`) FROM `our_numbers` `on`; если внутренний запрос (SELECT count(*) FROM ats WHERE `ToNum`=`on`.`name`) не выполняется, разве внутренний запрос может обращаться к внешнему? (как например он получил FROM `our_numbers` `on`;?)

deadka · 18.11.2011 19:07:08

Да, это называется коррелированный запрос (см. http://citforum.ru/database/sql_kg/3-3-5.shtml), там внутренний запрос обращается к записи из внешнего запроса. Как это происходит: внешний курсор бежит по таблице our_numbers (ее псевдоним - `on`), и для каждой записи в таблице our_numbers выполняет подзапрос - выборку из таблицы ats по условию, зависящему от записи во внешней таблице.

Отредактированно deadka (18.11.2011 19:09:04)

SanyaGarik · 23.11.2011 14:30:00

еще такой вопрос: есть запрос
SELECT `on`.*,
(SELECT count(*) FROM ats WHERE `ToNum` like `on`.name) as count_calls,
(select count(`ats`.id) FROM `ats` WHERE TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(`ats`.time)) <= 30
AND `ats`.ToNum like `on`.name) as days_30,

(select count(`ats`.id) FROM `ats` WHERE TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(`ats`.time)) <= 7
AND `ats`.ToNum like `on`.name) as days_7,

(select count(`ats`.id) FROM `ats` WHERE `ats`.time>UNIX_TIMESTAMP(CURDATE()) AND `ats`.ToNum like `on`.name) as days_1,

(select count(`ats`.id) from `ats` WHERE `ats`.time>$timestamp AND `ats`.ToNum like `on`.name) as hour

FROM `our_numbers` `on`";

выбирает номера из таблицы our_numbers и по каждому выводит статистику: сколько раз по этому номеру звонили за последний месяц, неделю, день и час. Не могу сделать запрос, чтоб посмотреть, сколько раз звонили за предыдущий день, добавляю в запрос такое:
...
(select count(`ats`.id) FROM `ats` WHERE `ats`.time>UNIX_TIMESTAMP(CURDATE()) AND `ats`.time>CURRENT_TIMESTAMP - interval 1 day
AND `ats`.ToNum like `on`.name) as days_2,
...
Ничего не возвращает (все даты в формате timestamp)

SanyaGarik · 23.11.2011 15:05:10

нашел такой вариант
(select count(`ats`.id) FROM `ats` WHERE TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(`ats`.time)) < 2 AND TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(`ats`.time)) >=1
AND `ats`.ToNum like `on`.name) as days_2,
вроде работает

Форум пользователей MySQL

#1 18.11.2011 15:14:28

проблема с поиском

#2 18.11.2011 15:51:52

Re: проблема с поиском

#3 18.11.2011 15:56:51

Re: проблема с поиском

#4 18.11.2011 15:59:03

Re: проблема с поиском

#5 18.11.2011 16:11:17

Re: проблема с поиском

#6 18.11.2011 17:18:47

Re: проблема с поиском

#7 18.11.2011 17:20:51

Re: проблема с поиском

#8 18.11.2011 17:21:53

Re: проблема с поиском

#9 18.11.2011 17:26:21

Re: проблема с поиском

#10 18.11.2011 17:36:52

Re: проблема с поиском

#11 18.11.2011 18:14:16

Re: проблема с поиском

deadka написал:

#12 18.11.2011 18:16:30

Re: проблема с поиском

#13 18.11.2011 18:55:27

Re: проблема с поиском

#14 18.11.2011 19:07:08

Re: проблема с поиском

#15 23.11.2011 14:30:00

Re: проблема с поиском

#16 23.11.2011 15:05:10

Re: проблема с поиском

Board footer