在图书销售系统数据库中有图书类别表盒图书表如下:
图书类别:category
id catname
1 计算机
2 外语
3 经济管理图书表book
id catid bookName saleCount
1 1 数据库设计 90
2 1 java 65
3 2 应用泛读 56
4 2 应于口语 120其中catid是category的外键salecount是销售数量
要查询平均数量大约80的图书类别:用一条sql语句 怎么写?
图书类别:category
id catname
1 计算机
2 外语
3 经济管理图书表book
id catid bookName saleCount
1 1 数据库设计 90
2 1 java 65
3 2 应用泛读 56
4 2 应于口语 120其中catid是category的外键salecount是销售数量
要查询平均数量大约80的图书类别:用一条sql语句 怎么写?
where a.saleCount>80连接查询
select catname from category inner join book on category.id=book.catid group by catname having sum(saleCount)>80
某图书类别的书平均销量大于80,用这个:select catname from category inner join book on category.id=book.catid group by catname having sum(saleCount)/count(saleCount)>80
drop table book
if exists(select * from sys.objects where object_id=object_id('category'))
drop table category create table category
(
id int not null primary key identity(1,1),
catname varchar(100) not null
)
create table book
(
id int not null primary key identity(1,1),
catid int not null,
bookName varchar(100) not null,
saleCount float not null
constraint fk_catid
foreign key(catid)
references category(id)
)insert into category values('计算机')
insert into category values('外语')
insert into category values('经济管理')insert into book values(1,'数据库设计',90)
insert into book values(1,'java',65)
insert into book values(2,'应用泛读',56)
insert into book values(2,'应于口语',120)select * from category
select * from bookselect c.catname,avg(b.saleCount) as categoryAvgSaleCount from category c
inner join book b on c.id=b.catid
group by c.catname
计算机 77.5
外语 88
drop table book
if exists(select * from sys.objects where object_id=object_id('category'))
drop table category create table category
(
id int not null primary key identity(1,1),
catname varchar(100) not null
)
create table book
(
id int not null primary key identity(1,1),
catid int not null,
bookName varchar(100) not null,
saleCount float not null
constraint fk_catid
foreign key(catid)
references category(id)
)insert into category values('计算机')
insert into category values('外语')
insert into category values('经济管理')insert into book values(1,'数据库设计',90)
insert into book values(1,'java',65)
insert into book values(2,'应用泛读',56)
insert into book values(2,'应于口语',120)select * from category
select * from bookselect c.catname,avg(b.saleCount) as categoryAvgSaleCount from category c
inner join book b on c.id=b.catid
group by c.catname
having avg(b.saleCount)>=80
外语 88