tbl_stu:学生成绩表
id(int) classID(int) english(float)
1 1 50
2 1 70
3 2 78.2
tbl_class:班级表
classID className
1 初一(1)班
2 初一(2)班查出来的是这个样子的:
classID className argEnglish(英语平均分)
1 初一(1)班 60
2 初一(2)班 78.2
id(int) classID(int) english(float)
1 1 50
2 1 70
3 2 78.2
tbl_class:班级表
classID className
1 初一(1)班
2 初一(2)班查出来的是这个样子的:
classID className argEnglish(英语平均分)
1 初一(1)班 60
2 初一(2)班 78.2
INSERT INTO @ta
SELECT 1, 1, 50
UNION ALL SELECT 2, 1, 70
UNION ALL SELECT 3, 2, 78.2DECLARE @tb TABLE([classID] int, [className] varchar(10))
INSERT INTO @tb
SELECT 1, '初一(1)班'
UNION ALL SELECT 2, '初一(2)班'
SELECT A.classID, B.className, A.english
FROM (SELECT classID, AVG(english) AS english FROM @ta GROUP BY classID)A
INNER JOIN @tb B ON A.classID = B.classID
现在初一(3)班没显示,就算没成绩也想让他显示NULL或0的要怎么写?
insert into @tbl_stu
select 1, 1, 50
union all
select 2 , 1 , 70
union all
select 3 , 2 , 78.2
select * from @tbl_stu
declare @tbl_class table(classID int, className nvarchar(50))
insert into @tbl_class
select 1 , N'初一(1)班'
union all
select 2 ,N'初一(2)班'
union all
select 3,N'初一(3)班'
select * from @tbl_classselect C.classID,C.className,Avg(Isnull(S.english,0))
from
@tbl_class C
Left Join
@tbl_stu S
On C.classID=S.classID
Group by C.classID,C.className
(select classid,average(english)
from tbl_stu) a
left outer join tdl_class b on a.classid=b.ClassID
insert tbl_stu
select 1, 1, 50
union all select 2, 1, 70
union all select 3, 2, 78.2create table tbl_class(classID int, className nvarchar(10))
insert tbl_class
select 1, '初一(1)班'
union all select 2, '初一(2)班'select tbl_class.*, argEnglish from tbl_class
inner join (
select classID, cast(avg(english) as decimal(10, 2)) as argEnglish from tbl_stu group by classID
)tmp on tbl_class.classID=tmp.classIDdrop table tbl_stu
drop table tbl_class--result
classID className argEnglish
----------- ---------- ------------
1 初一(1)班 60.00
2 初一(2)班 78.20(2 row(s) affected)
if object_id('pubs..tbl_stu') is not null
drop table tbl_stu
go
create table tbl_stu
(
id int,
classid int,
english float
)
insert into tbl_stu(id,classid,english) values(1,1,50)
insert into tbl_stu(id,classid,english) values(2,1,70)
insert into tbl_stu(id,classid,english) values(3,2,78.2)
go
if object_id('pubs..tbl_class') is not null
drop table tbl_class
go
create table tbl_class
(
classid int,
className varchar(10)
)
insert into tbl_class(classid,classname) values(1,'初一(1)班')
insert into tbl_class(classid,classname) values(2,'初一(2)班')
go
--统计语句
select a.classid , a.classname , b.argenglish from tbl_class a,
(select classid , cast(avg(english) as decimal(18,2)) argenglish from tbl_stu group by classid) b
where a.classid = b.classid
--删除表
drop table tbl_stu
drop table tbl_class
--结果
classid classname argenglish
----------- ---------- --------------------
1 初一(1)班 60.00
2 初一(2)班 78.20(所影响的行数为 2 行)
from tbl_stu a, tbl_class b
where a.classID = b.classID
group by a.classID, className