是否可以构建以下SQL查询

The original query looks like this (MySQL):

原始查询看起来像这样（MySQL）：

SELECT * 
FROM books 
WHERE title LIKE "%text%" OR description LIKE "%text%" 
ORDER BY date

Would it be possible to rewrite it (without unions or procedures), so that result will look like this:

是否可以重写它（没有工会或程序），因此结果将如下所示：

list of books where title matches query ordered by date, followed by:
标题与按日期排序的查询匹配的书籍列表，后跟：
list of books where description matches query ordered by date
书籍列表，其中描述符合按日期排序的查询

So basically just give a higher priority to matching titles over descriptions.

因此，基本上只是将标题与匹配标题放在一起。

7 个解决方案

#1

In sql server I would do the following:

在sql server中我会做以下事情：

select * from books 
where title like '%text%' or description like '%text%'
order by case when title like '%text%' then 1 else 2 end, date

I'm not sure if you can include columns in ORDER BY in mysql that aren't in the SELECT, but that's the principle I'd use. Otherwise, just include the derived column in the SELECT as well.

我不确定你是否可以在mysql中的ORDER BY中包含不在SELECT中的列，但这是我使用的原则。否则，只需在SELECT中包含派生列。

#2

select * from books 
where title like "%text%" or description like "%text%" 
order by date, case when title like "%text%" then 0 else 1 end

#3

rjk's suggestion is the right way to go. Bear in mind, though, that this query (with or without a union) can't use indexes, so it's not going to scale well. You might want to check out MySQL's fulltext indexing, which will scale better, allow more sophisticated queries, and even help with result ranking.

rjk的建议是正确的方法。但请记住，此查询（带或不带联合）不能使用索引，因此它不能很好地扩展。您可能想要查看MySQL的全文索引，这将更好地扩展，允许更复杂的查询，甚至帮助结果排名。

#4

You could use a case to sort by:

您可以使用案例进行排序：

order by case when title like '%text%' then 0 else 1 end

#5

DECLARE @Books TABLE
(
    [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Title] NVARCHAR(MAX) NOT NULL,
    [Description] NVARCHAR(MAX) NOT NULL,
    [Date] DATETIME NOT NULL
)

INSERT INTO @Books
SELECT 'War and Peace','A Russian Epic','2008-01-01' UNION
SELECT 'Dogs of War','Mercenary Stories','2006-01-01' UNION
SELECT 'World At Arms','A Story of World War Two','2007-01-01' UNION
SELECT 'The B Team','Street Wars','2005-01-01' 

SELECT * FROM
(
    SELECT *, CASE WHEN [Title] LIKE '%war%' THEN 1 WHEN [Description] LIKE '%war%' THEN 2 END AS Ord
    FROM @Books
    WHERE [Title] LIKE '%war%' OR [Description] LIKE '%war%'
) AS Derived
ORDER BY Ord ASC, [Date] ASC

I believe this gives you what you want, but due to the extra workload in the derived CASE statment, it may not have good performance.

我相信这可以为您提供所需的内容，但由于派生CASE语句中的额外工作量，它可能没有良好的性能。

#6

How about something like this...

这样的事情怎么样......

select *  
from books  
where title like "%text%"  
or description like "%text%"  
order by case when title like "%text%" then 1 else 0 end desc, date

#7

-2

The union command will help you. Something along these lines:

union命令会帮助你。这些方面的东西：

SELECT *, 1 as order from books where title like '%text%'
union
SELECT *, 2 as order from books where description like '%text%'
ORDER BY order, date

#1