表结构如下:
分类(分类编号,类名)
书目(ISBN, 书名,作者,出版单位,单价,分类编号)
图书(图书编号,ISBN,是否借出,备注)
问 查询每类图书的册数和平均单价(ISBN相同的不重复计算) 该怎么查?
用游标我能实现,但是老师还没有讲到游标,这是作业。望各位牛人指点指点。
分类(分类编号,类名)
书目(ISBN, 书名,作者,出版单位,单价,分类编号)
图书(图书编号,ISBN,是否借出,备注)
问 查询每类图书的册数和平均单价(ISBN相同的不重复计算) 该怎么查?
用游标我能实现,但是老师还没有讲到游标,这是作业。望各位牛人指点指点。
where 分类.分类编号 = 书目.分类编号 and 图书.ISBN = 书目.ISBN
group by 类名
from 分类 as a
,( select ISBN,avg(单价) as 平均单价,分类编号
from 书目
group by 分类编号
) as b
,图书 as c
where a.分类编号 = b.分类编号 and b.ISBN = c.ISBN
group by a.类名
-- 如果不同 isbn 的书价格不同
select
(select 类名 from 分类 where 分类编号=t.分类编号) 类名,
count(1) 册数,
avg(distinct 单价) 平均单价
from 书目 t
group by 分类编号;-- 如果不同 isbn 的书可能有相同的价格
select
(select 类名 from 分类 where 分类编号=t.分类编号) 类名,
count(1) 册数,
(select avg(单价) from
(select distinct ISBN,单价 from 书目 where 分类编号=t.分类编号) t) 平均单价
from 书目 t
group by 分类编号;
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
--表的结构
--分类(分类编号,类名)
-- 1 数据库
-- 2 计算机网络--书目(ISBN, 书名, 作者, 出版单位, 单价,分类编号)
--978-7-04-019583-2 网络程序设计 王艳平 人民邮电出版社 50 2
--978-7-04-019583-4 数据库设计 张健 西科大出版社 100 1
--978-7-04-019583-5 数据库系统概论 王珊 高等教育出版社 30 1
--978-7-04-019583-8 计算机网络 谢希仁 电子工业出版社 30 2
--978-7-04-019583-9 dkfdkfjdkl NULL NULL 100 2--图书(图书编号, ISBN, 是否借出)
-- 00001 978-7-04-019583-5 False
-- 00002 978-7-04-019583-5 False
-- 00003 978-7-04-019583-4 False
-- 00004 978-7-04-019583-4 False
-- 00005 978-7-04-019583-4 False
-- 00006 978-7-04-019583-2 False
-- 00007 978-7-04-019583-8 False问题如下:查询每类图书中多于10册的册数和平均单价(ISBN相同的不重复计算)
期望结果
类名 册数 平均单价
-------- -------- ----------
数据库 5 65
计算机网络 2 60
--978-7-04-019583-8 计算机网络 谢希仁 电子工业出版社 30 2-- 00006 978-7-04-019583-2 False
-- 00007 978-7-04-019583-8 False计算机网络 2 60计算机网络的平均为什么不是40?
insert into 分类 values(1 , '数据库')
insert into 分类 values(2 , '计算机网络')
create table 书目(ISBN varchar(30),书名 varchar(20),作者 varchar(20), 出版单位 varchar(20),单价 int,分类编号 int)
insert into 书目 values('978-7-04-019583-2', '网络程序设计' , '王艳平', '人民邮电出版社', 50 , 2)
insert into 书目 values('978-7-04-019583-4', '数据库设计' , '张健' , '西科大出版社' , 100 , 1)
insert into 书目 values('978-7-04-019583-5', '数据库系统概论', '王珊' , '高等教育出版社', 30 , 1)
insert into 书目 values('978-7-04-019583-8', '计算机网络' , '谢希仁', '电子工业出版社', 30 , 2)
insert into 书目 values('978-7-04-019583-9', 'dkfdkfjdkl' , NULL , NULL ,100 , 2)
create table 图书(图书编号 varchar(30), ISBN varchar(30), 是否借出 varchar(30))
insert into 图书 values('00001' , '978-7-04-019583-5' , 'False')
insert into 图书 values('00002' , '978-7-04-019583-5' , 'False')
insert into 图书 values('00003' , '978-7-04-019583-4' , 'False')
insert into 图书 values('00004' , '978-7-04-019583-4' , 'False')
insert into 图书 values('00005' , '978-7-04-019583-4' , 'False')
insert into 图书 values('00006' , '978-7-04-019583-2' , 'False')
insert into 图书 values('00007' , '978-7-04-019583-8' , 'False')
goselect t1.* , t2.单价 from
(
select m.分类编号,m.类名 , count(1) 册数 from 分类 m,书目 n,图书 o
where m.分类编号 = n.分类编号 and n.ISBN = o.ISBN
group by m.分类编号 , m.类名
) t1,
(
select m.分类编号,m.类名 , avg(n.单价) 单价 from 分类 m,书目 n
where m.分类编号 = n.分类编号 and exists(select 1 from 图书 o where n.ISBN = o.ISBN)
group by m.分类编号,m.类名
) t2
where t1.类名 = t2.类名
order by t1.分类编号drop table 分类,书目,图书/*
分类编号 类名 册数 单价
----------- -------------------- ----------- -----------
1 数据库 5 65
2 计算机网络 2 40(所影响的行数为 2 行)*/
select
(select [类名] from [分类] where [分类编号]=t2.[分类编号]) [类名],
count(1) [册数],
(select avg([单价]) from
(select distinct ISBN,[单价]
from [书目]
where [分类编号]=t2.[分类编号]) t
) [平均单价]
from [图书] t1 inner join [书目] t2 on t1.ISBN=t2.ISBN
group by [分类编号];
insert into 分类 values(1 , '数据库')
insert into 分类 values(2 , '计算机网络')
create table 书目(ISBN varchar(30),书名 varchar(20),作者 varchar(20), 出版单位 varchar(20),单价 int,分类编号 int)
insert into 书目 values('978-7-04-019583-2', '网络程序设计' , '王艳平', '人民邮电出版社', 50 , 2)
insert into 书目 values('978-7-04-019583-4', '数据库设计' , '张健' , '西科大出版社' , 100 , 1)
insert into 书目 values('978-7-04-019583-5', '数据库系统概论', '王珊' , '高等教育出版社', 30 , 1)
insert into 书目 values('978-7-04-019583-8', '计算机网络' , '谢希仁', '电子工业出版社', 30 , 2)
insert into 书目 values('978-7-04-019583-9', 'dkfdkfjdkl' , NULL , NULL ,100 , 2)
create table 图书(图书编号 varchar(30), ISBN varchar(30), 是否借出 varchar(30))
insert into 图书 values('00001' , '978-7-04-019583-5' , 'False')
insert into 图书 values('00002' , '978-7-04-019583-5' , 'False')
insert into 图书 values('00003' , '978-7-04-019583-4' , 'False')
insert into 图书 values('00004' , '978-7-04-019583-4' , 'False')
insert into 图书 values('00005' , '978-7-04-019583-4' , 'False')
insert into 图书 values('00006' , '978-7-04-019583-2' , 'False')
insert into 图书 values('00007' , '978-7-04-019583-8' , 'False')
goselect t1.* , t2.单价 from
(
select m.分类编号,m.类名 , count(1) 册数 from 分类 m,书目 n,图书 o
where m.分类编号 = n.分类编号 and n.ISBN = o.ISBN
group by m.分类编号 , m.类名
) t1,
(
select m.分类编号,m.类名 , avg(n.单价) 单价 from 分类 m,书目 n
where m.分类编号 = n.分类编号
group by m.分类编号,m.类名
) t2
where t1.类名 = t2.类名
order by t1.分类编号drop table 分类,书目,图书/*
分类编号 类名 册数 单价
----------- -------------------- ----------- -----------
1 数据库 5 65
2 计算机网络 2 60(所影响的行数为 2 行)
*/