Summary of SQL syntax not supported by SQLite

Source: Internet
Author: User
Tags 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.