SQL Server Learning Notes <> related sub-queries and complex queries

Two. Querying for missing values

Here we join to query 2008 how many orders per day? We can start by querying all order information for the order date in 2008 of the order form.

1  SELECT DISTINCT orderdate,count (*) as N ' daily order volume ' from Sales.orders2  where OrderDate between ' 20080101 ' and ' 20081 231 ' 3  GROUP by OrderDate

Query results

As can be seen from the above, the number of orders per day according to the OrderDate group after the statistics, but we found that some dates do not exist, such as 2008-01-01, 2008-01-02 ... but did not find the 2008-01-03 date of the order quantity, Join our request to see the daily order? (The majority of this requirement is derived from the statistics of the financial statements), which requires us to construct a table, we can construct a date that contains 2008 each year, and then the table close does not have to come out every day is included in the order. I'm going to do it. Start by constructing a table that contains every day of 2008.

1  CREATE TABLE Nums2  (3    n int4  ); 5  6  select * from Nums;

Create a nums empty table to hold the consecutive dates. You can then insert some data into the table.

1  declare @i int;2  set @i=0;3 while  @i<4004  begin5  set @[email protected]+1;6  INSERT into Nums (n) values (@i); 7  End

You can see the table insert and 1 to 400 ordered numbers:

Then we can construct the continuous date, the addition of the date has already learned DATEADD (), if you want to study together, you can look at the previous notes:

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

Continue to add the dates, and here we add the dates together to construct each day of the year 2008:

1  Select DATEADD (day,n, ' 20071231 ') 2 from  nums;

Date result of the construct

Once the date is constructed, we can use this result set to make a connection to the order table sales.orders.

1  Select DATEADD (DAY,F.N, ' 20071231 '), COUNT (OrderID) as n ' Daily order quantity ' 2 from  Nums F left  join Sales.orders m on3< C3/>dateadd (DAY,F.N, ' 20071231 ') = M.orderdate4   Group by DATEADD (DAY,F.N, ' 20071231 ') 5   ORDER by DATEADD (Day, F.N, ' 20071231 ')

Results:

(2) Sub-query, that is, query results can be used as a query condition.

For example: We want to query employee information for the youngest employee table (hr.employees). SQL statements can be written like this:

1   Select Max (birthdate) as N ' birthday ' 2 from   hr.employees

Here we know that we can use the aggregate function max to query, but join us also to query the youngest name, that is, the Lastname,sql statement as follows, you can find the error, because the Max aggregation function is a set of results processing, and LastName is not included in the aggregate function, so error.

Then we need to use the sub-query to deal with, can talk about the minimum age of results as query results to further query.

1   Select birthdate,lastname2 from   hr.employees3   where birthdate=4   (5       Select Max (birthdate) 6 from       Hr.employees7   )

Query Result:

To continue the subquery, join us to find out the customer who placed the most expensive order, to issue a SVIP level of honor, and to find out some basic information about his country that has been his own.

According to the face of the query, we can write our SQL, the first here in the View Sales.ordervalues store is the order of some price information. So we operate on this view.

1. First find the most expensive information for your order

1   2   Select Max (val) as N ' most Expensive order ' 3 from   sales.ordervalues

2. Then find out what the customer ID is for your most expensive order

1   Select CustID from Sales.ordervalues2   where val= (3   Select Max (val) as N ' most Expensive order ' 4   from SALES.ORDERVALUES5   )

3. Then we can find the ID equal to the query in the Customer table, and find out the country.

1   Select Custid,contactname,country 2 from   sales.customers where custid= 3   (4               Select CustID from Sales.ordervalues 5               where val= 6             (7               Select Max (val) as N ' most Expensive order ' 8 from                sales.ordervalues 9              )   

Results:

Three. A correlated subquery, which is a nested query of another query, which has conditions that relate to each other.

For example: We want to query the number of orders per customer, we have already learned before, there are two ways to achieve:

1. Grouping with group......by

1   Select CustID, COUNT (*) as N ' order quantity ' from Sales.orders2   Group by CustID ORDER by CustID

2. Using Count.....over

1   SELECT DISTINCT custid,count (*) over  (partition by CustID) as N ' order quantity ' 2 from   sales.orders

The third Way we use the relevant sub-query to solve, it can be understood: is that we do not check a customer's order quantity is to go to the order form inside the customer ID of the same is taken out, and then summed with the aggregate function. Customer ID We can take it out of the customer's table and the ID is equal to the ID in the order form. So according to the analysis we write SQL as follows:

1   Select n.custid,n.contactname,2   (3   Select COUNT (*) 4 from   sales.orders M 5   where m.custid= N.custid6   ) as N ' order quantity ' 7 from   sales.customers n

The result:

This can also be the number of orders for customers to calculate, here is the use of the outer query with the inner layer of query conditions as a comparison sum. That's what we call a correlated subquery.

Four. Multi-valued subquery

For example: We want to inquire about a country where there are customers but there are no suppliers, that is, there are customers in this country, there are no supplier companies.

In general: We will use common SQL notation:

1   2   select distinct m.country from Sales.customers M3   where m.country not  in4   (5       Select N.country from Production.suppliers N6   )

Results:

Since there is not.....in writing, of course there is the exists of the wording, the same can be achieved, exists for the result set if the existence of the return true, there is no return false. We can understand this: the outer query passes country to the inner query to see if it exists, where the memory query contains multiple results, so it is called a multivalued subquery. So the SQL statement can be written like this:

1   SELECT distinct m.country from sales.customers m2   where isn't  exists 3   (4       Select N.country from Production.suppliers N5       where   n.country= m.country 6   )

Results:

You can see that the results are the same as the results not.....in found, satisfying the conditions.

Five. Complex sub-query

(1) For example: if we want to query the previous order and the last order information of the current order of all orders, here we first analyze:

1. First we can check out all the orders first.

1   SELECT distinct  custid2 from   sales.orders

2. Then the order is less than the current order ID, and the order is less than the largest one in the current order (that is, the next order).

1   SELECT DISTINCT  2   (3      Select Max (CustID) from 4      sales.orders m where m.custid< n.custid5< c17/>) as n ' previous order ', N.custid as n ' current order ' 6 7 from   sales.orders N

3. In the same vein, you can find the next order that is larger than the current order.

1   SELECT DISTINCT   2   (3      Select Max (CustID) from  4      sales.orders m where m.custid< n.custid 5< c8/>) as n ' previous order ', N.custid as n ' current order ', 6     (7      Select min (custid) from  8      sales.orders p where P.CUSTID&G T N.custid 9   ) as N ' after an order ' from   sales.orders n

The result:

(2) Cumulative aggregation

Cumulative aggregation in the financial statistics, often used, such as 2007 sell how much, 2008 sell how much, then 2008 cumulative sell is 2007 years plus 2008 sold the sum, that is cumulative aggregation.

Here we have View sales.ordertotalsbyyear, which counts the total number of orders per year.

1 SELECT * FROM  sales.ordertotalsbyyear

We can see that there are 25489 orders in 2007 years, there are 16247 orders in 2008, and 9581 orders in 2006. The cumulative aggregation is used to add the number of orders that we require to sell each year.

1 Select n.orderyear,2 (3    Select SUM (qty) 4    from  sales.ordertotalsbyyear  M5    where m.orderyear <=N.ORDERYEAR6) as n ' Cumulative order quantity ' 7  from  sales.ordertotalsbyyear N8  ORDER by N.orderyear;

Results:

SQL Server Learning Notes <> related sub-queries and complex queries