有无高手指点下搜索结果排序问题:先谢过。如我有一个搜索语句:
Select id,c_name,c_title,c_content from tab_content where c_name like '%关键词%' or c_title like '%关键词%'
如何使搜索结果能优先按c_name对应的id排序呢?
Select id,c_name,c_title,c_content from tab_content where c_name like '%关键词%' or c_title like '%关键词%'
如何使搜索结果能优先按c_name对应的id排序呢?
from tab_content
where c_name like '%关键词%' or c_title like '%关键词%'
order by id
from tab_content
where c_name like '%关键词%' or c_title like '%关键词%'
order by id
--order by id desc --降序
from tab_content
order by case when id=(select id from tab_content where c_name like '%关键词%' or c_title like '%关键词%') then 1 else 0 end ,id试试
from tab_content
where c_name like '%关键词%' or c_title like '%关键词%'
order by id
from tab_content
where c_name like '%关键词%' or c_title like '%关键词%'
order by c_name,id
Select id,c_name,c_title,c_content
from tab_content
order by case when id=(select id from tab_content where c_name like '%关键词%' or c_title like '%关键词%') then 0 else 1 end ,id
或者Select id,c_name,c_title,c_content
from tab_content
order by case when id=(select id from tab_content where c_name like '%关键词%' or c_title like '%关键词%') then 1 else 2 end
感谢这位兄弟的解答,问题是如果这样查询的话会报错:子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。郁闷吖!!
感谢这位兄弟的解答,问题是如果这样查询的话会报错:子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。郁闷吖!!
SQL codeSelect id,c_name,c_title,c_contentfrom tab_contentorderbycasewhen id in (select idfrom tab_contentwhere c_namelike'%关键词%'or c_titlelike'%关键词%')then1else2end
GO
-- 确保Product与ProductReview是一对多的关系
SELECT A.*
FROM Production.ProductReview A
LEFT JOIN Production.Product B ON A.ProductID = B.ProductID AND B.Name = 'HL Mountain Pedal'
ORDER BY B.ProductID DESC
,A.ProductReviewID;SELECT *
FROM Production.ProductReview A
ORDER BY CASE WHEN EXISTS (SELECT * FROM Production.Product B WHERE A.ProductID = B.ProductID AND B.Name = 'HL Mountain Pedal')THEN 0
ELSE 1 END,ProductReviewID;;WITH TEMP
AS
(
SELECT *,0 AS Flag FROM Production.ProductReview A
WHERE EXISTS (SELECT * FROM Production.Product B WHERE A.ProductID = B.ProductID AND B.Name = 'HL Mountain Pedal')
)
SELECT * FROM TEMP
UNION ALL
SELECT *,1 AS Flag FROM Production.ProductReview A
WHERE NOT EXISTS (SELECT * FROM TEMP B WHERE A.ProductID = B.ProductID)
ORDER BY Flag,ProductReviewID;