Summary of SQL syntax not supported by SQLite

1 top

This is a frequently asked question, for exampleSqlserverYou can use the following statement to obtain the first 10 records in the record set:

Select top 10 * from [Index] order by indexid DESC;

 

HoweverSQLStatement inSQLiteCannot be executed, should be changed:

Select * from [Index] order by indexid DESC limit 0, 10;

 

WhereLimit 0, 10Indicates0Records start and are read later.10Entries

 

2Create a view (Create View)

SQLiteWhen creating a multi-Table view, there isBug, The problem is as follows:

Create view watch_single as select distinctWatch_item. [watchid], watch_item. [Itemid]From watch_item;

 

The aboveSQLAfter the statement is executed, it is displayed as successful,

Select count (*) from [watch_single] Where watch _ single. watchid = 1;

Other statements cannot be executed. The reason is that the table name of the field is specified when the view is created.SQLiteIt cannot be recognized correctly. Therefore, the above statement should be changed:

Create view watch_single as select distinct [watchid], [Itemid] From watch_item;

 

However, what should I do if a multi-Table view has a duplicate Name field between tables?

 

3 count(Distinct Column)

SQLiteAn error is reported when the following statement is executed:

Select count (distinct watchid) from [watch_item] Where watch_item.watchid = 1;

The reason is thatSQLiteAll built-in functions are not supported.DistinctTherefore, it may be difficult to count the number of records that are not repeated. It is feasible to create a view of the record table that does not repeat, and then count the view.

 

4External Connection

AlthoughSQLiteOfficially claimedLeft Outer JoinImplemented, but not yetRight Outer JoinAndFull outer join. However, the actual test shows that it does not seem to work properly. When executing the following three statements, an error is reported:

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 left join tag_rss. [tagid] = tags. [tagid];

 

In addition, tested+No.*It is not feasible.