有三个表:
student:
编号 姓名 年级 班级
001 AA 初一 1班
002 BB 初一 1班
..
jieshuzheng:
学生编号 借书证号 最多借书量
001 200801 5
002 200802 5
..
jieshu:
借书证号 借书数量 姓名 书名
200801 1 AA 计算机知识
200801 2 AA 计算机应用
..
说明:jieshuzheng的学生编号和student的编号对应
___________
我要得到的新表:
年级 班级 姓名 借书证号 最多借书量 借书数量
初一 1班 AA 200801 5 3
初一 1班 bb 200802 5 0
....
student:
编号 姓名 年级 班级
001 AA 初一 1班
002 BB 初一 1班
..
jieshuzheng:
学生编号 借书证号 最多借书量
001 200801 5
002 200802 5
..
jieshu:
借书证号 借书数量 姓名 书名
200801 1 AA 计算机知识
200801 2 AA 计算机应用
..
说明:jieshuzheng的学生编号和student的编号对应
___________
我要得到的新表:
年级 班级 姓名 借书证号 最多借书量 借书数量
初一 1班 AA 200801 5 3
初一 1班 bb 200802 5 0
....
from student a
left join jieshuzheng b
on a.编号=b.学生编号
left join (select 借书证号,sum(借书数量) 借书数量 from jieshu group by 借书证号 )c
on b.借书证号=c.借书证号
不过将left join 变为inner join貌似好一点
from jieshuzheng
left join student on jieshuzheng.学生编号=student.编号
left join (select 借书证号,sum(借书数量) 借书数量 from jieshu group by 借书证号 )subqty
on jieshuzheng.借书证号=subqty.借书证号
create table student(编号 varchar(20),姓名 varchar(20),年级 varchar(20), 班级 varchar(20))
insert into student select '001','AA','初一','1班'
insert into student select '002','BB','初一','1班'create table jieshuzheng(学生编号 varchar(20),借书证号 varchar(20),最多借书量 varchar(20))
insert into jieshuzheng select '001','200801',5
insert into jieshuzheng select '002','200802',5create table jieshu(借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into jieshu select '200801',1,'AA','计算机知识'
insert into jieshu select '200801',2,'AA','计算机应用'select a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,isnull(c.借书数量,0) as 借书数量
from student a join jieshuzheng b
on a.编号=b.学生编号 left join (select 姓名,借书证号,sum(借书数量) as 借书数量 from jieshu group by 姓名,借书证号) c
on b.借书证号=c.借书证号年级 班级 姓名 借书证号 最多借书量 借书数量
初一 1班 AA 200801 5 3
初一 1班 BB 200802 5 0
from student a join jieshuzheng b
on a.编号=b.学生编号 left join (select 姓名,借书证号,sum(借书数量) as 借书数量 from jieshu group by 姓名,借书证号) c
on b.借书证号=c.借书证号)tp
得到新表
on a.编号=b.学生编号 left join jieshu c
on b.借书证号=c.借书证号
group by a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量
student.班级,
student.姓名,
jieshuzheng.借书证号,
jieshuzheng.最多借书量,
case when a.借书数量 is null then 0 else a.借书数量 end as 借书数量
FROM student
inner join jieshuzheng on student.编号 = jieshuzheng.编号
left join (select 借书证号,
sum(借书数量) as 借书数量
from jieshu
group by 借书证号) a on a.借书证号 = jieshuzheng.借书证号
insert into @student select '001','AA','初一','1班'
insert into @student select '002','BB','初一','1班'declare @jieshuzheng table (学生编号 varchar(20),借书证号 varchar(20),最多借书量 varchar(20))
insert into @jieshuzheng select '001','200801',5
insert into @jieshuzheng select '002','200802',5declare @jieshu table (借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into @jieshu select '200801',1,'AA','计算机知识'
insert into @jieshu select '200801',2,'AA','计算机应用'select a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,isnull(c.借书数量,0) as 借书数量
from @student a join @jieshuzheng b
on a.编号=b.学生编号 left join (select 姓名,借书证号,sum(借书数量) as 借书数量 from @jieshu group by 姓名,借书证号) c
on b.借书证号=c.借书证号
insert into student select '001','AA','初一','1班'
insert into student select '002','BB','初一','1班'create table jieshuzheng(学生编号 varchar(20),借书证号 varchar(20),最多借书量 varchar(20))
insert into jieshuzheng select '001','200801',5
insert into jieshuzheng select '002','200802',5create table jieshu(借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into jieshu select '200801',1,'AA','计算机知识'
insert into jieshu select '200801',2,'AA','计算机应用'select a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,借书数量 = isnull((select count(*) from jieshu where 借书证号 = b.借书证号),0)
from student a,jieshuzheng b
where a.编号 = b.学生编号drop table student,jieshuzheng,jieshu/*
年级 班级 姓名 借书证号 最多借书量 借书数量
-------------------- -------------------- -------------------- -------------------- -------------------- -----------
初一 1班 AA 200801 5 2
初一 1班 BB 200802 5 0(所影响的行数为 2 行)
*/
insert into student select '001','AA','初一','1班'
insert into student select '002','BB','初一','1班'create table jieshuzheng(学生编号 varchar(20),借书证号 varchar(20),最多借书量 varchar(20))
insert into jieshuzheng select '001','200801',5
insert into jieshuzheng select '002','200802',5create table jieshu(借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into jieshu select '200801',1,'AA','计算机知识'
insert into jieshu select '200801',2,'AA','计算机应用'select a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,借书数量 = isnull((select count(*) from jieshu where 借书证号 = b.借书证号),0)
from student a,jieshuzheng b
where a.编号 = b.学生编号drop table student,jieshuzheng,jieshu/*
年级 班级 姓名 借书证号 最多借书量 借书数量
-------------------- -------------------- -------------------- -------------------- -------------------- -----------
初一 1班 AA 200801 5 2
初一 1班 BB 200802 5 0(所影响的行数为 2 行)
*/金华大哥的可以直接用
insert into student select '001','AA','初一','1班'
insert into student select '002','BB','初一','1班'create table jieshuzheng(学生编号 varchar(20),借书证号 varchar(20),最多借书量 varchar(20))
insert into jieshuzheng select '001','200801',5
insert into jieshuzheng select '002','200802',5create table jieshu(借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into jieshu select '200801',1,'AA','计算机知识'
insert into jieshu select '200801',2,'AA','计算机应用'select a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,借书数量 = isnull((select count(*) from jieshu where 借书证号 = b.借书证号),0)
from student a,jieshuzheng b
where a.编号 = b.学生编号drop table student,jieshuzheng,jieshu
insert into student select '001','AA','初一','1班'
insert into student select '002','BB','初一','1班'create table jieshuzheng(学生编号 varchar(20),借书证号 varchar(20),最多借书量 varchar(20))
insert into jieshuzheng select '001','200801',5
insert into jieshuzheng select '002','200802',5create table jieshu(借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into jieshu select '200801',1,'AA','计算机知识'
insert into jieshu select '200801',2,'AA','计算机应用'select t.年级, t.班级,t.姓名,g.借书证号, g.最多借书量,借书数量=isnull((select count(*) from jieshu where 借书证号=g.借书证号),0)
from student t inner join jieshuzheng g on t.编号=g.学生编号
insert into @student select '001','AA','初一','1班'
insert into @student select '002','BB','初一','1班' declare @jieshuzheng table (学生编号 varchar(20),借书证号 varchar(8),最多借书量 int)
insert into @jieshuzheng select '001','200801',5
insert into @jieshuzheng select '002','200802',5 declare @jieshu table (借书证号 varchar(20), 借书数量 int, 姓名 varchar(20),书名 varchar(20))
insert into @jieshu select '200801',1,'AA','计算机知识'
insert into @jieshu select '200801',2,'AA','计算机应用' select a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,'借书数量'=isnull(c.借书数量,0)
from @student a left join @jieshuzheng b on a.编号=b.学生编号
left join
(select 借书证号,借书数量=sum(借书数量) from @jieshu group by 借书证号)c
on b.借书证号=c.借书证号/*
年级 班级 姓名 借书证号 最多借书量 借书数量
-------- -------- -------- -------- ----------- -----------
初一 1班 AA 200801 5 3
初一 1班 BB 200802 5 0
*/
create table student(编号 varchar(3),姓名 varchar(10),年级 varchar(10),班级 varchar(10))
go
create table jieshuzheng(学生编号 varchar(3),借书证号 varchar(6),最多借书量 int)
go
create table jieshu(借书证号 varchar(6),借书数量 int,姓名 varchar(10),书名 varchar(10))
go
insert into student
select '001','AA','初一','1班' union all
select '002','BB','初一','1班'
insert into jieshuzheng
select '001','200801',5 union all
select '002','200802',5
insert into jieshu
select '200801',1,'AA','计算机知识' union all
select '200801',2,'AA','计算机应用' select * from student
select * from jieshuzheng
select * from jieshuselect a.年级,a.班级,a.姓名,b.借书证号,b.最多借书量,isnull(c.jssl,0) 借书数量 from student a inner join jieshuzheng b on a.编号=b.学生编号 left join
(select 借书证号,sum(借书数量) as jssl from jieshu group by 借书证号) c on b.借书证号=c.借书证号