Obviously ORDER BY plays the right role. Under the actual table structure, Absurdly Assured is the last entry, but it ranks at the top of the search results. Hands On record ranking last because O is ranked last in the alphabet in the above list. Obviously, Absolutely is better off before Absurdly by the alphabet. To do this, you need to adopt the 2nd level ORDER BY sorting criteria and sort it in accordance with the 2nd column:
SQL = "SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY
c_lastname, c_firstname"
The result will be sorted first by c_lastname column and then by c_firstname column. If your data table contains more records, careful design and sorting will make the output result arrangement more reasonable.
Put into use
If you like to code yourself like most programmers and indulge in the fanaticism of mastering new technologies. Why not try SQL encoding by turning around from ASP's verbose encoding? Below we will discuss common problems when programming ASP and how to efficiently utilize SQL statements in ASP.
8. Record statistics
It is not difficult to determine how many records are in the database, or how many records meet certain criteria. If you use the correct cursor type, you can use the RecordCount property to obtain the number of records, of course you can also use recordset. However, there is an easier way, which is to use count(*) in your own SELECT statement, the code is as follows:
SQL = "SELECT count(*) FROM Customers"
or
SQL = "SELECT count(*) FROM Customers WHERE c_lastname LIKE 'A%'"
For example, the following code will select some records and the total number of these records:
SQL = "SELECT c_firstname, c_lastname, count(*) FROM Customers WHERE c_lastname LIKE 'A%'"
But you can't achieve your goals. The "count" function used here is actually a collection function, which means that it only returns a single line of information: answering the question you asked. For the first SELECT statement, the question is "How many records are there in the client table?" query returns a single value as response, so it cannot be combined with your regular query. If you want to get other data, you need to use RecordCount.
In addition to "count", the set function also includes AVG, MIN, MAX, SUM, etc.
9. Connect
Anyone who is familiar with SQL and relational databases has encountered a large number of connection types. In simplest way, joining will combine the contents of two tables into a virtual table or recordset. If the data table is effectively normalized, perhaps you will often select specific information from one table and then select related information from another table. This requires a simple "equijoin".
In order to understand the actual connection operation, let us now assume that a certain type of software is stored in a database. A table (Software) contains the name of the software product, the version of the software, and other related details:
Another table (Releases) stores information about the software release history, including release date and release status (such as beta version, current version, outdated, etc.):
The above table also contains a column pointing to the ID number used in the software table. So, through this way of indexing software tables, you will know that the software with software_ID equal to 2 in the publishing table is Rome.
You use joins to combine information so you don't have to go back and forth between the two tables. However, in addition to combining information, the relevant information can also be merged through connection. In this way, as long as the software_ID in the publish table matches the ID in the software table, you will put the matching information into a record together.
The code is as follows:
SQL = "SELECT * FROM Software, Releases WHERE = "
After carefully analyzing the above statements, first notice that the two table names are listed behind the FROM. According to the connections used, you may find that the syntax will change (or the connection type will change), but the above syntax is the most basic and shows the joint selection method of data. The WHERE clause here is used to compare specific ID values. In the Software table, there is an ID column. Similarly, there is a software_ID column in the Releases table. In order to clarify the value you want to compare in the WHERE clause, you use the table name as the prefix and add a dot (.) afterwards.
The following is the result after the connection selects data:
Note: When creating a connection, carefully consider the columns that select data. The above code uses * wildcard characters to make readers pay attention to other parts of the SELECT line of code. However, as you can see from the above image, you cannot select the softwareID column because this column does not have an added value as the recordset part. Its function is to be used in the WHERE clause.
Previous page123Read the full text