主页 / 问题 / 1436098
Accepted
Дмитрий Варзанов
Asked:2022-08-04 18:00:46 +0800 CST2022-08-04 18:00:46 +0800 CST

按出现次数排序

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

我尝试按出现次数对记录进行排序，输出给出一条记录而不是五条

在此处输入图像描述

在此处输入图像描述

在此处输入图像描述

sql mysql

3 个回答

  1. Best Answer

    将功能更改为通过多个键进行搜索。

    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;
    • 1

  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;
    • 1

  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;
    • 1

相关问题