Как выбрать уникальные ip адреса или их количество из связанных таблиц?

Question

[yura_born]

У меня 3 таблицы:
1. таблица

TABLE usersr.users (
  id int(11) NOT NULL AUTO_INCREMENT,
  date_start datetime DEFAULT NULL,
  osnovanie varchar(255) DEFAULT NULL,
  fam varchar(255) DEFAULT NULL,
  name varchar(50) DEFAULT NULL,
  otchestvo varchar(255) DEFAULT NULL,
  podrazd int(11) DEFAULT NULL,
  sluzhba int(11) DEFAULT NULL,
  dolzhnost varchar(255) DEFAULT NULL,
  phone varchar(255) DEFAULT NULL,
  ip_adress varchar(255) DEFAULT NULL,
  ip_adress_btk varchar(255) DEFAULT NULL,
  login varchar(255) DEFAULT NULL,
  pass varchar(255) DEFAULT NULL,
  date_end datetime DEFAULT NULL,
  arhive tinyint(1) DEFAULT NULL,
  primechanie text DEFAULT NULL,
  otkritDostupUIT tinyint(1) DEFAULT NULL,
  uchetkaBezIP tinyint(1) DEFAULT NULL,
  idAccess int(11) DEFAULT NULL,
  idKip int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

2. таблица

TABLE podrazd (
  id int(11) NOT NULL AUTO_INCREMENT,
  id_podrazd int(20) DEFAULT NULL,
  txt varchar(255) DEFAULT NULL,
  idAccess int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

3. таблица

TABLE usersr.usersip (
  id int(11) NOT NULL AUTO_INCREMENT,
  idUsers int(11) DEFAULT NULL,
  ip varchar(255) DEFAULT NULL,
  btk tinyint(4) DEFAULT NULL,
  ipNAT varchar(255) DEFAULT NULL,
  UNIQUE INDEX id (id)
)

Как мне получить уникальные ip адреса в подразделениях или их количество, написал что-то вроде этого, но ничего не получается:

SELECT DISTINCT ip.ip
FROM
(
SELECT p.txt, ip.ip
FROM users u
LEFT JOIN usersip ip
  ON u.idAccess = ip.idUsers
LEFT JOIN podrazd p
  ON u.podrazd = p.idAccess
)

Comments

[Akina]

Выложите воспроизводимый пример:

CREATE TABLE всех таблиц (все лишние для вопроса поля - убрать!)
INSERT INTO с примером данных (2-3 базовых записи, не более 3 связанных на одну базовую)
Требуемый ответ для показанных данных, с подробными пояснениями

[yura_born]

Akina,

CREATE TABLE test.users (
  id int(11) NOT NULL AUTO_INCREMENT,
  podrazd int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

CREATE TABLE test.usersip (
  id int(11) NOT NULL AUTO_INCREMENT,
  idUsers int(11) DEFAULT NULL,
  ip varchar(255) DEFAULT NULL,
  UNIQUE INDEX id (id)
)

CREATE TABLE test.podrazd (
  id int(11) NOT NULL AUTO_INCREMENT,
  id_podrazd int(20) DEFAULT NULL,
  txt varchar(255) DEFAULT NULL,
  idAccess int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

SET NAMES 'utf8';

INSERT INTO test.podrazd(id, id_podrazd, txt, idAccess) VALUES
(1, 1, 'Подразделение_1', NULL),
(2, 2, 'Подразделение_2', NULL),
(3, 3, 'Подразделение_3', NULL);


INSERT INTO test.users(id, podrazd) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3);


INSERT INTO test.usersip(id, idUsers, ip) VALUES
(1, 1, '192.168.0.1\r\n'),
(2, 2, '192.168.0.1\r\n'),
(3, 1, '192.168.0.2\r\n'),
(4, 4, '192.168.10.1'),
(5, 3, '192.168.10.1'),
(6, 5, '192.168.11.45'),
(7, 6, '192.168.11.56'),
(8, 2, '192.23.90.12'),
(9, 2, '168.11.23.90');

Необходимо узнать сколько в каждом подразделении используется уникальных ip адресов.

[yura_born]

Akina, вот так я получаю подразделения и их ip

SELECT p.txt, ip.ip
FROM users u
LEFT JOIN podrazd p ON u.podrazd = p.id_Podrazd
LEFT JOIN usersip ip ON u.id = ip.idUsers

но подразделения и ip дублируются и у меня не получается их сгруппировать
мне надо:
Подразделение_1 |ip-адреса| 2
Подразделение_2 |ip-адреса| 1
Подразделение_3 |ip-адреса| 3

[Akina]

Чтобы сгруппировать, необходимо наличие GROUP BY. Чтобы посчитать количество, необходима функция COUNT(). Для подсчёта уникальных необходим DISTINCT.

[ThunderCat]

yura_born,

у меня не получается их сгруппировать

всмысле? Отсутствуют на клавиатуре символы g,r,o,u,p,b,y?

[psiklop]

ThunderCat, в смысле 'гении' как всегда, выспросили гору инфо, им чел все предоставил, а ему вместо ответа и не разбор и не sql запрос, а рассказали, что есть такой чудо оператор group by и даже вежливо показали как его писать на клаве коке. P.S. спасибо хоть дурнем не назвали

[Дмитрий]

psiklop, ну стоит отметить что как бы по факту там все сводится к тому что бы на клавиатуре напечатать group by. Чего тут разбирать реально не понятно.

[psiklop]

Дмитрий, ну твой ответ довольно сложен, ты не просто написал group by, согласен? К тому же он не делает чего просили.

[Дмитрий]

psiklop, нет мой ответ скопирован из попыток автора в вопросе, подправил поле которое юзается для join и банально дописал count distinct, group by. собственно как и сказано в комментарии у того же Akina.
Ну если он не делает то что просили, видимо я не понял что просили. Может автор обьяснит лучше?

[psiklop]

Дмитрий, к тебе никаких претензий, ты написал готовое решение как понял. Я понял, что ему надо в ответе табличку: цифрой напротив каждого подразделения кол-во уникальных IP

[Дмитрий]

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

уникальные ip адреса в подразделениях или их количество

[psiklop]

Дмитрий, а в чем колхоз? Mysql ? Люди ищут в гугле решения их тыкает либо на stack либо сюда, они тратят время читают, а тут соревнование в остроумии.

[Дмитрий]

psiklop, в том что помощь оказывают так как считают нужным и полезным те кто ее оказывает. Если помощь надо оказывать только в том виде в котором хочет просящий - это называется работа, она оплачивается и за ней на фриланс.

Учитывая что вопрос уж очень банальный - к вот этому варианту который уже написал автор

SELECT p.txt, ip.ip
FROM users u
LEFT JOIN podrazd p ON u.podrazd = p.id_Podrazd
LEFT JOIN usersip ip ON u.id = ip.idUsers

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

[psiklop]

Дмитрий, тебе мне не надо ничего доказывать, я тебе вообще ничего не писал, сейчас решению лайк поставлю, чтобы порадовать.

[psiklop]

Дмитрий, "Есть такая пословица - Не хочешь срать - не мучай жопу" - Еще раз напишу на всякий случай - это к тебе не относится, это когда помощь оказывать вообще не хочешь - то лучше ее и не оказывать. По кабале говорят полезно.

[Дмитрий]

psiklop, я просто не считаю правильным на этом ресурсе хамить аля "ты что дурак такое спрашивать" и равно как ругаться "ты как человеку помогаешь". Я не вижу первого варианта - и решил выступить.

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

[psiklop]

Дмитрий, нужно просто признать факт, что обучение как писать оператор на клавиатуре вообще не является помощью, а является хамством.

[Дмитрий]

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

[psiklop]

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

Answer 1

[Дмитрий]

Почему вы используете idAccess для джойна?

Количество уникальных ip

SELECT p.txt, count(distinct ip.ip)
FROM users u
LEFT JOIN usersip ip
  ON u.id = ip.idUsers
LEFT JOIN podrazd p
  ON u.podrazd = p.id
group by p.txt
order by p.txt;

Список ip

SELECT p.txt, ip.ip
FROM users u
LEFT JOIN usersip ip
  ON u.id = ip.idUsers
LEFT JOIN podrazd p
  ON u.podrazd = p.id
group by p.txt, ip.ip
order by p.txt;

Проверить

Comments

[yura_born]

Спасибо, сработало.
SELECT p.txt, count(distinct ip.ip)
Оказывается distinct в скобки взять нужно было.