Несколько полезных SQL запросов

Технические вопросы по UTM 5.0
Закрыто
Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

В итоге то что получить хотите? Что в выводе должно быть? Ваш пример, у меня в голове компилится в простыню текста со странным условием. Опишите словами что нужно?

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

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

К примеру членам групп корпоративные клиенты(110) интернет(140) на их договоры(у некоторых больше одного) выставлены такие счета такие то, каждый на такую то сумму.

В выводе нужны следующие поля:
-users.full_name
-accounts.id
-sum(invoice_entry.sum_cost)
-invoice.invoice_date
-invoice.is_payed
-invoice.is_printed


Типа такого на выходе:

ООО Лютик,110011,123руб,01.01.2016,0,1
ООО Лютик,110012,1234руб,01.01.2016,0,1
ИП Ромашкин, 110013,12руб,01.01.2016,1,1
ООО Роза, 110014,122руб,02.01.2016,0,0

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

kara писал(а):Запрос должен показывать список сумм выставленных счетов для членов определенных групп.

Типа такого на выходе:

ООО Лютик,110011,123руб,01.01.2016,0,1
ООО Лютик,110012,1234руб,01.01.2016,0,1
ИП Ромашкин, 110013,12руб,01.01.2016,1,1
ООО Роза, 110014,122руб,02.01.2016,0,0
Вы сами себе противоречите, если нужен вывод типа примера, то все просто:

Код: Выделить всё

SELECT 
 u.full_name, a.id, ie.sum_cost,FROM_UNIXTIME(i.invoice_date),i.is_payed,i.is_printed   
 FROM  users u,  users_groups_link ugl, invoices i, accounts a, invoice_entry ie, users_accounts ua    
 WHERE  
 u.id=ugl.user_id 
 AND ua.uid=u.id 
 AND ua.account_id=a.id 
 AND i.account_id=a.id 
 AND ie.invoice_id=i.id  
 AND u.is_deleted=0 
 AND ua.is_deleted=0 
 AND a.is_deleted=0 
 AND ie.sum_cost > '0'   
 AND  (ugl.group_id=110 OR ugl.group_id=140);
Если же нужно суммирование по суммам счетов, то добавляем суммирование с группировкой:

Код: Выделить всё

SELECT 
 u.full_name, a.id, SUM(ie.sum_cost),FROM_UNIXTIME(i.invoice_date),i.is_payed,i.is_printed   
 FROM  users u,  users_groups_link ugl, invoices i, accounts a, invoice_entry ie, users_accounts ua    
 WHERE  
 u.id=ugl.user_id 
 AND ua.uid=u.id 
 AND ua.account_id=a.id 
 AND i.account_id=a.id 
 AND ie.invoice_id=i.id  
 AND u.is_deleted=0 
 AND ua.is_deleted=0 
 AND a.is_deleted=0 
 AND ie.sum_cost > '0'   
 AND  (ugl.group_id=110 OR ugl.group_id=140)
 GROUP BY a.id;
Ничего сложного, просто нужно определиться, что нужно получить в итоге

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

Косяк в том, что приведенные вами запросы показывают данные НЕ только для тех, кто является членом групп 110 и 140, но и остальных, которые состоят хотя бы в одной из этих групп(тех у кого 110 и 130 или тех у кого 120 и 140) и в итоге получается месиво из физиков, юриков, интернет, телефон....

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

kara писал(а):Косяк в том, что приведенные вами запросы показывают данные НЕ только для тех, кто является членом групп 110 и 140, но и остальных, которые состоят хотя бы в одной из этих групп(тех у кого 110 и 130 или тех у кого 120 и 140) и в итоге получается месиво из физиков, юриков, интернет, телефон....
Ну тогда перепишите с IN, не вижу особых сложностей, пример без суммирования, смысл такой, что изначально делаем выборку, только по юзерам входящим в обе группы:

Код: Выделить всё

SELECT 
u.full_name, a.id, ie.sum_cost,FROM_UNIXTIME(i.invoice_date),i.is_payed,i.is_printed     
FROM  
users u,  users_groups_link ugl, invoices i, accounts a, invoice_entry ie, users_accounts ua      
WHERE    
u.id=ugl.user_id   
AND ua.uid=u.id   
AND ua.account_id=a.id   
AND i.account_id=a.id   
AND ie.invoice_id=i.id 
AND u.is_deleted=0 
AND ua.is_deleted=0 
AND a.is_deleted=0 
AND ie.sum_cost > '0' 
AND u.id IN 
	(SELECT 
	u.id  
	FROM users u, users_groups_link ugl   
	WHERE 
	u.id=ugl.user_id 
	AND (ugl.group_id=131 OR ugl.group_id=130) 
	GROUP BY 
	u.id 
	HAVING COUNT(u.id)>1);
Доделать суммирование вам в качестве домашнего задания :)

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

Да эт конечно просто, я такой запрос написал изначально себе :D

Но он выполняется по три минуты...

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

kara писал(а):Да эт конечно просто, я такой запрос написал изначально себе :D

Но он выполняется по три минуты...
Не верю :) Там изначально выполняется то, что в IN оно отрабатывает достаточно быстро, а уже только по тем юзерам что туда попали выполняется основой запрос с джоинами, у меня есть запросы на пару страниц текста, если распечатать 12 шрифтом и то 3 минуты не выполняются. Вот выполните запрос как есть и покажите время его обработки. Вот у меня:

Код: Выделить всё

11040 rows in set (0.06 sec)

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

Rico-X писал(а): Вот выполните запрос как есть и покажите время его обработки. Вот у меня:

Код: Выделить всё

11040 rows in set (0.06 sec)
84870 rows in set (57.86 sec)
Мда, на стандартных индексах и не самом передовом железе драматическая разница...

kara
Сообщения: 125
Зарегистрирован: Вс мар 21, 2010 21:02

Сообщение kara »

При этом если отдельно от друг друга:

Код: Выделить всё

SELECT
                   u.id
                   FROM users u, users_groups_link ugl
                   WHERE
                   u.id=ugl.user_id
                   AND (ugl.group_id=110 OR ugl.group_id=130)
                   AND u.is_deleted=0
                   GROUP BY
                   u.id
                   HAVING COUNT(u.id)>1
123 rows in set (0.03 sec)
И:

Код: Выделить всё

SELECT 
u.full_name, a.id, ie.sum_cost,FROM_UNIXTIME(i.invoice_date),i.is_payed,i.is_printed      
FROM  
users u,  users_groups_link ugl, invoices i, accounts a, invoice_entry ie, users_accounts ua      
WHERE    
u.id=ugl.user_id    
AND ua.uid=u.id    
AND ua.account_id=a.id    
AND i.account_id=a.id    
AND ie.invoice_id=i.id 
AND u.is_deleted=0 
AND ua.is_deleted=0 
AND a.is_deleted=0 
AND ie.sum_cost > '0' 
AND ugl.group_id=110;
31060 rows in set (1.10 sec)

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

Что-то не так в настройке сервера, не должно быть такой огромной разницы, прогоните проверку хотя-бы https://raw.githubusercontent.com/major ... qltuner.pl что-то где-то не кешируется в памяти.

Morbid
Сообщения: 104
Зарегистрирован: Пт окт 02, 2009 15:00
Откуда: МО

Сообщение Morbid »

А кто может подсказать, каким чудесным полями сейчас связаны политики списания с лицевыми счетами?

То есть в оригинале хотелось бы получить данные
лицевой счет <-> ид_политики списания.

P.S. Мне хотя бы понять через какие таблицы\поля есть связь. Запрос написать не беда.

Аватара пользователя
Magnum72
Сообщения: 1947
Зарегистрирован: Чт сен 22, 2005 06:54
Контактная информация:

Сообщение Magnum72 »

Morbid писал(а):А кто может подсказать, каким чудесным полями сейчас связаны политики списания с лицевыми счетами?

То есть в оригинале хотелось бы получить данные
лицевой счет <-> ид_политики списания.

P.S. Мне хотя бы понять через какие таблицы\поля есть связь. Запрос написать не беда.
Никак. политики списания теперь привязаны к переодическим сервисным линкам, наблюдать можно их в таблице periodic_service_links.policy_id

Vans
Сообщения: 133
Зарегистрирован: Чт сен 01, 2005 20:45

Сообщение Vans »

А кто-нибудь знает как вывести пользователей у которых подключен один тариф и при этом не подключен другой?

Rico-X
Сообщения: 164
Зарегистрирован: Вт окт 25, 2011 12:04

Сообщение Rico-X »

Vans писал(а):А кто-нибудь знает как вывести пользователей у которых подключен один тариф и при этом не подключен другой?
Без конкретизации видится как счетчик вида

Код: Выделить всё

GROUP BY
users.id
HAVING COUNT&#40;users.id&#41;=1
Выведет пользователей, у которых установлен только один тариф из указанных в WHERE, если нужна какая-то более конкретная логика - распишите кейс подробно, возможно придется переписать с IN или еще как-то извратиться.

Cramac
Сообщения: 454
Зарегистрирован: Сб июл 01, 2006 17:59

Сообщение Cramac »

А кто нить подскажет, как запросить историю платежей на версии 5.3 ?
с участием архивов

Закрыто