各种类型的查询

/**//*文件名称:select.sql
*功能:        演示各种查询
*创建时间:    2008-4-14
*创建人:        XSing
*最后修改时间:    2008-4-14
*/
use [testdatabase]

/**//*插入一些数据
人物名称难凭空想象啊，希望广大仙剑迷不要扔转头。
*/
/**//*
[into]是可选项，针对旧的标准。
以前习惯了insert into 表名(,..,) values(,..,)
那是插入一条记录，其实也可以插入一个数据集，例如一个查询语句得到的集合
union 是集合操作，将N条select语句得到N个数据集合并成一个数据集
*/
insert into [dbo].[Student]
select 'ap0808801','景天',1,'1987-1-1' union all
select 'ap0808802','唐雪见',0,'1987-2-1' union all
select 'ap0808803','龙葵',0,'1988-3-1' union all
select 'ap0808804','紫萱',0,'1988-4-1' union all
select 'ap0808805','长卿',1,'1989-5-1' union all
select 'ap0808806','夕瑶',0,'1989-6-1' union all
select 'ap0808807','重楼',1,'1990-7-1' union all
select 'ap0808808','花盈',0,'1990-8-1' union all
select 'ap0808809','飞蓬',1,'1991-9-1'
go
insert into [dbo].[Course]
select 'gdsx','高等数学',5,null union all
select 'dxyy','大学英语',5,null union all
select 'fbhs','复变函数',3,'gdsx' union all
select 'xhyxt','信号与系统',5,'fbhs'
go
insert into [dbo].[SC]
select 'ap0808801','gdsx',59 union all
select 'ap0808802','gdsx',60 union all
select 'ap0808803','gdsx',61 union all
select 'ap0808804','gdsx',62 union all
select 'ap0808805','gdsx',63 union all
select 'ap0808806','gdsx',64 union all
select 'ap0808807','gdsx',65 union all
select 'ap0808808','gdsx',66 union all
select 'ap0808801','fbhs',50 union all
select 'ap0808802','fbhs',60 union all
select 'ap0808803','fbhs',70
go

--*查询一：年龄在18到19岁学生的姓名
/**//*
------------------
case表达式一：
case
when 布尔表达式 then 语句1
[,..n]
else 语句n+1
end
------------------
case表达式二：
case 条件表达式
when 比较表达式 then 语句1
[,..n]
else 语句n+1
end
*/
已知日期计算年龄（精确到日）的方法1：
datediff(year,sbirthday,getdate()) -
case when right(convert(char(10),[Sbirthday],120),5) > right(convert(char(10),getdate(),120),5)
then 1 else 0 end
已知日期计算年龄（精确到日）的方法2：
case
when month([Student].[Sbirthday]) > month(getdate())
then datediff(year,[Student].[Sbirthday],getdate()) - 1
when month([Student].[Sbirthday]) = month(getdate()) and day([Student].[Sbirthday]) > day(getdate())
then datediff(year,[Student].[Sbirthday],getdate()) - 1
else datediff(year,[Student].[Sbirthday],getdate()) end

--@方案一，查询条件中对列进行计算
select N'18到19岁学生' = [Student].[Sname]
from [dbo].[Student]
where datediff(year,sbirthday,getdate()) -
case when right(convert(char(10),[Sbirthday],120),5) > right(convert(char(10),getdate(),120),5)
then 1 else 0 end
between 18 and 19
go
--@方案二，利用子查询
/**//*
select temptable.x from (select tb.x,tb.y from tb) as temptable where temptable.y
这种形式的查询中必须为（）内子查询得到的临时表命名
*/
select N'18到19岁学生' = temptalbe.Sname from
(select  Sname,N'Age' = datediff(year,[Student].[Sbirthday],getdate()) -
case when right(convert(char(10),[Student].[Sbirthday],120),5) > right(convert(char(10),getdate(),120),5)
then 1 else 0 end from [dbo].[Student])as temptalbe
where temptalbe.Age between 18 and 19
go
--@方案三，加强版子查询
select N'18到19岁学生' = [Student].[Sname] from [dbo].[Student] where [Student].[Sno]
in
(
select temptalbe.Sno from
(select Sno,N'Age' = datediff(year,sbirthday,getdate()) -
case when right(convert(char(10),[Sbirthday],120),5) > right(convert(char(10),getdate(),120),5)
then 1 else 0 end from [dbo].[Student])as temptalbe
where temptalbe.Age between 18 and 19
)
go

--查询二：查询姓景，或者名字第二个子为楼字的学生名单
select N'姓景，或者名字第二个字为楼字的学生'=[Student].[Sname] from [dbo].[Student]
where [Student].[Sname] like '景%' or [Student].[Sname] like '_楼%'
go

--查询三：各课程的先行课程
/**//*
使用了别名的方法进行联合查询。数据分析中的父子维道来源于这种表。
*/
select N'课程' = a.Cname, N'先行课程' = IsNull(b.Cname,N'无') from
[dbo].[Course] a left join [dbo].[Course] b on a.Cpc = b.Cno
go


--查询四：选修了两门或以上课程的学生的名单
/**//*
分组查询要注意：1，select 后面的查询列必须是经过运算的聚合函数、常量或者group by分组列
               2，having而不是where
*/
select N'选修了两门或以上课程的学生'=[dbo].[Student].[Sname],N'课程数目'=count([SC].[Cno])
from
[dbo].[Student] inner join [dbo].[SC] on [Student].[Sno]=[SC].[Sno]
group by [Student].[Sname] having count([SC].[Cno]) >=2
go