mySQL中关于排序的问题

这是一个搜索功能
例：
输入搜索内容：宝宝的宝爸
进行分词过后就是：宝宝的宝爸，宝宝，宝爸
排列顺序优先级：
1.包含宝宝的宝爸
2.同时包含宝宝和宝爸
3.包含宝宝或者宝爸

单次查询，按优先级进行排序，并且单个级别内按时间倒序排列

select id from mcloud_social
WHERE
(
title LIKE '%宝宝的宝爸%'
OR text LIKE '%宝宝的宝爸%'
OR (
title LIKE '%宝宝%'
AND title LIKE '%宝爸%'
)
OR (
text LIKE '%宝宝%'
AND text LIKE '%宝爸%'
)
OR title LIKE '%宝宝%'
OR text LIKE '%宝宝%'
OR title LIKE '%宝爸%'
OR text LIKE '%宝爸%'
)
AND isdeleted = 0

ORDER BY create_date DESC

在order by前面应该有一个条件限定，求个大神帮忙写一下

我前面的sql语句可能也有问，也可以不用看我下面写的，只用看需求写一个sql出来

用case when 的按你的条件排出1，2，3的顺序，然后再order by 排序。比如case when title like '%宝宝的宝爸%' or text like '%宝宝的宝爸%' then 1 when '' then 2

CREATE TABLE fa(
id INT ,
NAME VARCHAR(20),
Cre_DATE DATETIME

)

INSERT INTO fa VALUES
(1,'宝宝的宝爸',dateadd(hour,1,getdate())),
(2,'宝宝宝爸',DATEADD(hour,3,GETDATE())),
(3,'宝宝宝爸的',DATEADD(hour,5,GETDATE())),
(4,'宝宝的',DATEADD(hour,23,GETDATE())),
(5,'宝宝dsew',DATEADD(hour,12,GETDATE())),
(6,'宝爸',DATEADD(hour,17,GETDATE())),
(7,'宝宝的宝爸sdawe',DATEADD(hour,21,GETDATE())),
(8,'的宝宝的宝爸dew',DATEADD(hour,34,GETDATE()))

--SELECT * FROM fa

DECLARE @A VARCHAR(20),@B VARCHAR(20),@C VARCHAR(20)
SET @A='宝宝的宝爸' set @B='宝宝' set @C='宝爸';
WITH tab AS(
SELECT id,NAME ,cre_date,3 AS num FROM fa
WHERE NAME LIKE '%'+@A+'%'
UNION ALL
SELECT id,NAME ,cre_date ,2 AS num FROM fa
WHERE (NAME LIKE '%'+@B+'%' AND NAME LIKE '%'+@C+'%')
AND (NAME LIKE '%'+@B+'%' AND NAME LIKE '%'+@C+'%' AND NAME NOT LIKE '%'+@A+'%')
UNION ALL
SELECT id,NAME ,cre_date ,1 AS num FROM fa
WHERE ((NAME LIKE '%'+@B+'%' AND NAME not LIKE '%'+@A+'%')
and (NAME LIKE '%'+@B+'%' AND NAME not LIKE '%'+@C+'%'))
or ((NAME LIKE '%'+@C+'%' AND NAME not LIKE '%'+@A+'%')
and (NAME LIKE '%'+@C+'%' AND NAME not LIKE '%'+@B+'%'))
)
SELECT id,name,cre_date FROM tab
ORDER BY num desc, cre_date DESC

根据你的要求写的j脚本，个人认为应该就是这样。但是仅供参考哈

可以考虑 UNION

SELECT * FROM tableName where 条件
UNION (SELECT * FROM tableName where 条件 )
UNION (SELECT * FROM tableName where 条件 );