SQLize Online / PHPize Online  /  SQLtest Online

SET NAMES 'utf8'; CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, podrazd int(11) DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE usersip ( id int(11) NOT NULL AUTO_INCREMENT, idUsers int(11) DEFAULT NULL, ip varchar(255) DEFAULT NULL, UNIQUE INDEX id (id) ); CREATE 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) ); INSERT INTO podrazd(id, id_podrazd, txt, idAccess) VALUES (1, 1, 'Подразделение_1', NULL), (2, 2, 'Подразделение_2', NULL), (3, 3, 'Подразделение_3', NULL); INSERT INTO users(id, podrazd) VALUES (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3); INSERT INTO usersip(id, idUsers, ip) VALUES (1, 1, '192.168.0.1'), (2, 2, '192.168.0.1'), (3, 1, '192.168.0.2'), (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'); 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; 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;