Geek asks and answers

How to add a new column in a view in sqlite?

I have this database in sqlite (table1):

+-----+-------+-------+
| _id | name  | level |
+-----+-------+-------+
| 1   | Mike  | 3     |
| 2   | John  | 2     |
| 3   | Bob   | 2     |
| 4   | David | 1     |
| 5   | Tom   | 2     |
+-----+-------+-------+

I want to create a view with all the elements of level 2, and then add a new column indicating the row order in the new table. That is, I would like to get this result:

+-------+------+
| index | name |
+-------+------+
| 1     | John |
| 2     | Bob  |
| 3     | Tom  |
+-------+------+

I tried:

CREATE VIEW words AS SELECT _id as index, name FROM table1;

But then I get:

+-------+------+
| index | name |
+-------+------+
| 2     | John |
| 3     | Bob  |
| 5     | Tom  |
+-------+------+

I suppose this should be something like:

CREATE VIEW words AS SELECT XXXX as index, name FROM table 1;

What should i use instead ? XXXX

+5
source share
2 answers

When ordering, the _idnumber of lines before and including this coincides with the number of lines where the value is _idless than or equal to this line _id:

CREATE VIEW words AS
  SELECT (SELECT COUNT(*)
          FROM table1 b
          WHERE level = 2
            AND b._id <= a._id) AS "index",
         name
  FROM table1 a
  WHERE level = 2;

(The calculation itself does not actually require it ORDER BY _id, because the order of the lines doesn't matter when we just count them.)

, words ; ORDER BY "index", .

, , .

+1

. -, :

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

-, , , , , :

ALTER TABLE {tableName} RENAME TO TempOldTable;

:

CREATE TABLE {tableName} (name TEXT, COLNew {type} DEFAULT {defaultValue}, qty INTEGER, rate REAL);

:

INSERT INTO {tableName} (name, qty, rate) SELECT name, qty, rate FROM TempOldTable;

:

DROP TABLE TempOldTable;

, , .

-2

More articles:


All Articles