将 IN 与 SQL 中的元组集一起使用 (SQLite3) [英] Using IN with sets of tuples in SQL (SQLite3)

问题描述

我在 SQLite3 数据库中有下表:

I have the following table in a SQLite3 database:

CREATE TABLE overlap_results (
neighbors_of_annotation varchar(20),
other_annotation varchar(20),
set1_size INTEGER,
set2_size INTEGER,
jaccard REAL,
p_value REAL,
bh_corrected_p_value REAL,
PRIMARY KEY (neighbors_of_annotation, other_annotation)
);

我想执行以下查询:

SELECT * FROM overlap_results WHERE 
(neighbors_of_annotation, other_annotation)
IN (('16070', '8150'), ('16070', '44697'));

也就是说，我有几个注释 ID 元组，我想获取每个元组的记录.sqlite3 提示给了我以下内容错误:

That is, I have a couple of tuples of annotation IDs, and I'd like to fetch records for each of those tuples. The sqlite3 prompt gives me the following error:

SQL error: near ",": syntax error

如何将其正确表达为 SQL 语句?

How do I properly express this as a SQL statement?

编辑 我意识到我没有很好地解释我真正想要的是什么.让我再试试这个.

EDIT I realize I did not explain well what I am really after. Let me try another crack at this.

如果有人给我一个他们感兴趣的 neighbors_of_annotation 中的任意术语列表，我可以编写如下的 SQL 语句:

If a person gives me an arbitrary list of terms in neighbors_of_annotation that they're interested in, I can write a SQL statement like the following:

SELECT * FROM overlap_results WHERE 
neighbors_of_annotation
IN (TERM_1, TERM_2, ..., TERM_N);

但是现在假设这个人想要给我一对术语，如果形式 (TERM_1,1, TERM_1,2), (TERM_2,1, TERM_2,2), ..., (TERM_N,1, TERM_N,2)，其中 TERM_i,1 在 neighbors_of_annotation 和 TERM_i 中，2 在 other_annotation 中.SQL 语言是否提供了一种同样优雅的方式来为感兴趣的对(元组)制定查询?

But now suppose that person wants to give me pairs of terms if the form (TERM_1,1, TERM_1,2), (TERM_2,1, TERM_2,2), ..., (TERM_N,1, TERM_N,2), where TERM_i,1 is in neighbors_of_annotation and TERM_i,2 is in other_annotation. Does the SQL language provide an equally elegant way to formulate the query for pairs (tuples) of interest?

最简单的解决方案似乎是创建一个新表，只为这些对，然后将该表与要查询的表连接起来，只选择第一个术语和第二个术语匹配的行.创建大量 AND/OR 语句看起来很吓人，而且容易出错.

The simplest solution seems to be to create a new table, just for these pairs, and then join that table with the table to be queried, and select only the rows where the first terms and the second terms match. Creating tons of AND / OR statements looks scary and error prone.

推荐答案

我从未见过这样的 SQL.如果它存在，我会怀疑它是一个非标准的扩展.试试:

I've never seen SQL like that. If it exists, I would suspect it's a non-standard extension. Try:

SELECT * FROM overlap_results
WHERE neighbors_of_annotation = '16070'
AND   other_annotation = '8150'
UNION ALL SELECT * FROM overlap_results
WHERE neighbors_of_annotation = '16070'
AND   other_annotation = '44697';

换句话说，从您的元组构建动态查询，但作为一系列联合，或作为 OR 中的一系列 AND:

In other words, build the dynamic query from your tuples but as a series of unions instead, or as a series of ANDs within ORs:

SELECT * FROM overlap_results
WHERE (neighbors_of_annotation = '16070' AND other_annotation =  '8150')
OR    (neighbors_of_annotation = '16070' AND other_annotation = '44697');

所以，而不是代码(伪代码，仅在我的脑海中进行测试，因此调试是您的责任)，例如:

So, instead of code (pseudo-code, tested only in my head so debugging is your responsibility) such as:

query  = "SELECT * FROM overlap_results"
query += " WHERE (neighbors_of_annotation, other_annotation) IN ("
sep = ""
for element in list:
    query += sep + "('" + element.noa + "','" + element.oa + "')"
    sep = ","
query += ");"

你会得到类似的东西:

query  = "SELECT * FROM overlap_results "
sep = "WHERE "
for element in list:
    query += sep + "(neighbors_of_annotation = '" + element.noa + "'"
    query += " AND other_annotation = '" + element.oa + "')"
    sep = "OR "
query += ";"

这篇关于将 IN 与 SQL 中的元组集一起使用 (SQLite3)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！