Note-microsoft SQL Server 2008 Tech Insider: T-SQL language Basics-02 single-Table queries

Aliases in the SELECT clause

The SELECT clause is processed after the From, where, GROUP by, and having clauses, which means that the alias assigned to the expression in the SELECT clause does not exist for those clauses that were processed before the SELECT clause. For example:

SELECT  Year  as OrderYear  from sales.orders WHERE > 2006;

This is wrong, the OrderYear alias is not recognized in the WHERE clause and should be changed to:

SELECT  Year  as OrderYear  from sales.orders WHERE  Year > 2006;

About the WITH TIES option

Let's look at the following code:

SELECT TOP (5) OrderID, OrderDate, CustID, Empid  from sales.orders ORDER  by DESC DESC;

Execute Query Results

After adding the WITH TIES option:

SELECT TOP (5 with TIES OrderID, OrderDate, CustID, Empid from sales.ordersORDER  byDESC;

Then look at the results of the execution:

That is, the WITH TIES option returns all other rows with the same sort value (in this case, OrderDate) as the last row in the top N row (in this example, Chinese May 5, 2008).

OVER clause

Let's look at the following code:

SELECT OrderID, CustID, Val,   SUM  Over  as Totalvalue,   SUM  Over  by  as Custtotalvalue  from Sales.ordervalues;

Execution Result:

Then look at the following code:

SELECT  SUM  as Totalvalue  from    Sales.ordervalues;

Execution Result:

By contrast, it is not necessary to group data by using over, but also to return the columns and aggregate columns of the underlying row in the same row.

Look at the code again:

SELECT OrderID, CustID, Val,    - * / SUM  Over  as Pctall,    - * / SUM  Over  by  as Pctcust  from Sales.ordervalues;

Execution Result:

Note that one of the small details in the above code is the addition of a dot after 100, rather than the direct use of the integer 100, because it implicitly converts the integer value Val and sum (val) to a decimal real value, otherwise the division in the expression will be "integer division", which will truncate the fractional part of the number.

The over clause also supports four ranking functions: Row_number (line number), rank (rank), Dense_rank (dense rank), NTILE, see the following code:

SELECTOrderID, CustID, Val, Row_number () Over(ORDER  byVal asrownum, RANK () Over(ORDER  byVal asrank, Dense_rank () Over(ORDER  byVal asDense_rank, NTILE (Ten) Over(ORDER  byVal asNtile fromsales.ordervaluesORDER  byVal

Execution Result:

Simply explain the functions above.

Row_number is used to assign an incremented sequence number to each row in the result set of a query, whose logical order is specified by the ORDER BY statement in the over clause. The row_number generates a unique line number value. The difference between rank and Dense_rank is that rank indicates how many rows previously had a lower sort value, whereas Dense_rank indicates how many lower sort values were previously. The Ntile function associates the rows in the result to a group and assigns a number to each row for the group to which it belongs. The Ntile function accepts an input parameter that represents the number of groups and specifies the logical order in the over clause. The above code example is divided into 10 groups.

Use the partition by statement in the OVER clause:

SELECT OrderID, CustID, Val,    Over  by CustID                     ORDER  by  as rownum  from sales.ordervalues ORDER  by CustID, Val;

Execution Result:

As you can see from the results, the line numbers are calculated independently for each customer.

Note that if the window function is specified during the Select Processing phase, the window calculation is processed before the DISTINCT clause (if any).

Predicates and operators

Common predicates are: in, between, like, and so on.

Case expression

Let's look at one simple:

SELECTProductID, ProductName, CategoryID, CaseCategoryID when 1  Then 'Beverages'     when 2  Then 'Condiments'     when 3  Then 'Confections'     when 4  Then 'Dairy Products'     when 5  Then 'grains/cereals'     when 6  Then 'Meat/poultry'     when 7  Then 'Produce'     when 8  Then 'Seafood'    ELSE 'Unknown Category'  END  asCategoryName fromproduction.products;

Execution Result:

If there is an ELSE clause in the case expression, it is treated by default as else NULL.

Look at a bit more complicated:

SELECTOrderID, CustID, Val, CaseNTILE (3) Over(ORDER  byval) when 1  Then ' Low'     when 2  Then 'Medium'     when 3  Then ' High'    ELSE 'Unknown'  END  asTitledesc fromsales.ordervaluesORDER  byVal

Execution Result:

Case-Search expression:

SELECTOrderID, CustID, Val, Case      whenVal< 1000.00                    Then ' less Then'     whenValbetween 1000.00  and 3000.00  Then 'between'     whenVal> 3000.00                    Then 'More than'    ELSE 'Unknown'  END  asvaluecategory fromSales.ordervalues;

Execution Result:

Sorting rules

If you want the collation of a column to be case-insensitive, you can modify the collation of an expression as follows:

SELECT Empid, FirstName, LastName  from HR. EmployeesWHERE= N'Davis';

Date and time

Let's look at the following code:

SELECT GETDATE ()SELECTcurrent_timestamp

The above two lines of code return the same date, but Current_timestamp is the standard SQL, so it is preferable to use Current_timestamp.

Note-microsoft SQL Server 2008 Tech Insider: T-SQL language Basics-02 single-Table queries