选择数据库中数字上彼此接近的条目 [英] Selecting entries that are numerically close to each other in a database

问题描述

让我们说我在MS-Access数据库中有一个名为ABC的表.

Lets say I have a table called ABC in a MS-Access Database.

此表中有几列，但此问题仅涉及两列-"Hugo_symbol"和"Start_position". "Hugo_Symbol"具有基因名称，几行可以具有相同的Hugo_symbol-意味着此列具有重复的条目. 开始位置"有数字-介于1000到100000000之间.

There are several columns in this table but only two columns are of interest for this question - "Hugo_symbol" and "Start_position". "Hugo_Symbol" has gene names and several lines can have the same Hugo_symbol - meaning this column has duplicate entries. "Start_position" has numbers - anything from 1000 to 100000000.

我想建立一个查询，该查询返回表ABC中的行 1)具有相同的Hugo_Symbol AND 2)Start_position彼此之间的距离不超过20.

I want to build a query that returns lines from table ABC that 1) Have the same Hugo_Symbol AND 2) Start_position is within 20 of each other.

例如，查询应返回

Hugo_Symbol         Start_Position

TP53                      987654
TP53                      987660
TP53                      987662
APOB                      12345
APOB                      12350
APOB                      12359

因为这些行具有相同的Hugo_Symbol，并且Start_Position相距20以内.

because these lines have the same Hugo_Symbol and Start_Position is within 20 of each other.

这样的查询可能吗?如果是这样，SQL代码将是什么?

Is such a query possible? If so, what would the SQL code be?

推荐答案

我不使用Access，但这是我使用ANSI SQL的方式.

I don't use Access, but this is how I'd approach it with ANSI SQL.

SELECT
  *
FROM
  ABC    AS first
INNER JOIN
  ABC    AS second
    ON  second.Hugo_Symbol     = first.Hugo_Symbol
    AND second.Start_Position <= first.Start_Position + 20
    AND second.Start_Position >  first.Start_Position

这可能会返回您期望的更多数据，并可能返回您期望的其他格式.

This will potentially return more data that you expect, and potentially a different format that you expect.

First.Hugo_Symbol First.Start_Position Second.Hugo_Symbol Second.Start_Position
     TP53              987654                TP53              987660
     TP53              987654                TP53              987662
     TP53              987660                TP53              987662
     APOB              12345                 APOB              12350
     APOB              12350                 APOB              12359
     APOB              12350                 APOB              12359

上面的答案对彼此"有很大的影响.

The answer above is highly influence with "Each Other".

如果将要求改写为存在具有相同符号的另一个记录，并且该位置具有自己位置的20个记录的所有记录"，您将得到类似...的信息.

If you reform the requirements as "all records where another record exists with the same symbol and a position with 20 of it's own position" you could get something like...

SELECT
  *
FROM
  ABC     AS data
WHERE
  EXISTS (SELECT *
            FROM ABC AS lookup
           WHERE lookup.hugo_symbol     = data.hugo_symbol
             AND lookup.start_position >= data.start_position - 20
             AND lookup.start_position <= data.start_position + 20
             AND lookup.start_position <> data.start_position
         )

但是Access2000受到我通常使用的数据库的限制.我不知道Access2000可以做什么，不能做什么.

But Access2000 is more limitted that the databases I normally use. I don't know what Access2000 can and can't do.

这篇关于选择数据库中数字上彼此接近的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！