Архивы sql

SQL: удаление дублей записей в таблице

03.04.2025 Павел Грибов MySQL

Ну задача написания нужного запроса сводится к следующим шагам:

Сделаем выборку записей, сгруппировав по дублирующемуся полю. Получим количество дублей, и максимальный id дубля (чтобы знать какой удалять)
Если количество записей больше 1, то запись с максимальным id удалим

В итоге запрос может получится примерно таким:

delete from ls_indications where id in (
	select max_id from (
		select count(ls) as cnt,max(id) as max_id from ls_indications where period='2025-03-01' group by ls
	) as zxc where zxc.cnt>1	
)

delete from ls_indications where id in (

select max_id from (

select count(ls) as cnt,max(id) as max_id from ls_indications where period='2025-03-01' group by ls

) as zxc where zxc.cnt>1

)

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

Оставить комментарий sql, удаление дублей

Вложеный запрос в процедуре MySQL, курсоры и прочие прелести

06.06.2018 Павел Грибов MySQL

Оставляю код процедуры для «истории».

CREATE  PROCEDURE `GetInfo`(IN `mac_sw` VARCHAR(20), IN `port_sw` VARCHAR(20)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN
DECLARE value2 varchar(200); 
DECLARE type2 int(11); 
DECLARE code2 int(11); 
DECLARE cnt INT(10);
DECLARE DONE integer default 0;
DECLARE ip_cur CURSOR FOR SELECT code, type, value FROM dhcp_subnets where segment_id in (SELECT segment_id FROM dhcp_clients_by_relay WHERE upper(relay_id) = upper(mac_sw) AND upper(relay_port) = upper(port_sw)) UNION SELECT code, type, value FROM dhcp_clients_by_relay WHERE upper(relay_id) = upper(mac_sw) AND upper(relay_port) = upper(port_sw) ORDER BY CODE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   OPEN ip_cur;  
   SET cnt=0;
   REPEAT
     FETCH ip_cur INTO code2,type2,value2;
     IF NOT DONE THEN
       SELECT code2 as code,type2 as type,value2 as value;
    END IF;
    SET cnt=cnt+1;
   UNTIL DONE END REPEAT;
   CLOSE ip_cur;
  IF cnt=1 THEN 
   SELECT 1 as code,5 as type,"255.255.224.0" as value;
   SELECT 3 as code,5 as type,"172.30.128.1" as value;
   SELECT 6 as code,5 as type,"11.163.82.1,8.8.8.8" as value;
   SELECT 15 as code,4 as type,"цывацу.ru" as value;
   SELECT 51 as code,2 as type,"86400" as value;
   SELECT 1009 as code,5 as type,CONCAT("172.30.",ROUND((RAND() * (254 - 0)) + 0),".",ROUND((RAND() * (254 - 0)) + 0)) as value;
  END IF;
END

CREATE PROCEDURE `GetInfo`(IN `mac_sw` VARCHAR(20), IN `port_sw` VARCHAR(20)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN

DECLARE value2 varchar(200);

DECLARE type2 int(11);

DECLARE code2 int(11);

DECLARE cnt INT(10);

DECLARE DONE integer default 0;

DECLARE ip_cur CURSOR FOR SELECT code, type, value FROM dhcp_subnets where segment_id in (SELECT segment_id FROM dhcp_clients_by_relay WHERE upper(relay_id) = upper(mac_sw) AND upper(relay_port) = upper(port_sw)) UNION SELECT code, type, value FROM dhcp_clients_by_relay WHERE upper(relay_id) = upper(mac_sw) AND upper(relay_port) = upper(port_sw) ORDER BY CODE;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

OPEN ip_cur;

SET cnt=0;

REPEAT

FETCH ip_cur INTO code2,type2,value2;

IF NOT DONE THEN

SELECT code2 as code,type2 as type,value2 as value;

END IF;

SET cnt=cnt+1;

UNTIL DONE END REPEAT;

CLOSE ip_cur;

IF cnt=1 THEN

SELECT 1 as code,5 as type,"255.255.224.0" as value;

SELECT 3 as code,5 as type,"172.30.128.1" as value;

SELECT 6 as code,5 as type,"11.163.82.1,8.8.8.8" as value;

SELECT 15 as code,4 as type,"цывацу.ru" as value;

SELECT 51 as code,2 as type,"86400" as value;

SELECT 1009 as code,5 as type,CONCAT("172.30.",ROUND((RAND() * (254 - 0)) + 0),".",ROUND((RAND() * (254 - 0)) + 0)) as value;

END IF;

END

Оставить комментарий cursor, if, mysql, sql

Еще один полезный SQL запрос для Zabbix

14.11.2016 Павел Грибов Linux

Получение триггеров с учетом зависимостей. Т.е. Если некий тригер А зависит от триггера Б, то его в списке не будет.

SELECT (UNIX_TIMESTAMP()-triggers.lastchange)/60 as minut,hosts.host,        triggers.description,        triggers.comments,        triggers.triggerid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE hosts.status = 0   AND items.status = 0   AND triggers.status = 0   AND triggers.priority=5   AND triggers.value=1 and ((UNIX_TIMESTAMP()-triggers.lastchange)/60)>15 and triggers.triggerid in (select triggerid_up from trigger_depends where triggerid_down in (SELECT triggers.triggerid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE hosts.status = 0   AND items.status = 0   AND triggers.status = 0   AND triggers.priority=5   AND triggers.value=1 and ((UNIX_TIMESTAMP()-triggers.lastchange)/60)>15 group by triggerid))  group by triggers.triggerid

SELECT (UNIX_TIMESTAMP()-triggers.lastchange)/60 as minut,hosts.host, triggers.description, triggers.comments, triggers.triggerid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE hosts.status = 0 AND items.status = 0 AND triggers.status = 0 AND triggers.priority=5 AND triggers.value=1 and ((UNIX_TIMESTAMP()-triggers.lastchange)/60)>15 and triggers.triggerid in (select triggerid_up from trigger_depends where triggerid_down in (SELECT triggers.triggerid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE hosts.status = 0 AND items.status = 0 AND triggers.status = 0 AND triggers.priority=5 AND triggers.value=1 and ((UNIX_TIMESTAMP()-triggers.lastchange)/60)>15 group by triggerid)) group by triggers.triggerid

Оставить комментарий dashboard, mysql, sql, zabbix, запрос

Апрель 2025
Пн	Вт	Ср	Чт	Пт	Сб	Вс
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30