求sql语句

Question

我有这样三个表：
infant:(幼儿园表)
infantId   infantName
  1           aa
  2           bb
  3           cc－－－－－－－－－－－－－－－
teacher_active:(教师参与活动表，即连接表)
id   teacherId   infantId   teacherName  activId
1       1           1          张三           1
2       2           2         李四           2
3       3           1         王五           2
4       1           1         张三           2
5       1           1         张三           3－－－－－－－－－－－－－－
active：(活动表)
activId   activYear
  1         2007
  2         2006
  3         2006
--------------------
我想要如下的结果：
 根据用户输入的不同年份来判断幼儿园在当年参与活动的教师人数(此人数按参与人头数来算)
若输入2007，则
infantId   infantName  teacherCount
  1            aa          1
  2            bb          0
  3            cc          0若输入2006，则
infantId   infantName  teacherCount
  1            aa          3
  2            bb          1
  3            cc          0
若无输入，则计算总人数
infantId   infantName  teacherCount
  1            aa          4
  2            bb          1
  3            cc          0多谢！

Answer 1

select a.*,b.count as teacherCount
from infant a,(select infantId,count(*) as count from teacher_active where activId in (select activId from active where activYear like '%年份%')) b
where a.infantId=b.infantId

Answer 2

create table infant ( -- 幼儿园表
infantId int primary key,
    infantName varchar(32)
)
insert into infant
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc'create table teacher_active ( -- 教师参与活动表，即连接表
id int primary key,
    teacherId int,
infantId int,
teacherName varchar(32),
activId int
)
insert into teacher_active
select 1,1,1,'张三',1 union all
select 2,2,2,'李四',2 union all
select 3,3,1,'王五',2 union all
select 4,1,1,'张三',2 union all
select 5,1,1,'张三',3create table active ( -- 活动表
activId int primary key,
    activYear int
)
insert into active
select 1,2007 union all
select 2,2006 union all
select 3,2006 -- 根据用户输入的不同年份来判断幼儿园在当年参与活动的教师人数(此人数按参与人头数来算)
go
create procedure proc_ct (@year int) as begin
select a.infantId, a.infantName, teacherCount=isnull(teacherCount,0)
from infant a
left join (
select infantId, teacherCount = count(1) 
from active a
join teacher_active b on a.activId=b.activId
where activYear=isnull(@year,activYear)
group by infantId
) as b on a.infantId=b.infantId
end
go
declare @year int
set @year = 2007 
exec proc_ct  @year 
set @year = 2006 
exec proc_ct  @year 
set @year = null 
exec proc_ct  @year 
go
drop procedure proc_ct
drop table infant
drop table teacher_active
drop table active
go

Answer 3

select infantid,infantName ,count(teacherName) from 
(
select infantId ,(select infantName from infant where infantId=teacher_active.infantId) as infantName , teacherName  from teacher_active where activId=(select top 1 activYear  from activYear where activId=active.activId)
) as b  group by infantid,infantName 

Answer 4

--根据变量@year的值来求结果。前两段SQL内容一样。第三段为求总人数。
create table infant ( -- 幼儿园表
    infantId int primary key,
    infantName varchar(32))
insert into infant
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc'create table teacher_active ( -- 教师参与活动表，即连接表
    id int primary key,
    teacherId int,
    infantId int,
    teacherName varchar(32),
    activId int)
insert into teacher_active
select 1,1,1,'张三',1 union all
select 2,2,2,'李四',2 union all
select 3,3,1,'王五',2 union all
select 4,1,1,'张三',2 union all
select 5,1,1,'张三',3create table active ( -- 活动表
    activId int primary key,
    activYear int)
insert into active
select 1,2007 union all
select 2,2006 union all
select 3,2006 -- 根据用户输入的不同年份来判断幼儿园在当年参与活动的教师人数(此人数按参与人头数来算)
declare @year as int
set @year = 2007
select a.* , isnull(t.teacherCount,0) teacherCount from infant a left join
(select infantId , count(*) teacherCount from teacher_active b, active c where b.activId = c.activId and c.activYear = @year group by infantId) t
on a.infantId = t.infantId
/*
infantId    infantName                       teacherCount
----------- -------------------------------- ------------
1           aa                               1
2           bb                               0
3           cc                               0(3 行受影响)
*/set @year = 2006
select a.* , isnull(t.teacherCount,0) teacherCount from infant a left join
(select infantId , count(*) teacherCount from teacher_active b, active c where b.activId = c.activId and c.activYear = @year group by infantId) t
on a.infantId = t.infantId
/*
infantId    infantName                       teacherCount
----------- -------------------------------- ------------
1           aa                               3
2           bb                               1
3           cc                               0(3 行受影响)
*/--求总人数
select a.* , isnull(t.teacherCount,0) teacherCount from infant a left join
(select infantId , count(*) teacherCount from teacher_active b, active c where b.activId = c.activId group by infantId) t
on a.infantId = t.infantId
/*
infantId    infantName                       teacherCount
----------- -------------------------------- ------------
1           aa                               4
2           bb                               1
3           cc                               0(3 行受影响)
*/
--drop table infant,teacher_active,active

Answer 5

--使用存储过程根据不同年份获取结果。无年份用0表示
CREATE PROCEDURE my_proc @year int
As
begin
  if @year <> 0 
     select a.* , isnull(t.teacherCount,0) teacherCount from infant a left join
     (select infantId , count(*) teacherCount from teacher_active b, active c where b.activId = c.activId and c.activYear = @year group by infantId) t
     on a.infantId = t.infantId
  else
      select a.* , isnull(t.teacherCount,0) teacherCount from infant a left join
      (select infantId , count(*) teacherCount from teacher_active b, active c where b.activId = c.activId group by infantId) t
      on a.infantId = t.infantId
end
gocreate table infant ( -- 幼儿园表
    infantId int primary key,
    infantName varchar(32))
insert into infant
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc'create table teacher_active ( -- 教师参与活动表，即连接表
    id int primary key,
    teacherId int,
    infantId int,
    teacherName varchar(32),
    activId int)
insert into teacher_active
select 1,1,1,'张三',1 union all
select 2,2,2,'李四',2 union all
select 3,3,1,'王五',2 union all
select 4,1,1,'张三',2 union all
select 5,1,1,'张三',3create table active ( -- 活动表
    activId int primary key,
    activYear int)
insert into active
select 1,2007 union all
select 2,2006 union all
select 3,2006 -- 根据用户输入的不同年份来判断幼儿园在当年参与活动的教师人数(此人数按参与人头数来算)
exec my_proc 2007
/*
infantId    infantName                       teacherCount
----------- -------------------------------- ------------
1           aa                               1
2           bb                               0
3           cc                               0(3 行受影响)
*/exec my_proc 2006
/*
infantId    infantName                       teacherCount
----------- -------------------------------- ------------
1           aa                               3
2           bb                               1
3           cc                               0(3 行受影响)
*/--求总人数
exec my_proc 0
/*
infantId    infantName                       teacherCount
----------- -------------------------------- ------------
1           aa                               4
2           bb                               1
3           cc                               0(3 行受影响)
*/
drop table infant,teacher_active,active
drop procedure my_proc

Answer 6

create proc wsp 
@year int=0
as
select a.*,b.cnt from infant a left join
(select infantId,count(1)cnt from teacher_active where activId in
(select activId from active where activYear=isnull(nullif(@year,0),activYear)) group by infantId)b
on a.infantId=b.infantId--exec wsp 2007
--exec wsp 2006
--exec wsp

Answer 7

declare @activyear int
set @activyear=2007select  b.infantId    ,   a.infantName  ,count(teachername)  as   teacherCount  from infant a,teacher_active b,active c
where a.infantid=b.infantid and b.activid=c.activid and activyear=isnull(@activyear,activyear)
group by b.infantId    ,   a.infantName

Answer 8

declare @activyear int
set @activyear=2006
select a.*,isnull(teachercount,0) as teachercount from infant a left join (select b.infantid ,count(teachername) teacherCount from  teacher_active b ,active c where b.activid=c.activid and activyear=isnull(@activyear,activyear) group by b.infantid) b
on  a.infantid=b.infantid

Answer 9

create table infant(infantid int,infantname varchar(10))
insert into infant select 1,'aa'
union all
select 2,'bb'
union all
select 3,'cc'
gocreate table teacher_active (id int,teacherid int,infantid int,teachername varchar(10),activid int)
insert into teacher_active select 1,1,1,'张三',1
union all
select 2,2,2,'李四',2
union all
select 3,3,1,'王五',2
union all
select 4,1,1,'张三',2
union all
select 5,1,1,'张三',3
go
create table active(activid int,activyear varchar(4))
insert into active select 1,'2007'
union all
select 2,'2006'
union all
select 3,'2006'
go
create proc test @YY as varchar(4)
as
declare @YY1 as varchar(4)
,@YY2 as varchar(4)
IF @YY = ''
   BEGIN 
      SET @YY1 = ''
      SET @YY2 = 'Z'
   END
   ELSE BEGIN
      SET  @YY1= @YY
      SET @YY2 = @YY
   END 
select a.*,isnull(d.teacherCount,0)
from infant a
left join (select infantId,count(1) teacherCount from teacher_active b, active c
where b.activId = c.activId
  and c.activyear between @YY1 and @YY2
group by infantId)d
on a.infantId = d.infantId
exec test ''
drop proc test