union基本的原则，每个结果集必须有相同的列数，且结合的列有相同的数据类型

union会去除重复，重新排列数据，union all不去除重复也不会重新排列。

select tsid,tSName,tSGender,tSAge,tSBirthday from TblStudent

union

select tTId,tTName,tTGender,tTAge,tTBirthday from TblTeacher

大多数情况下，使用union all,效率较快




select tSGender,sum(tSAge) as tTAge from TblStudent group by tSGender order by tTage


union 


select tTGender,sum(tTAge) as tTage from TblTeacher group by tTGender order by tTage






--语法错误，第一个union前不能加oder by


--下述正确


select tSGender,sum(tSAge) as tTAge from TblStudent group by tSGender 


union 


select tTGender,sum(tTAge) as tTage from TblTeacher group by tTGender order by tTage


select 名称='最高分', 分数=max(tmath) from TblScore


union all


select 名称='最低分', 分数=min(tmath) from TblScore


union all


select 名称='平均分', 分数=avg(tmath) from TblScore


select


max(tmath) as '最高分',min(tmath) as '最低分',avg(tmath) as '平均分'


from TblScore

通过union一次向表中插入多条数据

insert into TblStudent

select '柳宗元','男','唐朝柳府','123xxx458',16,1767-08-08,'310xxxxxx6',3

union all

select '钟无艳','女','秦朝秦府','123xxx459',18,1765-08-08,'310xxxxxx7',4

本文来源https://www.modb.pro/db/99210

ITPub博客

IT日志之SqlServer数据库：union

最新文章