union以及一些扩展
select name,age from Students where Age<3
union
select name ,age from Students where Age >4
--两个结果集要有相同数目的列还要有相同的数据类型(至少要可以隐式转换的)
select name 姓名,age 年龄 from Students where Age<3
union
select name ,age from Students where Age >4
--列名是第一个决定的
select name 姓名,age 年龄 from Students where Age<3
union
select name ,age from Students where Age >4
union
select name ,age from Students where Age >4
--union会自动去除重复项
select name 姓名,age 年龄 from Students where Age<3
union all
select name ,age from Students where Age >4
union all
select name ,age from Students where Age >4
--union all不会把重复的项去掉
select name,gender from Students
union all
select name ,cast(gender as nvarchar(10)) from Classes
--如果类型不同可以通过cast进行尝试转换
select '最高分:'+CAST( MAX(English)as nvarchar(10))from score --最高分
union all
select '最低分:'+CAST( MIN (English) as nvarchar(10)) from score -- 最低分
union all
select '平均分:'+cast(AVG(English)as nvarchar(10) )from score --平均分
--↑竖直的方式呈现
select '最高分:'+CAST( MAX(English)as nvarchar(10)),'最低分:'+CAST( MIN (English) as nvarchar(10)),'平均分:'+cast(AVG(English)as nvarchar(10) )from score
--水平方式显示↑
insert into Students( Name, Gender, Address, Phone, Age, Birthday, CardId, CId)select Name, Gender, Address, Phone, Age, Birthday, CardId, CId from Students
--把结果集查询出来再插入,表的列数,数据类型都要一样