Dynamic Network Forum Code Analysis--Nested query

Dynamic Network Forum in the Dynamic Network forum, users login, the Forum menu will be more than two links, namely: "I published the theme" and "I participate in the theme." The former query is simple, only need to query the user posted to the topic on the line, can be written as "Select theme posts table where Author = login user name" on the line, but the latter is a bit more troublesome. Here, if the theme and reply stickers are in a datasheet, each record is separated by one field is the subject and reply paste, I did so. We know, usually, when the release of the reply, can omit the title, if we search the same as the previous one, the result of the search results are not the title of this item, so it can not be displayed, so you must show the reply to the corresponding post topic. How is this query to be done?


Let's take a look at the code of the Moving net:





"Select the" topic where TopicID in (select the Rootid from "&NowUseBBS& where postuserid=" &am p;userid& ' ORDER BY Announceid Desc ' ORDER BY topicid Desc '





Why do you have parentheses in this query? What is the meaning? And listen to the next decomposition.





The queries we use generally are single level queries, the above query is a two-tier, which requires the server to process the final query before the results of a result, and then according to the current query knot to further continue the following query work, that is, the first implementation of the parentheses in the query (we call it a subquery), The server then starts executing the outside query and returns the correct result, which is called a nested query. The above query, the first level of inquiry is to check the user posted in this version of all posts (including replies), and then check the theme of these posts.





What are the benefits of nested queries in addition to the above function? You can use nested queries if you require statistical functions to be used in search matching criteria, which are often used in profiling statistics. For example, now ask to inquire all sales volume larger than the average sales of the code, if written in the following query:





SELECT title_id,qty//title_id for ISBN, Qty for sales


from Sales


Where qty>avg (qty)//avg is the mean function, and AVG (qty) is the average sales




The
server will prompt for an error because it is not allowed to use statistical functions directly on the data in this select list in the WHERE clause. This problem can be resolved by using nested queries in the WHERE clause, as follows:





SELECT Title_id,qty


from Sales


WHERE qty>


(SELECT avg (qty) from sales)