“如果,那么,否则”在SQLite中 [英] "if, then, else" in SQLite
问题描述
在不使用自定义函数的情况下,SQLite中可以执行以下操作。我有两个表,这些表通过公共ID编号链接。在第二个表中,有两个变量。我想做的是能够返回一个结果列表,包括:行ID,如果这两个变量的所有实例(并且可能有两个以上)的所有实例均为NULL,则返回NULL,如果全部为0,则返回1。如果一个或多个是1,则为2。
Without using custom functions, is it possible in SQLite to do the following. I have two tables, which are linked via common id numbers. In the second table, there are two variables. What I would like to do is be able to return a list of results, consisting of: the row id, and NULL if all instances of those two variables (and there may be more than two) are NULL, 1 if they are all 0 and 2 if one or more is 1.
我现在所拥有的如下:
SELECT
a.aid,
(SELECT count(*) from W3S19 b WHERE a.aid=b.aid) as num,
(SELECT count(*) FROM W3S19 c WHERE a.aid=c.aid AND H110 IS NULL AND H112 IS NULL) as num_null,
(SELECT count(*) FROM W3S19 d WHERE a.aid=d.aid AND (H110=1 or H112=1)) AS num_yes
FROM W3 a
这需要按以下步骤逐步检查每个结果(粗糙的Python伪代码):
So what this requires is to step through each result as follows (rough Python pseudocode):
if row['num_yes'] > 0:
out[aid] = 2
elif row['num_null'] == row['num']:
out[aid] = 'NULL'
else:
out[aid] = 1
有没有更简单的方法?谢谢!
Is there an easier way? Thanks!
推荐答案
使用 CASE ...... c时,例如
CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
从 SQLite语法手册了解更多信息(转到案例表达部分。
Read more from SQLite syntax manual (go to section "The CASE expression").
这篇关于“如果,那么,否则”在SQLite中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!