Сортировка по количеству вхождений

Question

SELECT *, count(`title_prev` LIKE '%{$text}%') AS sort FROM `news` where `title_prev` LIKE '%{$text}%' ORDER BY sort limit 5

Попытался отсортировать записи по количеству вхождений, что та на выходе выдает одну запись вместо пяти

Answer 1

Изменил функцию на поиск по нескольким ключам.

USE Test;

/* DROP FUNCTION fn_SubStringCount; */

DELIMITER //

CREATE FUNCTION fn_SubStringCount(AFullString VARCHAR(1000), ASubString VARCHAR(255), ADilimiter VARCHAR(255))
RETURNS INT
BEGIN
    DECLARE InCount INT;    
    DECLARE aPos INT;
    DECLARE aPosKey INT;
    DECLARE aKey VARCHAR(255);
    DECLARE isExit BIT;
    
    SET isExit = 0;
    SET InCount = 0;
    
    SET aPosKey = LOCATE(ADilimiter, ASubString, 1); 
    SET aKey = CASE WHEN aPosKey > 0 THEN SUBSTRING(ASubString, 1, aPosKey - 1) ELSE ASubString END;
    SET isExit = CASE WHEN aPosKey = 0 THEN 1 ELSE 0 END;
    SET ASubString = SUBSTRING(ASubString, aPosKey + 1, LENGTH(ASubString) - aPosKey);
    
    label1: WHILE aKey IS NOT NULL DO       
        
        SET aPos = LOCATE(aKey, AFullString, 1);
        
        label2: WHILE aPos > 0 DO
            SET InCount = InCount + 1;
            SET aPos = LOCATE(aKey, AFullString, aPos + 1);     
        END WHILE label2;
        
        IF isExit = 1 THEN LEAVE label1; END IF;
                    
        SET aPosKey = LOCATE(ADilimiter, ASubString, 1); 
        SET aKey = CASE WHEN aPosKey > 0 THEN SUBSTRING(ASubString, 1, aPosKey - 1) ELSE ASubString END;
        SET isExit = CASE WHEN aPosKey = 0 THEN 1 ELSE 0 END;
        SET ASubString = SUBSTRING(ASubString, aPosKey + 1, LENGTH(ASubString) - aPosKey);        
                        
    END WHILE label1;

    RETURN InCount;
END;

Пример использования тоже поменял:

USE Test;

CREATE TABLE TextStrings (Str VARCHAR(1000));

INSERT TextStrings (Str) VALUES ('Lorem ipsum dolor sit amet, TEST1 consectetur adipiscing elit,');
INSERT TextStrings (Str) VALUES ('sed do eiusmod TeST1 tempor incididunt ut labore TEST2 et dolore magna aliqua.');
INSERT TextStrings (Str) VALUES ('Ut enim ad TEST3 minim veniam,');
INSERT TextStrings (Str) VALUES ('quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.');
INSERT TextStrings (Str) VALUES ('Duis aute irure TEST1 dolor in reprehenderit TEST3 in voluptate velit esse TEST2 cillum dolore eu fugiat nulla TEST pariatur.');
INSERT TextStrings (Str) VALUES ('Excepteur sint occaecat cupidatat non proident, TEST1 sunt in culpa qui TEST2 TEST2 TEST3 officia deserunt mollit anim id est laborum.');

SELECT fn_SubStringCount(ts.Str, 'TEST1|TEST2|TEST3', '|') FROM TextStrings ts;

DROP TABLE TextStrings;

Answer 2

Решил добавить ещё один ответ.

Для подсчёта длины ключей модифицируем предыдущую функцию:

USE Test;

/* DROP FUNCTION fn_SubStringKeyLen; */

DELIMITER //

CREATE FUNCTION fn_SubStringKeyLen(AFullString VARCHAR(1000), ASubString VARCHAR(255), ADilimiter VARCHAR(255))
RETURNS INT
BEGIN
    DECLARE keyLen INT;    
    DECLARE aPos INT;
    DECLARE aPosKey INT;
    DECLARE aKey VARCHAR(255);
    DECLARE isExit BIT;
    
    SET isExit = 0;
    SET keyLen = 0;
    
    SET aPosKey = LOCATE(ADilimiter, ASubString, 1); 
    SET aKey = CASE WHEN aPosKey > 0 THEN SUBSTRING(ASubString, 1, aPosKey - 1) ELSE ASubString END;
    SET isExit = CASE WHEN aPosKey = 0 THEN 1 ELSE 0 END;
    SET ASubString = SUBSTRING(ASubString, aPosKey + 1, LENGTH(ASubString) - aPosKey);
    
    label1: WHILE aKey IS NOT NULL DO       
        
        SET aPos = LOCATE(aKey, AFullString, 1);
        
        label2: WHILE aPos > 0 DO
            SET keyLen = keyLen + LENGTH(aKey);
            SET aPos = LOCATE(aKey, AFullString, aPos + 1);     
        END WHILE label2;
        
        IF isExit = 1 THEN LEAVE label1; END IF;
        
        
        SET aPosKey = LOCATE(ADilimiter, ASubString, 1); 
        SET aKey = CASE WHEN aPosKey > 0 THEN SUBSTRING(ASubString, 1, aPosKey - 1) ELSE ASubString END;
        SET isExit = CASE WHEN aPosKey = 0 THEN 1 ELSE 0 END;
        SET ASubString = SUBSTRING(ASubString, aPosKey + 1, LENGTH(ASubString) - aPosKey);        
            
        
    END WHILE label1;

    RETURN keyLen;
END;

И соответственно пример:

USE Test;

CREATE TABLE TextStrings (Str VARCHAR(1000));

INSERT TextStrings (Str) VALUES ('Lorem ipsum dolor sit amet, TEST1 consectetur adipiscing elit,');
INSERT TextStrings (Str) VALUES ('sed do eiusmod TeST1 tempor incididunt ut labore TEST2 et dolore magna aliqua.');
INSERT TextStrings (Str) VALUES ('Ut enim ad TEST3 minim veniam,');
INSERT TextStrings (Str) VALUES ('quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.');
INSERT TextStrings (Str) VALUES ('Duis aute irure TEST1 dolor in reprehenderit TEST3 in voluptate velit esse TEST2 cillum dolore eu fugiat nulla TEST pariatur.');
INSERT TextStrings (Str) VALUES ('Excepteur sint occaecat cupidatat non proident, TEST1 sunt in culpa qui TEST2 TEST2 TEST3 officia deserunt mollit anim id est laborum.');

SELECT fn_SubStringKeyLen(ts.Str, 'TEST1|TEST2|TEST3', '|') FROM TextStrings ts;

DROP TABLE TextStrings;

Answer 3

Без комментариев.

USE Test;

DROP FUNCTION fn_SubStringKeyLenUnique;

DELIMITER //

CREATE FUNCTION fn_SubStringKeyLenUnique(AFullString VARCHAR(1000), ASubString VARCHAR(255), ADilimiter VARCHAR(255))
RETURNS INT
BEGIN
    DECLARE keyLen INT;    
    DECLARE aPos INT;
    DECLARE aPosKey INT;
    DECLARE aKey VARCHAR(255);
    DECLARE isExit BIT;
    
    SET NAMES utf8;
    
    CREATE TEMPORARY TABLE tKeys  (vKey VARCHAR(255));
    
    SET isExit = 0;
    SET keyLen = 0;
    
    SET aPosKey = LOCATE(ADilimiter, ASubString, 1); 
    SET aKey = CASE WHEN aPosKey > 0 THEN SUBSTRING(ASubString, 1, aPosKey - 1) ELSE ASubString END;
    SET isExit = CASE WHEN aPosKey = 0 THEN 1 ELSE 0 END;
    SET ASubString = SUBSTRING(ASubString, aPosKey + 1, CHARACTER_LENGTH(ASubString) - aPosKey);
    
    label1: WHILE aKey IS NOT NULL DO       
        
        IF NOT EXISTS(SELECT 1 FROM tKeys WHERE vKey = aKey) THEN
            SET aPos = LOCATE(aKey, AFullString, 1);
            IF aPos > 0 THEN SET keyLen = keyLen + CHARACTER_LENGTH(aKey); END IF;
            INSERT INTO tKeys (vKey) VALUES (aKey);
        END IF;
            
        IF isExit = 1 THEN LEAVE label1; END IF;
        
        SET aPosKey = LOCATE(ADilimiter, ASubString, 1); 
        SET aKey = CASE WHEN aPosKey > 0 THEN SUBSTRING(ASubString, 1, aPosKey - 1) ELSE ASubString END;
        SET isExit = CASE WHEN aPosKey = 0 THEN 1 ELSE 0 END;
        SET ASubString = SUBSTRING(ASubString, aPosKey + 1, CHARACTER_LENGTH(ASubString) - aPosKey);        
            
    END WHILE label1;
    
    DROP TEMPORARY TABLE tKeys;
    
    RETURN keyLen;
END;

БЛОГ НА HUSL

Сортировка по количеству вхождений

Ответы (3 шт):