天道酬勤这么早就来啦,厉害厉害。呵呵楼主参考下面: declare @temp table(xuehao int,kecheng varchar(1000)) insert into @temp(xuehao) select 学号 from xs order by 学号 declare @xuehao --declare @kecheng declare xs_cur cursor for select 学号 from xs order by 学号 open xs_cur fetch next from xs_cur into @xuehao while @@fetch_status=0 begin declare kc_cur cursor for select 课程号 from xs_kc where 学号=@xuehao order by 课程号 fetch next from kc_cur into @kecheng while @@fetch_status=0 begin update @temp set kecheng=kecheng+cast(@kecheng as varchar(N))--N为楼主的课程号的位数 where @temp.xuehao=@xuehao end deallocate kc_cur end deallocate xs_curselect xs.姓名,c.kecheng from (select xuehao,kecheng from @temp group by kecheng having count(xuehao)>1 order by kecheng )c right join xs on c.xuehao=xs.学号
晕,修改下 declare @temp table(xuehao int,kecheng varchar(1000)) insert into @temp(xuehao) select 学号 from xs order by 学号 declare @xuehao int declare @kecheng varchar(N)--N为楼主的课程号的位数 declare xs_cur cursor for select 学号 from xs order by 学号 open xs_cur fetch next from xs_cur into @xuehao while @@fetch_status=0 begin declare kc_cur cursor for select 课程号 from xs_kc where 学号=@xuehao order by 课程号 open kc_cur fetch next from kc_cur into @kecheng while @@fetch_status=0 begin update @temp set kecheng=kecheng+cast(@kecheng as varchar(N))--N为楼主的课程号的位数 where @temp.xuehao=@xuehao fetch next from kc_cur into @kecheng end deallocate kc_cur fetch next from xs_cur into @xuehao end deallocate xs_curselect xs.姓名,c.kecheng from (select xuehao,kecheng from @temp group by kecheng having count(xuehao)>1 order by kecheng )c right join xs on c.xuehao=xs.学号
楼主需要多个嵌套查询 --xs --学号、姓名、性别 --xs_kc --学号、课程号、成绩declare @xs table(学号 int,姓名 varchar(10),性别 varchar(2)) insert @xs select 1,'a','男' union all select 2,'b','女' union all select 3,'c','男' union all select 4,'d','男' union all select 5,'e','女' declare @xs_kc table(学号 int,课程号 varchar(10),成绩 int) insert @xs_kc select 1,'语文',80 union all select 1,'数学',90 union all select 2,'语文',70 union all select 3,'语文',90 union all select 3,'数学',80 union all select 4,'语文',80 union all select 4,'数学',80 union all select 4,'物理',80 union all select 5,'物理',80 union all select 5,'化学',80 union all select 5,'数学',80 select xs.*,课程号,成绩 from @xs_kc tb inner join @xs xs on tb.学号=xs.学号 where not exists (select 1 from (select *,记录=(select count(*) from (select 学号,学号数=(select count(*) from @xs_kc where 学号=kc.学号), 课程号,课堂数=(select count(*) from @xs_kc where 课程号=kc.课程号),成绩 from @xs_kc kc)kc1 where 课堂数=kc.课堂数 and 课程号=kc.课程号 and 学号数=kc.学号数 and 学号!=kc.学号) from (select 学号,学号数=(select count(*) from @xs_kc where 学号=kc.学号), 课程号,课堂数=(select count(*) from @xs_kc where 课程号=kc.课程号),成绩 from @xs_kc kc)kc)ta where 学号=tb.学号 and 记录<1) (所影响的行数为 5 行) (所影响的行数为 11 行)学号 姓名 性别 课程号 成绩 ----------- ---------- ---- ---------- ----------- 1 a 男 语文 80 1 a 男 数学 90 3 c 男 语文 90 3 c 男 数学 80(所影响的行数为 4 行)
上面的好象写复杂了哦,用下面语句好象就行了select 姓名 from xs where 学号=(select a.学号 from xs_kc as a inner join xs_kc as b on a.课程号=b.课程号 where a.学号<>b.学号) go
declare @temp table(xuehao int,kecheng varchar(1000))
insert into @temp(xuehao) select 学号 from xs order by 学号
declare @xuehao
--declare @kecheng
declare xs_cur cursor for select 学号 from xs order by 学号
open xs_cur
fetch next from xs_cur into @xuehao
while @@fetch_status=0
begin
declare kc_cur cursor for select 课程号 from xs_kc where 学号=@xuehao order by 课程号
fetch next from kc_cur into @kecheng
while @@fetch_status=0
begin
update @temp
set kecheng=kecheng+cast(@kecheng as varchar(N))--N为楼主的课程号的位数
where @temp.xuehao=@xuehao
end
deallocate kc_cur
end
deallocate xs_curselect xs.姓名,c.kecheng
from (select xuehao,kecheng from @temp group by kecheng having count(xuehao)>1 order by kecheng
)c right join xs on c.xuehao=xs.学号
declare @temp table(xuehao int,kecheng varchar(1000))
insert into @temp(xuehao) select 学号 from xs order by 学号
declare @xuehao int
declare @kecheng varchar(N)--N为楼主的课程号的位数
declare xs_cur cursor for select 学号 from xs order by 学号
open xs_cur
fetch next from xs_cur into @xuehao
while @@fetch_status=0
begin
declare kc_cur cursor for select 课程号 from xs_kc where 学号=@xuehao order by 课程号
open kc_cur
fetch next from kc_cur into @kecheng
while @@fetch_status=0
begin
update @temp
set kecheng=kecheng+cast(@kecheng as varchar(N))--N为楼主的课程号的位数
where @temp.xuehao=@xuehao
fetch next from kc_cur into @kecheng
end
deallocate kc_cur
fetch next from xs_cur into @xuehao
end
deallocate xs_curselect xs.姓名,c.kecheng
from (select xuehao,kecheng from @temp group by kecheng having count(xuehao)>1 order by kecheng
)c right join xs on c.xuehao=xs.学号
--xs
--学号、姓名、性别
--xs_kc
--学号、课程号、成绩declare @xs table(学号 int,姓名 varchar(10),性别 varchar(2))
insert @xs
select 1,'a','男' union all
select 2,'b','女' union all
select 3,'c','男' union all
select 4,'d','男' union all
select 5,'e','女'
declare @xs_kc table(学号 int,课程号 varchar(10),成绩 int)
insert @xs_kc
select 1,'语文',80 union all
select 1,'数学',90 union all
select 2,'语文',70 union all
select 3,'语文',90 union all
select 3,'数学',80 union all
select 4,'语文',80 union all
select 4,'数学',80 union all
select 4,'物理',80 union all
select 5,'物理',80 union all
select 5,'化学',80 union all
select 5,'数学',80 select xs.*,课程号,成绩
from @xs_kc tb inner join @xs xs on tb.学号=xs.学号
where
not exists
(select 1
from (select *,记录=(select count(*) from (select 学号,学号数=(select count(*) from @xs_kc where 学号=kc.学号),
课程号,课堂数=(select count(*) from @xs_kc where 课程号=kc.课程号),成绩
from @xs_kc kc)kc1 where 课堂数=kc.课堂数 and 课程号=kc.课程号 and 学号数=kc.学号数 and 学号!=kc.学号)
from (select 学号,学号数=(select count(*) from @xs_kc where 学号=kc.学号),
课程号,课堂数=(select count(*) from @xs_kc where 课程号=kc.课程号),成绩
from @xs_kc kc)kc)ta
where 学号=tb.学号 and 记录<1)
(所影响的行数为 5 行)
(所影响的行数为 11 行)学号 姓名 性别 课程号 成绩
----------- ---------- ---- ---------- -----------
1 a 男 语文 80
1 a 男 数学 90
3 c 男 语文 90
3 c 男 数学 80(所影响的行数为 4 行)
where 学号=(select a.学号 from xs_kc as a
inner join xs_kc as b
on a.课程号=b.课程号
where a.学号<>b.学号)
go