SQLite SELECT ...按“最佳"排序匹配 [英] SQLite SELECT...ordering by "best" match

问题描述

此处的上下文是 Android 应用程序中的 SQLCipher 数据库.这里的上下文是 Android 应用程序中的 SQLCipher 数据库.这里的上下文是 Android 应用程序中的 SQLCipher 数据库.考虑以下 SQLite 表

The context here is a SQLCipher database in an Android app. The context here is a SQLCipher database in an Android app. The context here is a SQLCipher database in an Android app. Consider the following SQLite table

CREATE TABLE IF NOT EXISTS test(a INTEGER NOT NULL,b INTEGER NOT NULL,c INTEGER NOT NULL,d INTEGER NOT NULL);

我在其中插入以下行

INSERT INTO test (a,b,c,d) VALUES(1,2,3,4);
INSERT INTO test (a,b,c,d) VALUES(1,2,3,5);
INSERT INTO test (a,b,c,d) VALUES(1,2,5,5);
INSERT INTO test (a,b,c,d) VALUES(1,5,5,5);
INSERT INTO test (a,b,c,d) VALUES(5,5,5,5);

一个简单的 SELECT COUNT(*) FROM test WHERE a = 1 OR b = 2 or c = 3 or d = 4; 将返回 4 作为结果，因为5 行中有 4 行具有一个或多个匹配值.我需要做的是找到最佳"匹配，即满足最多 WHERE .. OR 条件的匹配.在本例中，这将返回所有四列 匹配的第一行.虽然我可以沿着 SELECT * FROM test WHERE a = 1 OR b = 2 or c = 3 or d = 4; 的行简单地将光标返回到原始选择上，然后在Java 我想知道是否没有办法直接在 SQLite 内部执行此操作.

A simple SELECT COUNT(*) FROM test WHERE a = 1 OR b = 2 or c = 3 or d = 4; would return 4 as the result since 4 of the 5 rows have one or more matching values. What I need to do is to find the "best" match, i.e. the one that satisfies the most WHERE .. OR conditions. In the present instance this would return the first row where all four columns match. While I could simply get a cursor back on a raw select along the lines of SELECT * FROM test WHERE a = 1 OR b = 2 or c = 3 or d = 4; and then do the rest in Java I am wondering if there isn't a way to do this directly within SQLite itself.

推荐答案

使用表达式:

(a=1) + (b=2) + (c=3) + (d=4)

在 ORDER BY 子句中:

SELECT *
FROM test
ORDER BY (a=1) + (b=2) + (c=3) + (d=4) DESC
LIMIT 1

每个术语:(a=1)、(b=2)、(c=3) 和 (d=4) 计算结果为 1 表示 TRUE 或 0 表示 FALSE.
请参阅演示.
如果你想在结果中使用 CTE:

Each of the terms: (a=1), (b=2), (c=3) and (d=4) evaluates to 1 for TRUE or 0 for FALSE.
See the demo.
If you want ties in the results use a CTE:

WITH cte AS (
  SELECT *, (a=1) + (b=2) + (c=3) + (d=4) counter
  FROM test
)
SELECT a, b, c, d 
FROM cte
WHERE counter = (SELECT MAX(counter) FROM cte)

请参阅演示.
或者用 RANK() 窗口函数:

WITH cte AS (
  SELECT *, RANK() OVER (ORDER BY (a=1) + (b=2) + (c=3) + (d=4) DESC) rn
  FROM test
)
SELECT a, b, c, d 
FROM cte
WHERE rn = 1

请参阅演示.
结果:

See the demo.
Results:

| a   | b   | c   | d   |
| --- | --- | --- | --- |
| 1   | 2   | 3   | 4   |

这篇关于SQLite SELECT ...按“最佳"排序匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！