VPF::Выбрать просроченные заявки - Форум программистов

swordfishik

Дата 27.10.2015, 01:02 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

Всем привет. Есть 2 таблицы - "заявки" и "логи авторизации клиента". Связь между таблицами по полю `client_id`. Посмотрите пример, там я наглядно все выложил - тыц. Запрос конечно не верен, не могу придумать как его верно написать. Пока выводит все.

Как я вижу работу запроса - запрос выбирает все заявки, которые не заблокированы, потом ищет владельца заявки со статусом success и смотрит дату последнего его входа (сортируем по убыванию дабы самая свежая дата была вверху, указываем лимит 1), если дата последнего входа превышает 1 день, то выводим ИД этой заявки для дальнейшей обработки.

Прошу помочь с запросом, перепробовал разные вариации запроса, но все не так. Спасибо.

swordfishik

Дата 27.10.2015, 02:31 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

Вот написал запрос - тыц, пока указал 1 DAY. По первым тестам работает, но мне не нравится, что пока просрочка не сработает, то выводится поле с пустым значением во втором столбце второй строки, нужно вторую строку вообще не выводить в этом случае, но как? И вообще как запрос составлен, лучше можно?

Akina (Online)

Дата 27.10.2015, 08:59 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Где пояснения по структуре и наполнению? Где постановка задачи? трёп один...

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

swordfishik

Дата 27.10.2015, 12:25 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

sqlfiddle работает с переменным успехом, поэтому выложу тут код:

Таблицы и заполнение:

Код

CREATE TABLE `client_bid` (
  `client_bid_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(8) unsigned NOT NULL DEFAULT '0',
  `client_bid_blocked` enum('y','n') NOT NULL DEFAULT 'n',
  PRIMARY KEY (`client_bid_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `client_bid` (`client_bid_id`, `client_id`, `client_bid_blocked`) VALUES
    (1, 1, 'n'),
    (2, 4, 'y'),
    (3, 4, 'n');

CREATE TABLE `client_login` (
  `client_login_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(8) unsigned NOT NULL DEFAULT '0',
  `client_login_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `client_login_type` enum('wait','success','failed') NOT NULL DEFAULT 'wait',
  PRIMARY KEY (`client_login_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `client_login` (`client_login_id`, `client_id`, `client_login_date`, `client_login_type`) VALUES
    (1, 1, '2015-05-24 16:06:03', 'success'),
    (2, 1, '2015-05-24 18:19:39', 'success'),
    (3, 1, '2015-05-24 18:21:07', 'success'),
    (4, 1, '2015-05-24 18:22:21', 'success'),
    (5, 1, '2015-05-24 18:24:35', 'success'),
    (6, 1, '2015-05-28 16:20:42', 'success'),
    (7, 1, '2015-05-28 16:32:56', 'success'),
    (8, 1, '2015-05-28 16:46:47', 'success'),
    (9, 1, '2015-05-28 17:46:57', 'success'),
    (10, 1, '2015-05-29 18:56:03', 'success'),
    (11, 1, '2015-05-29 19:28:52', 'success'),
    (12, 1, '2015-05-29 19:32:25', 'success'),
    (13, 1, '2015-05-29 19:57:37', 'success'),
    (14, 1, '2015-05-30 12:37:56', 'wait'),
    (15, 1, '2015-05-30 12:38:50', 'failed'),
    (16, 1, '2015-05-30 12:38:58', 'failed'),
    (17, 1, '2015-05-30 13:21:18', 'wait'),
    (18, 1, '2015-05-30 20:12:11', 'success'),
    (19, 1, '2015-06-02 20:49:12', 'success'),
    (20, 1, '2015-06-02 22:52:09', 'success'),
    (21, 1, '2015-06-02 22:54:32', 'success'),
    (22, 1, '2015-06-03 13:45:09', 'success'),
    (23, 1, '2015-06-05 12:47:24', 'success'),
    (24, 1, '2015-06-05 19:50:08', 'success'),
    (25, 1, '2015-07-21 10:09:48', 'success'),
    (26, 1, '2015-07-21 10:29:47', 'wait'),
    (27, 1, '2015-07-21 10:31:03', 'success'),
    (28, 1, '2015-07-21 10:31:21', 'failed'),
    (29, 1, '2015-07-21 14:59:10', 'wait'),
    (30, 1, '2015-07-21 14:59:45', 'success'),
    (31, 1, '2015-07-21 15:00:32', 'failed'),
    (39, 1, '2015-07-21 20:01:37', 'wait'),
    (40, 1, '2015-07-21 20:13:12', 'success'),
    (41, 1, '2015-07-25 14:08:54', 'wait'),
    (42, 1, '2015-07-25 14:20:34', 'wait'),
    (43, 1, '2015-07-25 14:24:18', 'wait'),
    (44, 1, '2015-07-26 18:06:08', 'success'),
    (45, 1, '2015-07-26 19:01:59', 'wait'),
    (46, 1, '2015-07-26 21:47:41', 'wait'),
    (47, 1, '2015-07-26 21:53:27', 'wait'),
    (48, 1, '2015-07-26 22:34:46', 'success'),
    (58, 1, '2015-09-17 21:05:48', 'wait'),
    (59, 2, '2015-09-17 22:32:07', 'wait'),
    (60, 2, '2015-09-17 22:33:46', 'wait'),
    (61, 3, '2015-09-17 22:36:34', 'wait'),
    (62, 1, '2015-09-17 22:43:50', 'wait'),
    (63, 1, '2015-09-17 22:44:31', 'wait'),
    (64, 1, '2015-09-18 01:13:20', 'wait'),
    (65, 1, '2015-09-18 01:14:13', 'wait'),
    (66, 1, '2015-10-16 19:30:13', 'success'),
    (67, 1, '2015-10-22 23:22:31', 'success'),
    (68, 1, '2015-10-23 17:31:42', 'success'),
    (69, 1, '2015-10-23 17:32:09', 'failed'),
    (70, 1, '2015-10-23 17:34:57', 'success'),
    (71, 1, '2015-10-23 17:36:17', 'success'),
    (72, 1, '2015-10-23 17:37:34', 'success'),
    (73, 1, '2015-10-23 17:58:51', 'success'),
    (74, 1, '2015-10-23 18:07:19', 'wait'),
    (75, 1, '2015-10-23 18:07:46', 'failed'),
    (76, 1, '2015-10-23 18:09:05', 'success'),
    (77, 1, '2015-10-23 18:10:40', 'success'),
    (78, 1, '2015-10-23 19:02:15', 'success'),
    (79, 1, '2015-10-23 19:04:27', 'success'),
    (80, 1, '2015-10-23 19:05:48', 'success'),
    (81, 1, '2015-10-23 21:28:25', 'success'),
    (82, 1, '2015-10-23 21:32:41', 'success'),
    (83, 1, '2015-10-23 21:34:19', 'success'),
    (84, 1, '2015-10-23 21:36:24', 'success'),
    (85, 1, '2015-10-23 21:37:30', 'success'),
    (86, 1, '2015-10-23 21:56:07', 'wait'),
    (87, 1, '2015-10-23 22:09:11', 'wait'),
    (88, 1, '2015-10-23 22:11:06', 'success'),
    (89, 1, '2015-10-23 22:17:27', 'success'),
    (90, 1, '2015-10-24 20:04:45', 'success'),
    (91, 1, '2015-10-24 20:16:37', 'success'),
    (92, 1, '2015-10-26 19:14:43', 'success'),
    (93, 1, '2015-10-26 19:32:34', 'success'),
    (94, 4, '2015-10-27 00:15:34', 'success'),
    (95, 4, '2015-10-26 01:25:46', 'failed');

Запрос по-первой ссылке (Запрос конечно не верен, не могу придумать как его верно написать. Пока выводит все.):

Код

SELECT a.`client_bid_id`,
       b.`client_login_date`
FROM `client_bid` a
JOIN `client_login` b ON (b.`client_id` = a.`client_id` AND b.`client_login_type` = 'success')
WHERE a.`client_bid_blocked` = 'n'

Запрос по-второй ссылке (Пока указал 1 DAY. По первым тестам работает, но мне не нравится, что пока просрочка не сработает, то выводится поле с пустым значением во втором столбце второй строки, нужно вторую строку вообще не выводить в этом случае, но как? И вообще как запрос составлен, лучше можно?):

Код

SELECT `client_bid`.`client_bid_id` as `id`,
       (
            SELECT `client_login`.`client_login_date` 
            FROM `client_login` 
            WHERE `client_login`.`client_id` = `client_bid`.`client_id` 
            AND `client_login`.`client_login_type` = 'success' 
            AND `client_login`.`client_login_date` < NOW() - INTERVAL 1 DAY 
            ORDER BY `client_login`.`client_login_date` DESC 
            LIMIT 1
         ) as `date`
FROM `client_bid`
WHERE `client_bid`.`client_bid_blocked` = 'n'

Garmahis

Дата 27.10.2015, 12:29 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 254
Регистрация: 23.12.2004

Репутация: 1
Всего: 4

Ты напиши что значат поля в таблице и понятнее что ты хочешь в запросе получить.

Akina (Online)

Дата 27.10.2015, 12:33 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Цитата(Akina @ 27.10.2015, 09:59

)

Где постановка задачи?

--------------------

swordfishik

Дата 27.10.2015, 12:38 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

client_bid - таблица заявок
-- client_bid_id - ид заявки
-- client_id - ид того кто заявку составил
-- client_bid_blocked - заблокирована или нет заявка

client_login - таблица входов клиента в панель
-- client_login_id - ид входа
-- client_id - ид того кто вошел в панель
-- client_login_date - дата и время входа
-- client_login_type - тип входа (wait - ожидание кода подтверждения входа, success - успешный вход, failed - ошибка при входе)

Нужно - выбрать заявки, которые являются просроченными (потом я их заблокирую). Просроченной заявкой я считаю ту, у которой создатель не входил в панель более 1 дня (цифра будет другая, для теста беру 1 день).

Во втором запросе я почти добился того, что нужно, но хотелось бы довести запрос до ума.

Garmahis

Дата 27.10.2015, 13:31 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 254
Регистрация: 23.12.2004

Репутация: 1
Всего: 4

Я не знаю как на mySQL но в MS SQL это можно сделать так:

Код


    SELECT C.client_login_date, A.client_bid_id, C.Client_id FROM (
    SELECT MAX(B.client_login_date) client_login_date, B.client_id
    FROM client_login B WITH (NOLOCK)    
    WHERE B.client_login_type = 'success'  
    GROUP BY B.client_id) C
    INNER JOIN client_bid A WITH (NOLOCK) ON A.client_id = C.client_id AND A.client_bid_blocked = 'n'  
    WHERE C.client_login_date < DATEADD(d,-1,GETDATE())

Вам надо найти аналоги функций DATEADD и GETDATE

Akina (Online)

Дата 27.10.2015, 13:40 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Цитата(swordfishik @ 27.10.2015, 13:38

)

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

Т.е. у которого с момента последнего успешного входа прошло более 1 дня или у которого имеется промежуток между двумя последовательными успешными входами более 1 дня?

Предполагаю первое. Тогда будет что-то типа:

Код


SELECT client_bid.client_bid_id
FROM client_bid
WHERE client_bid.client_bid_blocked = 'n'
AND NOT EXISTS
( SELECT 1
  FROM client_login
  WHERE client_login.client_login_type = 'success'
    AND client_login.client_login_date > NOW() - INTERVAL 1 DAY
    AND client_login.client_id = client_bid.client_id);

Или

Код


SELECT cb.client_bid_id
FROM client_bid cb
LEFT JOIN (
  SELECT cl.client_id
  FROM client_login cl
  WHERE cl.client_login_type = 'success'
    AND cl.client_login_date > NOW() - INTERVAL 1 DAY
  ) sq ON sq.client_id = cb.client_id
WHERE sq.client_id IS NULL
  AND cb.client_bid_blocked = 'n';

Это сообщение отредактировал(а) Akina - 27.10.2015, 13:43

--------------------

swordfishik

Дата 27.10.2015, 13:40 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

Вроде бы переписал, но результат не тот. Может неверно переписал конечно же.

Код


SELECT C.client_login_date, A.client_bid_id, C.Client_id FROM (
SELECT MAX(B.client_login_date) client_login_date, B.client_id
FROM client_login B
WHERE B.client_login_type = 'success'  
GROUP BY B.client_id) C
INNER JOIN client_bid A ON A.client_id = C.client_id AND A.client_bid_blocked = 'n'  
WHERE C.client_login_date < DATE_ADD(NOW(), INTERVAL 1 DAY)

Посидел еще немного, вот что вышло у меня:

Код


SELECT `client_bid`.`client_bid_id`
FROM `client_bid`
WHERE `client_bid`.`client_bid_blocked` = 'n'
AND EXISTS 
(
    SELECT `client_login`.`client_login_id`
    FROM `client_login`
    WHERE `client_login`.`client_id` = `client_bid`.`client_id`
    AND `client_login`.`client_login_type` = 'success'
    AND `client_login`.`client_login_date` < NOW() - INTERVAL 1 DAY
    ORDER BY `client_login`.`client_login_date` DESC
    LIMIT 1
)

Выводит теперь корректно 1 запись, пока 1 день не пройдет.

Garmahis

Дата 27.10.2015, 13:44 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 254
Регистрация: 23.12.2004

Репутация: 1
Всего: 4

Написал для MySQL

Код


    SELECT C.`client_login_date`, A.`client_bid_id`, C.`Client_id` FROM (
    SELECT MAX(`client_login_date`) `client_login_date`, `client_id`
    FROM `client_login`    
    WHERE `client_login_type` = 'success'  
    GROUP BY `client_id`) C
    JOIN `client_bid` A  ON A.`client_id` = C.`client_id` AND A.`client_bid_blocked` = 'n'  
    WHERE C.`client_login_date` < DATE_ADD(NOW(),INTERVAL -1 DAY)

Akina (Online)

Дата 27.10.2015, 13:46 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Цитата(swordfishik @ 27.10.2015, 13:38

)

Просроченной заявкой я считаю ту, у которой создатель не входил в панель более 1 дня (цифра будет другая, для теста беру 1 день).

На будущее - если хотите адекватного тестирования - давайте литерал (константу) даты и эталонный ответ на приведённых данных.

--------------------

swordfishik

Дата 27.10.2015, 13:47 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

Цитата(Akina @ 27.10.2015, 13:40

)

Т.е. у которого с момента последнего успешного входа прошло более 1 дня

так

Почти одновременно написали посты smile

скорей не

Код


AND NOT EXISTS

а вот так

Код


AND EXISTS

а также не

Код


> NOW()

а вот так

Код


< NOW()

Akina (Online)

Дата 27.10.2015, 13:50 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20570
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 453

Цитата(swordfishik @ 27.10.2015, 14:47

)

скорей не

а вот так

а также не

а вот так

А теперь представь, что заявка есть, а логинов нет вообще. Скажем, почистили таблицу.
Моему запросу пох. Твоему - нет.

--------------------

swordfishik

Дата 27.10.2015, 13:52 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 45
Регистрация: 23.1.2008

Репутация: нет
Всего: нет

Цитата(Garmahis @ 27.10.2015, 13:44

)

Написал для MySQL

в результате пусто, должно вывести первую заявку под ИД 1, что у меня и вышло

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »