Content List:
- Case When
- CROSS JOIN (U-SQL)
- Cross Apply And Outer Apply
- Querying XML fields using t-sql
一：Case When
case expression has two formats:
simple case expression
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]searched case expression
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
Arguments:
- WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used.
- THEN result_expression
Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE.
- ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL
NOTE:
- Simple CASE expression:
Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
- Searched CASE expression
Returns result_expression of the first Boolean_expression that evaluates to TRUE.
Examples
- Using a SELECT statement with a simple CASE expression
Within a SELECT statement, a simple CASE expression allows for only an equality check; no other comparisons are made. The following example uses the CASE expression to change the display of product line categories to make them more understandable.
USE AdventureWorks2012;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
- Using a SELECT statement with a searched CASE expression
Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.
USE AdventureWorks2012;
GO
SELECT ProductNumber, Name, "Price Range" =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
To left join query, using the syntax of the case after on (Similar example appeares in my own project)
select * from AIRPORTORDER as A
inner join RANGECODE as B
on
case when A.AIRPORDER_GOORBACK='2' then A.AIRPORDER_ENDCODE
when A.AIRPORDER_GOORBACK='1' then A.AIRPORDER_STARTCODE
end =B.RANGECODE_NO
GO
二：CROSS JOIN (U-SQL)
A cross join returns the Cartesian product of rows from the rowsets in the join. In other words, it will combine each row from the first rowset with each row from the second rowset.
Note that this is potentially an expensive and dangerous operation since it can lead to a large data explosion. It is best used in scenarios where a normal join cannot be used and very selective predicates（判断） are being used in the WHERE clause to limit the number of produced rows.
三：Cross Apply And Outer Apply
The Apply operator joins two table valued expression, the table on right is evaluated every time for each row of the table on the left which is actually a table-valued function. The final outcome contains all the selected columns from left side table and then from the right side table.There are two types of APPLY operators
- Cross Apply
The Cross Apply returns rows form the outer table (table on the left of the Apply operator) that produces matching values from the table-valued function (which is on the right side of the operator).
The Cross Apply is equivalent to Inner Join, but it works with a table-valued function.
Example: To view the working of the Cross Apply operator first we shall create two tables namely EmployeeDetails and EmpSalary.
Below is the schema and create query for EmployeeDetails
CREATE TABLE EmployeeDetails (
EmpId int PRIMARY KEY,
EmpFirstName VARCHAR(50),
EmpLastName VARCHAR(50),
Department VARCHAR(50),
DepartID INT
)
The data in EmployeeDetails table is provided below
Now Create another table EmpSalary
CREATE TABLE EmpSalary (
EmpID INT,
EmpFullName VARCHAR(80),
EmpSalary INT,
EmpWorkingYears INT,
DepartID INT
)
Here is the data in EmpSalary table
Now we will create a user defined function of sql server with name fn_Salaryinc, this function returns output with increased salary by Rs.5000 on the basis of Departid column.
CREATE FUNCTION fn_Salaryinc (@DepartmentID int)
RETURNS TABLE
AS
RETURN
(
SELECT
EmpID, EmpFullName,
EmpSalary+5000 AS Salaryinc
FROM
Empsalary
WHERE
DepartID = @DepartmentID
)
GO
Use funtion fn_Salaryinc to get increased salary.
SELECT EmpID, Salaryinc FROM fn_Salaryinc(2)
After applying the above function the salary got increased by 5000. The below table shows the output for the function fn_Salaryinc
Now since the function is giving the desired results we will write a Cross apply Query
SELECT
e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM
EmployeeDetails AS e
CROSS APPLY
fn_Salaryinc (e.DepartID) AS f
The output of the above query which shows the functioning for Cross Apply operator
We are getting repetitive employees as we are running the function, and it gets data once for each Departid.
- Outer Apply
Actually，Outer Apply operator is similar with Cross Apply operator，using the Outer Apply operator we are able to return all the rows from the outer table no matter if function returns any row.
四：Querying XML fields using t-sql
Example
This creates a table with one column of the xml datatype and inserts one row with a (simple) XML document:
declare @demo table(field1 xml)
create table demo (field1 xml)
insert into @demo (field1)
values ('<document>
<header>Alphabet</header>
<items>
<item id="a">a is for apple</item>
<item id="b">b is for balloon</item>
</items>
</document>')
As you can observe the document structure is like this:
<document>
<header>Alphabet</header>
<items>
<item id="a">a is for apple</item>
<item id="b">b is for balloon</item>
</items>
</document>
Now the cool thing is that there are several methods that can be executed on the Xml datatype, such as:
Query、Value、Exists、Modify、Nodes
In this brief introduction I’ll highlight the usage of the Value 、 Query and Nodes methods. All methods are well documented in the MSDN library.
- Query
select field1.query('/document/header') A from @demo
select field1.query('/document/items/item[@id="a"]') B from @demo
- Value
value (XQuery, SQLType)
select field1.value('(/document/items/item)[1]', 'nvarchar(max)') C from @demo
- nodes
nodes (XQuery) as Table(Column)
A nodes() method invocation with the query expression /document/items/itemwould return a rowset with three rows, each containing a logical copy of the original XML document, and with the context item set to one of the nodes:
You can then query this rowset by using xml data type methods. The following query extracts the subtree of the context item for each generated row:
SELECT T2.Loc.query('.') D
FROM @demo
CROSS APPLY field1.nodes('/document/items/item') as T2(Loc)
study links
1.Case When
2.CROSS JOIN (U-SQL)
3.Cross Apply And Outer Apply With Examples
4.Querying XML fields using t-sql