sqlite中的SQL sqlite条件查询（适用于Android）

Hello I have a table with that structure:

你好,我有一个具有该结构的表:

ID      VALUE
1         3
2         5
3        12

if I query select id,value,value+5 from table. Thats the result

如果我从表中查询选择id,value,value + 5。多数民众赞成的结果

ID      VALUE    NEW_VALUE
1         3         8
2         5         10
3        12         17

And what I want to make a query indicating the id and the new value that return the whole table but with a 3rd column indicating the new values after inserting. for example for myQuery(id=2,value=8)

我想做一个查询,指出返回整个表的id和新值,但第3列指示插入后的新值。例如,对于myQuery(id = 2,value = 8)

ID      VALUE    NEW_VALUE
1         3         3
2         5         8
3        12         12

Is posible to do that in the same query?

可以在同一个查询中执行此操作吗?

1 个解决方案

#1

YOu can use the WHERE clause to select only the rows you want ("...if the student has the given id..."):

你可以使用WHERE子句只选择你想要的行(“......如果学生有给定的id ......”):

             update T
             set col3 = col2 + 5
             where id = 2

Of course, col3 would have to exist before you can update it. So you will either have to issue an ALTER-TABLE statement (if your implementation supports it) or recreate the table with the desired columns, import the original data (INSERT INTO YOURNEWTABLE...SELECT ... from YOUROLDTABLE) and then update col3.

当然,col3必须存在才能更新它。因此,您必须发出ALTER-TABLE语句(如果您的实现支持它)或重新创建具有所需列的表,导入原始数据(INSERT INTO YOURNEWTABLE ... SELECT ... from YOUROLDTABLE)然后更新col3 。

If you don't want to "persist" this third column but only need it to be displayed when you query:

如果您不想“保留”第三列,但只需要在查询时显示它:

            select id, col2, col2 + 5 as myComputedValue 
            from T
            where id = 2

Finally, if you want to display all rows but change the addend conditionally (add zero to col2 when the id is not one of the ones you desire but add 5 when it is) then you can use the CASE statement.

最后,如果要显示所有行但是有条件地更改加数(当id不是您想要的那个时添加0到col2,但是当它是5时添加5)则可以使用CASE语句。

#1