Summary of SQL syntax not supported by SQLite

1 TOP

This is a frequently asked question, such as the following statements can be used in SQL Server to get the first 10 records in a recordset:

SELECT TOP Ten * from [index] ORDER by IndexID DESC;

However, this SQL statement is not executable in SQLite and should be changed to:

SELECT * FROM [index] ORDER by IndexID DESC limit 0, 10;

Where limit 0,10 means starting from the No. 0 record, reading 10 entries

2 Creating a view ( create view )

SQLite has a bug when creating a multi-table view, with the following issues:

CREATE VIEW Watch_single as SELECT DISTINCT watch_item.[ Watchid],watch_item. [Itemid] from Watch_item;

The above SQL statement will show success after execution, but in fact

SELECT COUNT (*) from [Watch_single] WHERE Watch_ single.watchid = 1;

No other statements can be executed outside of the executable. The reason for this is that the name of the table in which the field is located is specified when the view is established, and SQLite does not recognize it correctly. So the above creation statement should read:

CREATE VIEW Watch_single as SELECT DISTINCT [Watchid],[itemid] from Watch_item;

But the question that comes up is, what if it's a multi-table view with a rename field between the tables?

3 COUNT (DISTINCT column )

SQLite will error when executing the following statement:

SELECT COUNT (DISTINCT watchid) from [Watch_item] WHERE watch_item.watchid = 1;

The reason is that all of SQLite's built-in functions do not support distinct qualification, so there is some trouble if you want to count the number of records that are not duplicated. It is more feasible to first create a view of the record table that is not duplicated, and then count the view.

4 External Connection

Although SQLite has officially claimed that the left OUTER join has been implemented, there is no right OUTER join and full OUTER join. But the actual tests show that it does not seem to work properly. The following three statements will be executed with an error:

SELECT tags. [TagID] From [Tags],[tag_rss] WHERE tags. [TagID] = Tag_rss. [TagID] (*);

SELECT tags. [TagID] From [Tags],[tag_rss] WHERE left OUTER JOIN tag_rss. [TagID] = tags. [TagID];

SELECT tags. [TagID] From [Tags],[tag_rss] WHERE the left JOIN Tag_rss. [TagID] = tags. [TagID];

It is also not feasible to use the + sign instead of the * number after testing.

Original address: http://www.cnblogs.com/goody9807/archive/2012/04/27/2473609.html

Summary of SQL syntax not supported by SQLite