1,限制行数,不重复列,以及联合查询--限制结果集的行数
复制代码 代码示例:
---查询前五行
select top 5 * from T_Employee order by FSalary desc
select * from T_Employee order by FSalary desc
----查询6-10行
select top 5 * from T_Employee
where FNumber not in
(
select top 5 FNumber from T_Employee
order by FSalary desc
)
order by FSalary desc
2,限制行数,不重复列,以及联合查询---SQL中自带的产生行号
复制代码 代码示例:
select ROW_NUMBER () over (order by fnumber asc ) as RowId ,*
from T_Employee
GO
select Fdepartment from T_Employee
3,限制行数,不重复列,以及联合查询----选择不重复的列
复制代码 代码示例:
select distinct Fdepartment from T_Employee
---查询多个列时和Group By一样,组合中不重复的列
SELECT DISTINCT FDepartment,FSubCompany
FROM T_Employee
GO
4,限制行数,不重复列,以及联合查询---UNION合并结果集,并去除重复项
复制代码 代码示例:
SELECT FNumber,FName,FAge FROM T_Employee
UNION
SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee
GO
5,限制行数,不重复列,以及联合查询-----UNION每个结果集必须有相同的列数;每个结果集的列必须类型相容。
复制代码 代码示例:
SELECT FNumber,FName,FAge,FDepartment FROM T_Employee
UNION
SELECT FIdCardNumber,FName,FAge,'临时工,无部门' FROM T_TempEmployee
GO
6,限制行数,不重复列,以及联合查询-----UNION ALL 合并结果集,不去除重复项
复制代码 代码示例:
SELECT FName FROM T_Employee
UNION ALL
SELECT FName FROM T_TempEmployee
7,限制行数,不重复列,以及联合查询/////UNION ALL 练习
复制代码 代码示例:
select '员工最高年龄',MAX(fage) from T_Employee
union all
select '员工最低年龄',min(fage) from T_Employee
union all
select '临时员工最高年龄', MAX(FAge ) from T_TempEmployee
union all
select '临时员工最低年龄',MIN (fage) from T_TempEmployee
GO