试试这个 ORDER BY 条款：

ORDER BY
    CASE WHEN P.ProductName LIKE 'Decor%' THEN 0 ELSE 1 END,
    P.ProductName;

这将使所有名称以 Decor 首先是其他所有产品。在这两个组中，我们继续按整个产品名称的字母顺序进行排序。
我可以提供以下完整查询：

SELECT
    t.ProductName, t.ProductPrice
FROM
(
    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 c
        ON cp.CategoryID = c.CategoryID
    WHERE
        p.ProductName LIKE '%param%' OR
        c.CategoryName LIKE '%param%'
) t
ORDER BY
    CASE WHEN t.ProductName LIKE 'Decor%' THEN 0 ELSE 1 END,
    t.ProductName;

请创建函数

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)