Несколько полезных SQL запросов
Код: Выделить всё
SELECT u.id AS uid, inet_ntoa(ig.ip)
FROM users u,
service_links sl,
iptraffic_service_links isl,
ip_groups ig,
accounts a
WHERE u.id=sl.user_id
AND u.basic_account=a.id
AND a.is_blocked != 0
AND isl.id=sl.id
AND isl.ip_group_id=ig.ip_group_id
AND ig.is_deleted=0
AND u.is_deleted=0
AND sl.is_deleted=0 ORDER BY u.id;
Код: Выделить всё
| 3698 | NULL |
| 3705 | NULL |
| 3710 | NULL |
| 3712 | NULL |
| 3714 | NULL |
| 3715 | NULL |
| 3716 | NULL |
+------+------------------+
P.S.
Заработало так:
Код: Выделить всё
SELECT u.id AS uid, inet_ntoa(4294967295 & ig.ip) FROM users u, service_links sl, iptraffic_service_links isl, ip_groups ig, accounts a WHERE u.id=sl.user_id AND u.basic_account=a.id AND a.is_blocked != 0 AND isl.id=sl.id AND isl.ip_group_id=ig.ip_group_id AND ig.is_deleted=0 AND u.is_deleted=0 AND sl.is_deleted=0 ORDER BY u.id;
- Davy_Jones
- Сообщения: 36
- Зарегистрирован: Ср июл 27, 2011 12:50
Помогите пожалуйста с запросом на выборку
IP + tariff_id не удаленных пользователей с действующими тарифами.
Никак не соображу с чем поле ip связать для перекрёстной проверки.
Наваял тут, вроде похоже, но двойные записи вылазиют.
IP + tariff_id не удаленных пользователей с действующими тарифами.
Никак не соображу с чем поле ip связать для перекрёстной проверки.
Наваял тут, вроде похоже, но двойные записи вылазиют.
Код: Выделить всё
SELECT inet_ntoa(t1.ip&0xffffffff) as IP, t2.account_id, t2.tariff_id FROM ip_groups as t1, account_tariff_link as t2 where t1.is_deleted=0 and t2.is_deleted=0;
Davy_Jones писал(а):Помогите пожалуйста с запросом на выборку
IP + tariff_id не удаленных пользователей с действующими тарифами.
Код: Выделить всё
SELECT inet_ntoa(ig.ip), t.id
FROM accounts a, service_links sl, iptraffic_service_links il, ip_groups ig, users u, account_tariff_link atl, tariffs t
WHERE sl.account_id=a.id AND sl.id=il.id AND il.ip_group_id=ig.ip_group_id AND u.basic_account=a.id AND atl.account_id = a.id AND t.id = atl.tariff_id AND sl.is_deleted=0 AND il.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND atl.is_deleted=0
ORDER BY u.id
- Davy_Jones
- Сообщения: 36
- Зарегистрирован: Ср июл 27, 2011 12:50
Без стоимости и времени как-то так:Bcs писал(а):Подскажите, пжлста, с запросом
Выбрать
tax_number(ИНН), tariffs.name, full_name, sum(bytes), Стоимость_скаченного , ip (как для vpn , так и для неvpn)
По тарифу 'Traf%' и определенном временном промежутке.
Код: Выделить всё
SELECT u.tax_number, t.name, u.full_name, sum(di.bytes)/1024/1024, inet_ntoa(ig.ip)
FROM accounts a, service_links sl, iptraffic_service_links isl, ip_groups ig, users u, account_tariff_link atl, tariffs t, dtagg_iptraffic di
WHERE sl.account_id=a.id AND sl.id=isl.id AND isl.ip_group_id=ig.ip_group_id AND u.basic_account=a.id AND atl.account_id = a.id AND t.id = atl.tariff_id
AND isl.id=di.slink_id AND di.tclass = ID входящего класса трафика AND di.is_closed=0
AND sl.is_deleted=0 AND isl.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND atl.is_deleted=0
GROUP BY u.tax_number, t.name, u.full_name, di.bytes, di.discounted_without_tax, ig.ip
ORDER BY u.full_name
Спасибо, чуть поправил
Получаю
Но какбы еще получить выборку ток за определенное время и поле сумма к оплате.
Код: Выделить всё
SELECT u.tax_number, t.name, u.full_name, sum(di.bytes)/1024/1024, inet_ntoa(ig.ip&0xffffffff)
FROM accounts a, service_links sl, iptraffic_service_links isl, ip_groups ig, users u, account_tariff_link atl, tariffs t, dtagg_iptraffic di
WHERE sl.account_id=a.id AND sl.id=isl.id AND isl.ip_group_id=ig.ip_group_id AND u.basic_account=a.id AND atl.account_id = a.id AND t.id = atl.tariff_id
AND isl.id=di.slink_id AND di.tclass = 10 AND di.is_closed=0
AND sl.is_deleted=0 AND isl.is_deleted=0 AND ig.is_deleted=0 AND a.is_deleted=0 AND u.is_deleted=0 AND atl.is_deleted=0 AND t.name like 'Тра%'
GROUP BY u.tax_number, t.name, u.full_name, di.bytes, di.discounted_without_tax, ig.ip
ORDER BY u.full_name;
Код: Выделить всё
+-------------+-----------------------------------+-------------------+-------------------------+-----------------------------+
| tax_number | name | full_name | sum(di.bytes)/1024/1024 | inet_ntoa(ig.ip&0xffffffff) |
+-------------+-----------------------------------+-------------------+-------------------------+-----------------------------+
| 23488888888 | Трафик 90коп за 1 Мб | Тестовый2 | 315.23014450 | 10.0.0.34 |
| 23488888888 | Трафик 90коп за 1 Мб | Тестовый2 | 462.23170090 | 172.16.99.103 |
+-------------+-----------------------------------+-------------------+-------------------------+-----------------------------+
попробовал дергать данные таким образом:
mysql -h127.0.0.1 -uroot -B -N -D UTM5 -e '\
SELECT inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS user_ip, \
users.login, router_comments AS router_ip, users.port_number FROM users \
INNER JOIN service_links ON users.id=service_links.user_id \
INNER JOIN iptraffic_service_links ON service_links.id=iptraffic_service_links.id \
INNER JOIN ip_groups ON iptraffic_service_links.ip_group_id=ip_groups.id \
INNER JOIN routers_info ON users.remote_switch_id=routers_info.id \
WHERE ip_groups.is_deleted=0 AND users.is_deleted=0 \
AND service_links.is_deleted=0 AND routers_info.router_bin_ip<>0 AND users.port_number<>0;'
сталкнулся с проблемой service_links.id <> iptraffic_service_links.id
подскажите как правильно выполнить данный запрос?
(не считая поиска по логину)
mysql -h127.0.0.1 -uroot -B -N -D UTM5 -e '\
SELECT inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS user_ip, \
users.login, router_comments AS router_ip, users.port_number FROM users \
INNER JOIN service_links ON users.id=service_links.user_id \
INNER JOIN iptraffic_service_links ON service_links.id=iptraffic_service_links.id \
INNER JOIN ip_groups ON iptraffic_service_links.ip_group_id=ip_groups.id \
INNER JOIN routers_info ON users.remote_switch_id=routers_info.id \
WHERE ip_groups.is_deleted=0 AND users.is_deleted=0 \
AND service_links.is_deleted=0 AND routers_info.router_bin_ip<>0 AND users.port_number<>0;'
сталкнулся с проблемой service_links.id <> iptraffic_service_links.id
подскажите как правильно выполнить данный запрос?
(не считая поиска по логину)
Когда-то встала задача находить первый свободный адрес в заданном диапазоне.
Я тогда сделал это через урфу, выбрав все IP-группы, а потом пробежав по ним циклом.
Затем в целях оптимизации я заменил это простой выборкой адресов из базы с сохранением цикла.
А недавно я решил, что негоже гонять эти циклы, когда делать это может сам сервер. В итоге получился запрос, который может быть кому-то полезным:
SELECT INET_NTOA(0xffffffff&ip+1)
FROM (SELECT ip FROM ip_groups WHERE is_deleted = 0 AND ip & 0xffffffff > INET_ATON('10.0.0.1') AND ip & 0xffffffff < INET_ATON('10.0.0.255') ORDER BY ip) i1
WHERE NOT EXISTS (SELECT * FROM ip_groups i2 WHERE i2.ip = i1.ip+1 AND i2.is_deleted = 0)
LIMIT 1;
Я тогда сделал это через урфу, выбрав все IP-группы, а потом пробежав по ним циклом.
Затем в целях оптимизации я заменил это простой выборкой адресов из базы с сохранением цикла.
А недавно я решил, что негоже гонять эти циклы, когда делать это может сам сервер. В итоге получился запрос, который может быть кому-то полезным:
SELECT INET_NTOA(0xffffffff&ip+1)
FROM (SELECT ip FROM ip_groups WHERE is_deleted = 0 AND ip & 0xffffffff > INET_ATON('10.0.0.1') AND ip & 0xffffffff < INET_ATON('10.0.0.255') ORDER BY ip) i1
WHERE NOT EXISTS (SELECT * FROM ip_groups i2 WHERE i2.ip = i1.ip+1 AND i2.is_deleted = 0)
LIMIT 1;
В том виде, как вы это сформулировали, задача звучит абсурдно.Pei0t писал(а):Никто не делал следующий запрос?
Необходимо выцепить тех абонентов, которые поднимают vpn не со своей учетки. Тоесть вызывающий ip адрес не равен тому, который в сервисной связке.
Вызывающий IP-адрес и не должен быть равен тому, который в сервисной связке, потому что этот адрес клиент должен получить по VPN.
У вас, видимо, две услуги? В одной физический адрес, а в другой тот, который будет получен по VPN?