统计有多少列符合条件

发布于 2024-12-13 19:34:01 字数 340 浏览 0 评论 0原文

我有一个表 [Table]，其中包含 3 列 [Column1]、[Column2] 和 [Column3]。

我的条件是：

WHERE [Column1] LIKE '%q%' OR [Column2] LIKE '%q%' OR [Column3] LIKE '%q%'

它有效，但我想按匹配次数对结果进行排序。

例如

C1 - C2 - C3 
q - q - q
q - a - q
q - a - a

原文

I have a table [Table] with 3 columns [Column1], [Column2] and [Column3].

My condition is:

WHERE [Column1] LIKE '%q%' OR [Column2] LIKE '%q%' OR [Column3] LIKE '%q%'

It works but I'd like to order results by the number of match.

For example

C1 - C2 - C3 
q - q - q
q - a - q
q - a - a

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

记忆で 2024-12-20 19:34:01

在查询中尝试使用此 ORDER BY 子句：

ORDER BY (CASE WHEN [Column1] LIKE '%q%' THEN 1 ELSE 0 END +
          CASE WHEN [Column2] LIKE '%q%' THEN 1 ELSE 0 END +
          CASE WHEN [Column3] LIKE '%q%' THEN 1 ELSE 0 END) DESC

Try this ORDER BY clause on your query:

ORDER BY (CASE WHEN [Column1] LIKE '%q%' THEN 1 ELSE 0 END +
          CASE WHEN [Column2] LIKE '%q%' THEN 1 ELSE 0 END +
          CASE WHEN [Column3] LIKE '%q%' THEN 1 ELSE 0 END) DESC

回复收藏 0 原文

灯角 2024-12-20 19:34:01

SELECT [Column1], [Column2], [Column3] FROM [Table]
WHERE [Column1] LIKE '%q%' OR [Column2] LIKE '%q%' OR [Column3] LIKE '%q%'
ORDER BY 
    (CASE WHEN [Column1] LIKE '%q%' THEN 1 ELSE 0 END) +
    (CASE WHEN [Column2] LIKE '%q%' THEN 1 ELSE 0 END) +
    (CASE WHEN [Column3] LIKE '%q%' THEN 1 ELSE 0 END)
    DESC

SELECT [Column1], [Column2], [Column3] FROM [Table]
WHERE [Column1] LIKE '%q%' OR [Column2] LIKE '%q%' OR [Column3] LIKE '%q%'
ORDER BY 
    (CASE WHEN [Column1] LIKE '%q%' THEN 1 ELSE 0 END) +
    (CASE WHEN [Column2] LIKE '%q%' THEN 1 ELSE 0 END) +
    (CASE WHEN [Column3] LIKE '%q%' THEN 1 ELSE 0 END)
    DESC

回复收藏 0 原文

何以畏孤独 2024-12-20 19:34:01

您可能应该添加第四列，与此类似：

SELECT [Col1],[Col2],[Col3], 
   CAST([Col1] LIKE '%q%' AS SMALLINT) + CAST([Col2] LIKE '%q%' AS SMALLINT) +
   CAST([Col3] LIKE '%q%' AS SMALLINT) AS Strength
....
ORDER BY Strength

由于我不知道您正在使用哪个数据库，因此您可能需要查阅文档以了解如何从布尔值转换为整数。

伊泰

You should probably add a fourth column, similar to this:

SELECT [Col1],[Col2],[Col3], 
   CAST([Col1] LIKE '%q%' AS SMALLINT) + CAST([Col2] LIKE '%q%' AS SMALLINT) +
   CAST([Col3] LIKE '%q%' AS SMALLINT) AS Strength
....
ORDER BY Strength

Since I don't know which database you're using, you may need to consult the documentation to see how to cast from a boolean value to an integer.

Itay

回复收藏 0 原文

~没有更多了~