前言:
当前咱们对“oraclesql字段合并”大体比较重视,大家都想要剖析一些“oraclesql字段合并”的相关文章。那么小编也在网摘上收集了一些对于“oraclesql字段合并””的相关知识,希望看官们能喜欢,看官们快快来学习一下吧!UNION合并结果集是对两条查询语句的结果进行整理再返回结果
联接和合并结果集的区别:
联接:根据SELECT选择列表将两个表的指定列横向联接在一起合并结果集:把两个或多个查询结果纵向追加在一起 (合并的结果集列数要相同,对应的列的数据类型也要相同或者能够进行隐式转换)UNION和UNION ALLUNION:会将两个结果集中的重复行进行删除操作UNION ALL:结果中包含所有行
SELECT employee_name, employee_sex FROM employee
UNION
SELECT employee_name, employee_sex FROM employee
SELECT employee_name, employee_sex FROM employee
UNION ALL
SELECT employee_name, employee_sex FROM employee
对合并结果集进行排序
SELECT a.employee_id,a.employee_name, b.salary_type,b.salary FROM employee a
INNER JOIN salary b
ON a.employee_id = b.employee_id
where b.salary_type ='岗位工资'
UNION ALL
SELECT c.employee_id,c.employee_name, d.salary_type,d.salary FROM employee c
INNER JOIN salary d
ON c.employee_id = d.employee_id
where d.salary_type ='技能工资'
使用ORDER BY 按employee_id 进行排序
SELECT a.employee_id,a.employee_name, b.salary_type,b.salary FROM employee a
INNER JOIN salary b
ON a.employee_id = b.employee_id
where b.salary_type ='岗位工资'
UNION ALL
SELECT c.employee_id,c.employee_name, d.salary_type,d.salary FROM employee c
INNER JOIN salary d
ON c.employee_id = d.employee_id
where d.salary_type ='技能工资'
ORDER BY employee_id
此处ORDER BY 后的排序列必须是合并结果集表中的列名,否则会报错
如 ORDER BY d.employee_id
ORDER BY 只能在最后一个SELECT语句后面使用,因为其逻辑处理阶段是在UNION合并结果集之后。
学习参考资料:《跟韩老师学 SQL Server 数据库设计与开发》
标签: #oraclesql字段合并