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
--把结果集查询出来再插入,表的列数,数据类型都要一样

posted @ 2014-04-06 22:44  编程猴子  阅读(170)  评论(0编辑  收藏  举报