Mysql:WHERE LIKE条件中按优先级排序

Question

我想获得符合以下条件的所有产品1. 产品名称如“decor”2. 列出类别名称为“decor”的类别中的所有产品

这是我的sql查询

param = "decor"
sql = "SELECT DISTINCT
P.ProductName,P.ProductPrice FROM
Products_Joined AS P LEFT JOIN Categories_Products_Link AS CP ON
P.ProductId = CP.ProductId LEFT JOIN Categories ON CP.CategoryID =
Categories.CategoryID where P.ProductName LIKE '%" & param &"%' OR
Categories.CategoryName LIKE '%" & param &"%' order by P.ProductName "

我的查询工作正常，我返回了这样的输出

但我想要的结果是

需要按产品名称订购

我该怎么做？

Answer 1

请创建一个函数

 DROP FUNCTION IF EXISTS PatIndex;
 DELIMITER $$

 CREATE FUNCTION PatIndex(pattern VARCHAR(255), tblString VARCHAR(255)) 
 RETURNS INTEGER
DETERMINISTIC
BEGIN

DECLARE i INTEGER;
SET i = 1;

myloop: WHILE (i <= LENGTH(tblString)) DO

    IF SUBSTRING(tblString, i, 1) REGEXP pattern THEN
        RETURN(i);
        LEAVE myloop;        
    END IF;    

    SET i = i + 1;

   END WHILE; 

   RETURN(0);

   END

然后使用它作为您的排序逻辑

 SELECT DISTINCT
P.ProductName,P.ProductPrice FROM
Products_Joined AS P LEFT JOIN Categories_Products_Link AS CP ON
P.ProductId = CP.ProductId LEFT JOIN Categories ON CP.CategoryID =
Categories.CategoryID where P.ProductName LIKE '%" & param &"%' OR
Categories.CategoryName LIKE '%" & param &"%'                           
 order by   SUBSTRING(P.ProductName ,PATINDEX('[A-z]',P.ProductName ),length(P.ProductName ) - PATINDEX('[A-z]',P.ProductName )+1)