SQL Server Learning Note Series 6

First, write it in front.

Time is very familiar to each of us, but in the end it is what, with what to measure, probably a lot of people will be stunned there. Time can witness everything, but also can kill all, those past point drops can think can recall. Recall that after the Qingming Festival, in the home of the night can always hear bursts of frogs, it is a crisp cry, it is the taste of hometown. In a twinkling of an eye, seemingly those days have been away from me for a long time, in the hustle and bustle of the city, to find that kind of inner peace. Sigh the passage of time, miss the past bit by bit. I would like to find a stable mood in the bustling city to learn, perhaps a good way. Study will let us recognize ourselves, find ourselves, do the inner strong, rashness, positive and enterprising, sunshine self-confidence. The man who strives is the luckiest! Just do it!

From now on, do not live the life you should have, go to the life you have thought of! --Thoreau

Two. Derived tables

A derived table is a special subquery that uses a subquery after the From, and note here that the derived table must be aliased.

For example: Join us to inquire, customer information, and the number of customers each country has. So we can write this:

1  2  SELECT * FROM3  (4  Select Custid,count (*) over (PARTITION by country) as N ' number of customers ' 5  from SALES.CUSTOMERS6  ) T

The clause in parentheses is equivalent to a derived table, and if we do not alias COUNT (*) over (PARTITION by country) Here, then select * does not know which column to remove, so it will give an error.

At the same time, there is another way to alias, that is, outside its alias, the same can achieve the effect, the wording is as follows:

1  SELECT * FROM2  (3  Select Custid,count (*) over (PARTITION by country) 4 from  sales.customers5  ) T ( CustID, number of customers)

Three. CTE (common-table expression)

(1) Common CTE usage

How to use a CTE, which must be defined here, and named. The defined form is as follows:

1 with  use_customers2  AS3  (4    SELECT companyname,country5 from    sales.customers 6    WHERE country= ' USA ' 7  )

So we're going to use a CTE, just query the corresponding CTE name.

1 with  use_customers2  AS3  (4    SELECT companyname,country5 from    sales.customers 6    WHERE country= ' USA ' 7  ) 8  9  SELECT * from Use_customers

Results:

However, it is important to note that when using a CTE, when an external query is completed, the lifetime of the CTE is over, and if the CTE needs to be queried, it will fail at this time.

It is also possible to give the CTE each column its alias, which also exists in the expression inside and outside the alias, to see what kind of personal preference.

1  with use_customers2  AS3  (4    SELECT CompanyName as n ' Company name ', country as n ' Country name ' 5 from    sales.customers 6    WHERE country= ' USA ' 7  ) 8  9  SELECT * from Use_customers

1 with  Use_customers (company name, country name) 2  AS3  (4    SELECT CompanyName, country 5 from    Sales.customers 6    WHERE country= ' USA ' 7  ) 8  9  SELECT * from Use_customers

The results of the query are:

(2) CTE with parameters

A CTE with parameters can be used multiple times, simply by changing the parameter's pass-through value.

For example: Query the customer information of each country, the country exists usa,uk ..., multiple countries, at this point we use the country as a variable, similar to the parameters in C #. Declare the CTE as follows:

1  DECLARE @country NVARCHAR; 2  SET @country = ' USA '; 3  with Use_customers (company name, country name) 4 as  5  (6    SELECT CompanyName, country  7 from    sales.customers  8    WHERE [email protected] 9  )  SELECT * from Use_customers

If you want to query some customer information of UK countries, then only need to change the set @country = ' UK ' can be queried, do not need to change the structure of the CTE.

1  DECLARE @country NVARCHAR; 2  SET @country = ' UK '; 3  with Use_customers (company name, country name) 4 as  5  ( 6    SELECT CompanyName, country  7 from    sales.customers  8    WHERE [email protected] 9  ) 10  One  SELECT * from Use_customers

(3) Complex CTE use

"1" Here, if we want to query the number of annual orders more than 10 of customer information. According to General thinking, we can talk about orders according to the annual group, and then use the aggregate function count counts the number of orders per customer, and then find more than the number of orders more than 10 customer information. So we can do the following sql:

Query Result:

"2" You can also use subqueries, which are derived tables to come out of the question in turn.

The first step is to check the year of the order, the customer's information

1  SELECT Year  (OrderDate) as Orderyear,custid2 from  sales.orders

The second step is to group the number of orders per customer according to the year. The first step of the query results as a subquery, that is, a derived table.

1  Select Orderyear,custid,count (*) as N ' order quantity ' 2  FROM3  (4      SELECT year  (OrderDate) as OrderYear, CustID 5 from      sales.orders6  ) as t17  GROUP by Orderyear,custid

The third step is to find out the customer information with order quantity greater than 10.

1  Select Orderyear,custid,ordercount 2 from  3  (4  SELECT orderyear,custid,count (*) as  OrderCount 5 from  6          (7              SELECT year  (OrderDate) as Orderyear,custid  8 from              sales.orders 9          ) as t110< C12/>group by Orderyear,custid11  ) as t212  WHERE ordercount >10

is not feeling the use of sub-query, a layer of one layer, but after the analysis, in line with our general problem-solving thinking of what to do first, and then what step. Here we can use the newly acquired CTE to solve the problem.

"3" uses a CTE to resolve customer information for annual orders over 10.

The advantage is that a well-defined CTE can be used directly as a derived table, so we can define the CTE as described above, define three CTE respectively, and finally query the result:

One thing to keep in mind: Define multiple CTE separated by commas (,), without having to use with to redefine.

1 with  Yearorder01 2 as  3  (4        SELECT Year (OrderDate) as Orderyear,custid 5 from        sales.orders 6  ) , 7  Yearorder02 8  as  9  (          orderyear,custid,count (*) as ordercount11          from yearorder0112          GROUP by Orderyear,custid13  ),  yearorder0315  AS16  (         SELECT orderyear,custid,ordercount18 from         yearorder0219         WHERE  ordercount>1020  )  SELECT * from YEARORDER03

The query results are the same as the subquery results:

Four. Multiple CTE

The use of multiple CTE will make it easier for us to make related subqueries. For example: if we want to inquire how many customers in the year, what should we do with the test? We will think of, or according to the annual grouping, and then aggregate Count (CustID) to find out how many customers per year, which should pay attention to the weight, that is, the same custid belong to the same customer, so write count (distinct CustID). SQL is as follows:

1  2  SELECT Year (OrderDate) as Orderyear,count (DISTINCT CustID) as  custcount3 from  sales.orders4  GROUP by year (OrderDate)

Now join has a demand, is to do a query, to find out the number of customers and the number of customers in the previous year, and also calculate the number of customers between the amount of how many, what should we deal with? Ask for the difference between the previous year and the current year, then you can think of whether the current results set can be found to do a connection, you can get the previous year and the current year, so SQL is as follows:

1  SELECT Pre_orderyear,now_orderyear,pre_custcount,now_custcount, 2        (now_custcount-pre_custcount) as N ' Poor customer count ' 3  from  4  (5  SELECT Year (OrderDate) as Now_orderyear,count (DISTINCT CustID) as Now_custcount  6  From Sales.orders 7 GROUP by year (  OrderDate) 8  ) as T1 9 left  JOIN ten  (one   SELECT year (OrderDate) A S Pre_orderyear,count (DISTINCT custid) as  pre_custcount12 from  sales.orders13 GROUP by year  (OrderDate ) as  t215 on  t1.now_orderyear=t2.pre_orderyear+1;

Which is the result set to make a self-connection, but we do not find that the writing seems very redundant, as a program ape we are to write as concise as possible Code,don ' t repeat yourself! Here our CTE plays his part, and the defined CTE can be used in the next step as a query condition. So let's see how the CTE is implemented. Similarly, we have to define a CTE before we can use a CTE, where we define a CTE to query the number of customers per year, as in the case of SQL above, but as a form of a CTE.

1 with  Custcount 2 as  3  (4  SELECT Year (OrderDate) as Orderyear,count (DISTINCT CustID) as  Custcount 5 from  sales.orders 6  GROUP by year (OrderDate) 7  ) 8   9  SELECT t1.orderyear as Nowyear,t2.orderyear As preyear,t1.custcount as nowcount,t2.custcount as precount,10        (t1.custcount-t2.custcount) as N ' poor customer count '  From Custcount t112 left  JOIN  custcount t213 on  t1.orderyear=t2.orderyear+1;

The result:

In fact, we can see from the SQL statement, the simplicity of the CTE is that the defined CTE can be reused to connect, compared to the sub-query itself of the connection, more concise, easy to operate, to join the required calculations, we only need to change the definition of the CTE.

Five. Recursive CTE

Recursive CTE used in recursive lookup is very much, in fact, can be seen as a level of lookup relationships, such as the company is generally divided into a level of departments, two departments, three departments .... Then the first level department contains more than two levels of departments, the level two department also contains a number of three departments and so on ....., for example: The company has a subordinate relationship, if we want to find an employee's superior is who, under normal circumstances we can do one's own connection, find out who is superior.

For example: Find the superior information for all customers. That is, in the hr.employees inside the Mgrid equals the employee's Empid, then found the employee several Mgrid's boss. SQL is as follows:

1  SELECT t1.empid,t1.mgrid,t1.lastname,t2.empid,t2.lastname2 from  HR. Employees T1 left JOIN hr. Employees T2 3 on  t1.mgrid =t2.empid

Because to ensure that all employee information is displayed, use the left-hungry connection here to ensure that the employees on the left side of the table are present. The result

Further discussion here, join us to inquire about the employee below all the subordinate information, how to do? For example, to inquire about the subordinate information of employee number 2nd, we can write sql:

1  SELECT * from 2  hr. Employees WHERE mgrid=2

We can see number 2nd employees have numbers 3rd and 5th, then 5th employees may also have subordinates, followed by inquiries:

1  SELECT * from HR. Employees2  WHERE mgrid in3  (SELECT empid from 4  hr. Employees WHERE mgrid=25  

We can see, the results found in the 4,6,7,8,9, then 4,6,7,8,9 below whether there are subordinates, is not still need to continue to query, so check down is endless, because we do not know how many layers, so recursive CTE is to solve such problems, A recursive CTE can be used to query the level of the situation until the end. First the recursive CTE needs to define a starting point, that is, the query starting point. The recursive query is then made according to the defined CTE itself until all the node information is found. As follows:

1 DECLARE @mgrid INT; 2 SET @mgrid = 2; 3 with Emplist 4 as 5 (6     --Here is the starting point, execute once for 7     SELECT Empid,lastname,mgrid 8 from     HR. Employees 9     WHERE [email protected]10     UNION ALL11     -recursion begins     with a SELECT e.empid,e.lastname, E.mgrid15 from     HR. Employees e INNER JOIN emplist M16 on     e.mgrid=m.empid     )-SELECT * from Emplist

Define a variable, accept the employee ID that you want to query, set to 2, that is, query all the subordinate information below the employee number 2nd.

The use of CTE there are many, here is only a few basic usage, if there are ideas, can be proposed, hope to learn together!

SQL Server Learning Note 1:http://www.cnblogs.com/liupeng61624/p/4354983.html

SQL Server Learning Note 2:http://www.cnblogs.com/liupeng61624/p/4367580.html

SQL Server Learning Note 3:http://www.cnblogs.com/liupeng61624/p/4375135.html

SQL Server Learning Note 4:http://www.cnblogs.com/liupeng61624/p/4388959.html

SQL Server Learning Note 5:http://www.cnblogs.com/liupeng61624/p/4392746.html

I hope that you Daniel give guidance, inappropriate to accept learning! Thank you!

SQL Server Learning Note Series 6