--1统计每个销售人员的销售书籍的总量,总码洋,总实洋,显示内容包括销售人员姓名,书籍名称,销售数量,总码洋,及总实洋--错误
select distinct username as 销售人员姓名,bookname as 书籍姓名,quantity as 销售数量,sum(salesprice*quantity)as 总码洋,sum(salesprice*quantity*discount)as 总实洋
from userinfo join sales on userinfo.userid=sales.userid
join bookinfo on sales.bookid=bookinfo.bookid
group by username,bookname,quantity,(salesprice*quantity),(salesprice*quantity*discount)--2统计各个出版社的销售情况,包括销售总量,销售码洋,销售实洋,出版社,并按照销售数量由高到低显示。--错误
select sum(quantity) as 销售总量,(salesprice*quantity) as 销售码洋,(salesprice*quantity*discount) as 销售实洋,publishername as 出版社,bookname as 书名
from sales join bookinfo
on sales.bookid=bookinfo.bookid
join publisher on
bookinfo.publisherid=publisher.publisherid
group by quantity,(salesprice*quantity),(salesprice*quantity*discount),publishername,publishername,
bookname
order by quantity desc 这两题怎么改?
select distinct username as 销售人员姓名,bookname as 书籍姓名,quantity as 销售数量,sum(salesprice*quantity)as 总码洋,sum(salesprice*quantity*discount)as 总实洋
from userinfo join sales on userinfo.userid=sales.userid
join bookinfo on sales.bookid=bookinfo.bookid
group by username,bookname,quantity,(salesprice*quantity),(salesprice*quantity*discount)--2统计各个出版社的销售情况,包括销售总量,销售码洋,销售实洋,出版社,并按照销售数量由高到低显示。--错误
select sum(quantity) as 销售总量,(salesprice*quantity) as 销售码洋,(salesprice*quantity*discount) as 销售实洋,publishername as 出版社,bookname as 书名
from sales join bookinfo
on sales.bookid=bookinfo.bookid
join publisher on
bookinfo.publisherid=publisher.publisherid
group by quantity,(salesprice*quantity),(salesprice*quantity*discount),publishername,publishername,
bookname
order by quantity desc 这两题怎么改?
解决方案 »
- 请老师讲讲left joint, right joint, inner joint 的区别吧
- 求一个简单的联级删除
- 插入动态变量表问题
- PB10.5连接sql server 2000死锁
- 如何让执行过的sql不驻留再cache中?
- VS2008连接SQLserver2000和SQLserver2005问题
- 一个表里面由多个个用户名,每个用户名也有多个记录,但是每个记录的记录时间是不一样的,问题就是要查找这样的记录
- 考过程序员的各位前辈给点意见
- 如何用SQL创建带有命名约束的表
- 该如何更新数据?请大家帮帮忙
- SQL 货物入库出库数据统计,原则[先入库的先出库]!~~~~
- sql2000中同一用户,每一个连接是不是独立的一个会话?
sum(b.salesprice*b.quantity)as 总码洋,sum(b.salesprice*b.quantity*b.discount)as 总实洋
from userinfo a join sales b on a.userid=b.userid
join bookinfo c on b.bookid=c.bookid
group by a.username,c.booknameselect sum(a.quantity) as 销售总量,sum(a.salesprice*a.quantity) as 销售码洋,
sum(a.salesprice*a.quantity*a.discount) as 销售实洋,c.publishername as 出版社
from sales a join bookinfo b
on a.bookid=b.bookid
join publisher c on
b.publisherid=c.publisherid
group by c.publishername
order by 1 desc
bookname as 书籍姓名,sum(quantity) as 销售总数量,
sum(salesprice*quantity)as 总码洋,sum(salesprice*quantity*discount)as 总实洋
from userinfo join sales on userinfo.userid=sales.userid
join bookinfo on sales.bookid=bookinfo.bookid
group by username,bookname--quantity应该是合计
select
sum(quantity) as 销售总量,
sum(salesprice*quantity) as 销售码洋,
sum(salesprice*quantity*discount) as 销售实洋,
publishername as 出版社,bookname as 书名
from sales join bookinfo
on sales.bookid=bookinfo.bookid
join publisher on
bookinfo.publisherid=publisher.publisherid
group by publishername,
bookname
order by 销售总量 desc
[/code]