本篇将举例子的方法介绍SQL Server中的内置函数(Built-in function)
1. COUNT
SELECT COUNT(Name) AS Total
FROM Products.Products_Information
GO
计算Products.Products_Information表格下的Name数量
2. SUM AVG
SELECT SUM(TotalDue), AVG(TotalDue)
FROM SaleOrders.Orders
WHERE OrderDate >= ‘2012-10-31’
GO
计算SaleOrders.Orders表格下的TotalDue总数和TotalDue平均数
3. 乘法
SELECT SalesEmployeeID, SUM (Bonus*1.05)
FROM SaleOrders.SalesEmployee
GROUP BY SalesEmployeeID
将SalesEmployeeID下的Bonus乘以1.05
4. 最大值，最小值
SELECT MIN(TotalDue) AS MINTotal,
MAX(TotalDue) AS MAXTotal
FROM SaleOrders.Order
GO
计算SaleOrders.Order下的TotalDue的最小值和最大值
5. REPLACE
SELECT REPLACE (‘MacDonald’, ‘Mac’, ‘Mc’)
GO
6. SUBSTRING
SELECT SUBSTRING (‘This is the test expression’, 9, 8)
GO
the test
7. 计算字母数
SELECT Last_name, LEN(Last_Name)
AS Characters
FROM HR.Employees
GO
Wallers 7
Bradley 7
8. 去除空格
SELECT LTRIM(‘ There are three blanks’)
AS BlanksRemoved,
(‘ There are three blanks’)
AS BlanksShowing
GO
9. RANK (非连续)
SELECT e.Last_Name, d.DeptName,
e.Salary, RANK() OVER (ORDER BY e.salary DESC)
AS Ranking
FROM HR.Employees e
JOIN HR.Dept d
ON e.DeptID = d.DeptID
GO
A 57000,00 1
B 44000.00 2
C 44000.00 2
D 43000.00 4
10. DENSE_RANK (连续)
SELECT e.Last_Name, d.DeptName, e.Salary,
DENSE_RANK() OVER (PARTITION BY d.DeptName
ORDER BY e.Salary DESC) AS DenseRanking,
RANK() OVER (PARTITION BY d.DeptName
ORDER BY e.Salary DESC) AS RANKING
FROM HR.Employees e
JOIN HR.Dept d
ON e.DeptID = d.DeptID
GO
A 57000,00 1 1
B 44000.00 2 2
C 44000.00 2 2
D 43000.00 3 4
11. ROW_NUMBER(连续数)
SELECT e.Last_Name, d.DeptName, e.Salary,
ROW_NUMBER() OVER(PARTITION BY d.DeptName
ORDER BY e.Salary DESC)
AS Ranking
FROM HR.Employees e
JOIN HR.Dept d
ON e.DeptID = d.DeptID
GO
A 57000,00 1
B 44000.00 2
C 44000.00 3
D 43000.00 4
12. NITIE() 将结果分列
SELECT e.Last_Name, d.DeptName, e.Salary
NTILE(5) OVER(PARTITION BY d.DeptName
ORDER BY e.Salary DESC) AS NTILEValue
FROM HR.Employees e
JOIN HR.Dept d
ON e.DeptID = d.DeptID
GO
A 57000,00 1
B 44000.00 1
C 44000.00 2
D 43000.00 2
E 41000.00 3
F 40000.00 4
G 39500.00 5
13. CONVERT
SELECT First_Name + ‘ ‘ + Last_Name + ‘ was hired on ‘ +
CAST(HireDate AS VARCHAR) AS ‘CAST’,
First_Name + ‘ ‘ + Last_Name + ‘ was hired on ‘ +
CONVERT(VARCHAR, HireDate, 3) AS ‘CONVERT’
FROM HR.Employees
GO
