英文:
Is there a way to select integers that are distinct with a margin in SQLite?
问题
我有一个包含三列的表：id，name和value。我想选择所有具有不同值且没有其他行具有值在例如 3 范围内的行。所以，如果行中的值是：
14, 15, 15, 17, 20, 23, 23, 23, 24, 29, 33, 32, 32, 33, 38, 38, 43
那么我想要获得值为 20、29 和 43 的行。
英文:
I have a table with three columns: id, name, and value. I want to select all the rows that have a distinct value and no other row has a value within a range of for instance 3. So if the values in the rows are:
14, 15, 15, 17, 20, 23, 23, 23, 24, 29, 33, 32, 32, 33, 38, 38, 43
Then I want to get back the rows with the values 20, 29, and 43.
答案1
得分: 1
只需翻译代码部分：
select id, value, ...
from thetable t1
where not exists (
select t2.value
from thetable t2
where abs(t2.value - t1.value) < 3
and t1.id <> t2.id)
英文:
Assuming your table having the following structure
| id | value | ... |
|---|---|---|
| 1 | 14 | ... |
| 2 | 15 | ... |
| ... | ... | ... |
You can do as follows
select id, value, ...
from thetable t1
where not exists (
select t2.value
from thetable t2
where abs(t2.value - t1.value) < 3
and t1.id <> t2.id)
Ie select all rows from the table, where there is no other row (ie with a different id) in which the difference of value is less then 3.
You need abs(...) because otherwise comparing for instance 43 from t1 to 20 from t2 will give you -9 which is of course < 3 and thus it will wrongly remove 43 from the result. And checking the different id is also important, because otherwise, it will at some point compare 20 from t1 to 20 from t2 which gives a difference of 0 and thus, it will be wrongly removed form the result also.
See also this fiddle
答案2
得分: 1
对于SQLite 3.28.0及更高版本，您可以使用COUNT()窗口函数，将RANGE帧类型调整为您的条件：
WITH cte AS (
SELECT *,
COUNT(*) OVER (
ORDER BY value
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) AS cnt
FROM tablename
)
SELECT id, name, value FROM cte WHERE cnt = 1;
请参见演示。
英文:
For SQLite 3.28.0+ you can use COUNT() window function with the RANGE frame type adjusted to your condition:
WITH cte AS (
SELECT *,
COUNT(*) OVER (
ORDER BY value
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) AS cnt
FROM tablename
)
SELECT id, name, value FROM cte WHERE cnt = 1;
See the demo.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论