For more information about SQL Server basic knowledge data retrieval and query of sorting statements, see.
For more information about SQL Server basic knowledge data retrieval and query of sorting statements, see.
The Code is as follows:
-- Execution sequence From Where Select
Select * from
(Select sal as salary, comm as commission from emp) x where salary <5000
-- Get Name Work as a Job
Select ename + 'work as a' + job as msg from emp where deptno = 10
-- If the employee's salary is less than 2000, the system returns "OK" between the OverPaid values when the UnderPaid is greater than or equal to 4 k.
Select ename, sal,
Case when sal <2000 then 'underpaid'
When sal> = 4000 then 'overpaid'
Else
'OK'
End
From emp
-- Returns N records newid () randomly from the table ()
-- When the order by clause specifies a numerical constant, it must be sorted according to the column at the corresponding position in the select list.
-- When a function is used in the order by clause, the result is sorted by the function in the calculation result of no row.
Select top 5 ename from emp order by newid ()
-- Null value is null
Select * from emp where comm is null
-- Convert null values to actual values
-- Explanation: return the first non-null expression in the parameter.
-- Coalesce combination, merge, and combine. English: [, k} u records 'Les] American: [, ko records 'l records s]
Select coalesce (comm, 1), empNo from emp
-- Search by Mode
-- Returns the row that matches the specified substring or pattern.
Select ename, job
From emp
Where deptno in (10, 20)
-- Sort by substrings by the last two characters in the position Field
Select ename, job from emp order by substring (job, len (job)-2, 2)
-- Select top 2 len (job)-2 from emp
-- Select top 2 job from emp
-- ☆☆☆☆☆☆Process sorting null values ☆☆☆☆☆[ can only be greater than 0]
Select ename, sal, comm
From emp
Order by 1 desc
-- Sort non-null values in descending or ascending order. Put the null values to the end. You can use case
Select ename, sal, comm from
(
Select ename, sal, comm,
Case when comm is null then 0 else 1 end as
From emp
) X
Order by A desc, comm desc
