mysql - 如何使用成功计数对我的表进行排序，例如在行中执行的语句

Question

我想要一个查询，根据某个短语在一行中出现的次数对我的表进行排序。

像这样:

select * from table where paragraph like '%im%' order by (count of success like statement execute in row) 

_________________ 
| paragraph     | 
_________________ 
|hello im vahid| 

| and im happy | with 2 (im) 
_________________ 

在这种情况下结果是

_________________ 
| paragraph     | 
_________________ 
|hello im vahid | 

|and im happy   | 
_________________ 
|im vahid glad  | 

|to meet u      | 
_________________

---

是的Gerrat 说得对， 每条包含字母“im”(在段落列中)的记录按每个段落包含字母“im”的次数降序排列

$trimmed1= "my paragraph";
$pieces = explode(" ", trim($trimmed1));

$query = "SELECT * FROM agahi
WHERE active='2' AND  (  ";
$countp = count($pieces);

for ($i = 0; $i < $countp-1; $i++) {

$query .="  CONCAT(sharh,' ',onvan,' ',kelid) LIKE '%".$pieces[$i]."%' OR ";
}
$query .=" CONCAT(sharh,' ',onvan,' ',kelid) LIKE '%".$pieces[$i]."%'  " ;
$query .= ")  

ORDER BY (";

for ($i = 0; $i < $countp-1; $i++) {
$query .=
"(
CASE WHEN  CONCAT(sharh,' ',onvan,' ',kelid) LIKE  '%".$piecesf[$i]."%'
THEN 1 
ELSE 0 
END
) + ";}

$query .= "(CASE WHEN  CONCAT(sharh,' ',onvan,' ',kelid) LIKE  '%".$piecesf[$i]."%'
THEN 1 
ELSE 0 
END )
) DESC ";

这是我的代码，但 ORDER BY 不起作用

这是生成的代码:

SELECT * FROM agahi WHERE active='2' AND ( CONCAT(sharh,' ',onvan,' ',kelid) LIKE '%cd%' OR CONCAT(sharh,' ',onvan,' ',kelid) LIKE '%dvd%' ) 

ORDER BY (
( CASE WHEN CONCAT(sharh,' ',onvan,' ',kelid) LIKE '%cd%' THEN 1 ELSE 0 END ) 
+ ( CASE WHEN CONCAT(sharh,' ',onvan,' ',kelid) LIKE '%dvd%' THEN 1 ELSE 0 END ) 
) DESC

Answer 1

select * 
from table t 
inner join 
  (select tableID, numberToControlSort  
  from [put the rest of a query that calcs a priority on each row here]) s  
on t.tableID=s.tableID 
where [put your where clause here] 
order by s.numberToControlSort

这会起作用。不幸的是，SQLServer 没有常备例程来计算子字符串在字符串中出现的次数。您可以编写/找到一个函数来执行此操作，并将该函数用于 numberToControlSort。