我建了个测试环境,但还没找到解决办法,先给你。
create table student
(no int,
depart char(2),
iyear int,
)
insert into student
select 1 , 'S' , 2004
union select 2 , 'D' , 2003
union select 3 , 'B' , 2002
union select 4 , 'D' , 2002 create table scores
(no int,
depart char(2),
class char(10),
iyear int,
)
insert into scores
select 1 , 'S' , 'kc1' , 2004
union select 1 , 'S' , 'KC2' , 2004
union select 2 , 'D' , 'KC3' , 2003
union select 2 , 'D' , 'KC1' , 2003
union select 3 , 'B' , 'KC4' , 2002
union select 4 , 'D' , 'KC3' , 2002
union select 4 , 'D' , 'KC1' , 2002--drop table student
--drop table scores
create table student
(no int,
depart char(2),
iyear int,
)
insert into student
select 1 , 'S' , 2004
union select 2 , 'D' , 2003
union select 3 , 'B' , 2002
union select 4 , 'D' , 2002 create table scores
(no int,
depart char(2),
class char(10),
iyear int,
)
insert into scores
select 1 , 'S' , 'kc1' , 2004
union select 1 , 'S' , 'KC2' , 2004
union select 2 , 'D' , 'KC3' , 2003
union select 2 , 'D' , 'KC1' , 2003
union select 3 , 'B' , 'KC4' , 2002
union select 4 , 'D' , 'KC3' , 2002
union select 4 , 'D' , 'KC1' , 2002--drop table student
--drop table scores
----------- ------ -----------
1 S 2004
2 D 2003
3 B 2002
4 D 2002(4 row(s) affected)--TABLE 2
select * from scoresno depart class iyear
----------- ------ ---------- -----------
1 S kc1 2004
1 S KC2 2004
2 D KC1 2003
2 D KC3 2003
3 B KC4 2002
4 D KC1 2002
4 D KC3 2002(7 row(s) affected)
实考人数=(select count(distinct no) from scores where depart=a.depart and iyear=a.iyear),
应考课程数=(select count(distinct class) from scores where depart=a.depart and iyear=a.iyear),
实课程数=(select count(distinct class) from scores where depart=a.depart)
from student a group by depart,iyear
这方法好像不对啊我再试试
create table student(no int,depart char(2),iyear int)
insert student select 1,'S',2004
union all select 2,'D',2003
union all select 3,'B',2002
union all select 4,'D',2002 create table scores(no int,depart char(2),class char(10),iyear int)
insert scores select 1,'S','kc1',2004
union all select 1,'S','KC2',2004
union all select 2,'D','KC3',2003
union all select 2,'D','KC1',2003
union all select 3,'B','KC4',2002
union all select 4,'D','KC3',2002
union all select 4,'D','KC1',2002
goselect 专业=isnull(a.depart,b.depart)
,入学时间=isnull(a.iyear,b.iyear)
,应考人数=isnull(a.应考人数,0)
,实考人数=isnull(b.实考人数,0)
,应考课程数=isnull(b.应考课程数,0)
,实课程数=isnull(b.应考课程数,0)
from(
select depart,iyear,应考人数=count(distinct no)
from student
group by depart,iyear
)a full join(
select depart,iyear,实考人数=count(distinct no),应考课程数=count(*)
from scores
group by depart,iyear
)b on a.depart=b.depart and a.iyear=b.iyear
go--删除测试
drop table student,scores/*--测试结果专业 入学时间 应考人数 实考人数 应考课程数 实课程数
---- ----------- ----------- ----------- ----------- -----------
B 2002 1 1 1 1
D 2002 1 1 2 2
D 2003 1 1 2 2
S 2004 1 1 2 2(所影响的行数为 4 行)
--*/
下面语句是如何关联的啊?
from(
select depart,iyear,应考人数=count(distinct no)
from student
group by depart,iyear
)a full join(
select depart,iyear,实考人数=count(distinct no),应考课程数=count(*)
from scores
group by depart,iyear
)b on a.depart=b.depart and a.iyear=b.iyear
谢谢,