Label:Execute SQL statement: SELECT * FROM ( SELECT * from tab where id>20 order by UserID Desc As a order by date desc Logically looking pretty right but error: The ORDER by clause is not valid in views, inline functions, derived tables, subqueries, and common table expressions unless you specify also TOP or for XML. As long as we add in the nested subqueries view: Top percent SELECT * FROM ( Select Top Percent * from Tab where id>20 order by UserI
Briefly:In the query statement contains another query statement, the contained query is called a subquery, the query containing the subquery is called the parent query.In summary, a subquery is one or more query statements nested within a query statement.
Common sub-query categories:One, independent sub-queryFeatures: S
1. subquery in SQL:
When we are doing SQL questions, we are most afraid of complex query statements, because most of the time we need two or three subqueries for implementation,
Most of the students are afraid of subqueries. I was confused by these questions,
However, after doing a lot of such questions, we found that there are also some rules. We can sum up a se
cannot generate connection record (extra record)
SelectE1.ename, E2.ename fromEMP E1 Left JoinEMP E2 on(E1.mgr=e2.empno); --full external link, the left and right side of the record can not generate connections to query out
SelectENAME, Dname fromEMP E Full JoinDept D on(E.deptno=D.deptno); --From the connection, the same table will be different aliases, and then as two tables to use. The self-connection can be connected either inside or outside.
SelectE1.ename,e2.ename fromEMP E1JoinEMP E2 on
Use [erp2015]go/****** object:storedprocedure [dbo]. [GetUser] Script date:03/14/2015 13:27:04 ******/set ansi_nulls ongoset quoted_identifier ongo--=============================== ==============--author:wangyanling--Create date:205-03-12--Description: Get customer information--================================ =============alter PROCEDURE [dbo]. [GetUser]--ADD the parameters for the stored procedure here@uname varchar ($) asbegin--SET NOCOUNT on added to prevent Extra result sets from--inter
Tags: conditional msi order top sum end tar Mon oneResult set of the first query Select* from(
SelectC.msname,a.msid,c.msprice, C.msprice*count (A.MSID) asTotalmoney,sum (A.msamount) asTotalCount fromBillconsume a RIGHT join (SelectTop1Mstime asStarttime,getdate () asEndTime fromBillconsumewhereDateDiff (Dd,mstime,getdate ()) =0ORDER BY mstime ASC) b on a.mstime between B.starttime and B.endtime and a.msid!=0Left join Menus C on C.msid=a.msid GROUP by A.msid, C.msname,c.msprice) s A statement th
* Query two columns of data from two different tablesMake contact with the same column in the two tables through a subqueryExample: Query all students for Sname, CNO, and degree columns.Select (select Sname from Student where Student.sno=score.sno), Cno,degree from score--where sname is in the Student table, CNO and degree in the score table, where two tables have Student.sno=score.sno connections-- use a table with more data to query the main table, where score is the main tableSecond, aggrega
Tags: SQL data performance Oracle--=========================--sql Base--subquery--=========================One, sub-queryA subquery is a query that is inside a select, UPDATE, or DELETE statementIi. classification of sub-queriesSingle-line subqueryReturns 0 rows or one rowMulti-row subqueriesReturn one or more rowsDole
Renyuan--second pageSelect Top 5 * from Renyuan where code not in (select Top 5 code from Renyuan)--Page ThreeSelect Top 5 * from Renyuan where code not in (select top of code from Renyuan)--a total of several pages????Select CEILING (COUNT (*)/5.0) from Renyuan--Find out all information about people older than 35 years in the sales departmentSELECT * from Renyuan where code in(select code from Renyuan where age>35 and bc=(select Bcode from bumen where bname= ' sales Department '))--View all pe
A subquery is the embedding of a SQL Sever statement into another SQL Sever statement. The database engine performs a query operation on a subquery as a virtual table. Subqueries can be used as a table in a join statement, either as a value in a SELECT statement, as a SQL se
Ask! When mysql returns a fixed result set, whether the result set is repeatedly executed, for example, 1: selectt. *, (selectcount (1) fromtable01) fromtable01t; for example, 2: selectt. *, (selectcount (1) fromtable01t2wheret2. keyt1.key) fromtable01t1; -- Nested subqueries like this will be executed repeatedly. Q: In the subquery traversal process in example 1 above, there are fixed result sets. Will the result be repeated for N times (N records )?
There are two tables, A and B.
Aid scriptid taskid yearID
1 5 3 1
2 5 4 1
3 5 5 1
4 6 3 1
5 6 4 1
6 7 3 2
7 7 5 2
8 8 2 2
9 8 6 2
Table B is as follows:Bid aid11 112 213 314 415 516 617 718 819 9
Obtain the maximum value of yearID = 1, scriptid = 5, and taskid not greater than 4 in table.
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->
Select
Scriptid,
Max
(Taskid)
As
Taskid
From
A
Where
Ybid
=
1
And
Scripti
Dynamic
Recently looked back at the SQL Server books, mainly on the SQL related subqueries and crosstab knowledge.
The difference between a correlated subquery and a normal subquery is that the related subquery references the columns of the outer query.
This ability to refer
Label:SELECT Info_key, Info_value, Info_status, Edit_timeF ROM (SELECT * from ' detail ' WHERE login = ' [e-mail protected] ' ORDER by Edit_time DESC) as AAAGROUP by Info_key Hope to realize the sub-query based on alias by Thinkphp, and finally refer to the following article to realize. Http://www.thinkphp.cn/update/122.html The following is the original text in the Help manual. (Search sub-query) New subquery support has been added since version
belong to any of the list in the back of not, and the query does not return any values, unlike the expected result, the reason is that the non in clause is handling null values in SQL Server, the not as shown in Figure 2 The IN clause can actually be converted to a query as shown in 3. Figure 3: Equivalent conversions are possible for the NOT in clause In Figure 3, you can see that not in can be converted to a condition for each value of the unequa
Tags: problem SP c new SQL table BS r ROMIn a subquery, if you want to implement the following functions:Select Lib,count (*), select SUM (NEWSNO) from Table1 GROUP by LibFrom Tabel1 t1,table2 T2 where T1.newsno =t2.newsnoGroup by LibYou will be prompted that the subquery returns more than one value. "Error, meaning that a su
the sum of the price value of all previous records in the current record. Therefore, you can sum each record (using the SUM function), but ask for the current record and the price of the previous record, such as the following SQL statement:
select a.xh, a.price, (select sum(price) from t_product b where b.xh from t_product a
As you can see from the SQL statement above, a
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.