Sqlite3列分区(Sqlite3 column division)

Sqlite3列分区(Sqlite3 column division)

我在sqlite3 db文件中有以下列（FirstCol，SecondCol，ThirdCol）：

1 Inside 100 1 Outside 200 2 Inside 46 2 Outside 68

第一列的类型为INT，第二列的类型为TEXT，第三列的类型为INT。

对于每个FirstCol值（在这种情况下只有1和2），我需要获得与Outside / Inside相关的值的结果，即200/100，其中FirstCol = 1，68/46，其中FirstCol = 2。

我不介意这是通过单个查询还是通过创建新表来完成的，我只需要该结果。

谢谢。

I have the following columns (FirstCol, SecondCol, ThirdCol) in a sqlite3 db file:

1 Inside 100 1 Outside 200 2 Inside 46 2 Outside 68

First column has type INT, second has type TEXT and third one has type INT.

For each FirstCol value (in this case just 1 and 2) i need to obtain the result of the value associated with Outside/Inside, which is to say 200/100 where FirstCol=1 and 68/46 where FirstCol=2.

I don't mind whether this is done with a single query or by creating a new table, i just need that result.

Thanks.

最满意答案

您必须使用相关子查询查找不同行的值：

SELECT FirstCol, (SELECT ThirdCol FROM MyTable WHERE FirstCol = T.FirstCol AND SecondCol = 'Outside' ) / (SELECT ThirdCol FROM MyTable WHERE FirstCol = T.FirstCol AND SecondCol = 'Inside' ) AS Result FROM (SELECT DISTINCT FirstCol FROM MyTable) AS T;

You have to look up the values from different rows with correlated subqueries:

SELECT FirstCol, (SELECT ThirdCol FROM MyTable WHERE FirstCol = T.FirstCol AND SecondCol = 'Outside' ) / (SELECT ThirdCol FROM MyTable WHERE FirstCol = T.FirstCol AND SecondCol = 'Inside' ) AS Result FROM (SELECT DISTINCT FirstCol FROM MyTable) AS T;