当前位置:首页 » 编程语言 » 查询销售总量的sql语言
扩展阅读
webinf下怎么引入js 2023-08-31 21:54:13
堡垒机怎么打开web 2023-08-31 21:54:11

查询销售总量的sql语言

发布时间: 2022-11-03 03:16:57

A. sql查询每件商品销售总量(重点是没有销售记录的商品怎么让它显示为0)

select sum(sale.salenum) as '销售总数' from sale left join goods on goods.gid=sale.gid group by goods.gid

看看left join的用法
left join是左连接,左边的表是主表,右边的表是从表

B. 用一条SQL语句查询所有商品的总进货数和总销售数

总进货数: select inqty from 进货表
总销售数: select salqty from 销售表
这个用SQL语句挺好查的

C. 通过T-SQL语句统计当日销售的总金额。

1、如果你是要统计当日销售总金额,如下:
select sum(piece*price) as '当日销售总额' from 当日销售表
where date='07/5/16'
2、如果你是要统计各商品当日的销售额,如下:
select rq,sno,sname,sum(piece) as piece,sum(piece*price) as '销售额'
from 当日销售表 group by rq,sno,sname

D. 如何用SQL语句查询上个月销售金额跟本月销售金额,所指的每个月是从上个月的25号开始到本月25号

select FTime,sum(jine) as 月销售总量 from (
select dingdan,case when Day(riqi)>25 then
Year(DATEADD(MM,1,riqi)+'-'+Month(DATEADD(MM,1,riqi))
else Year(riqi)+'-'+Month(riqi) end as FTime,jine from 订单表 )x
group by FTime

E. 如何利用sql查询一段时间内每天、没人的销售总额我要详细的sql语句,我可以把表贴出来,求大神指导!!

首先你要先有一张时间表类似作为代码表。(必须为你要查找的全集) time
然后你要有一张姓名表也是全集 name
然后你要根据这两张表查出时间和姓名的全集 (select time.time,name.name from time,name)
先有的这张表查询出销量:(select time,name sum(sale) as zs from test group by time,name )
然后将查出的两张表进行关联就可以查出来了
总SQL应该为: select a.*,nvl(zs,0) zs from (select time.time,name.name from time,name)a,(select time,name sum(sale) as zs from test group by time,name ) b where a.time=b.time(+) and a.name=b.name(+) order by a.time

F. 如何用SQL语句查询出业务员每个月的业绩和总计

IFOBJECT_ID('saleinfo')ISNOTNULLDROPTABLEsaleinfo

--创建模拟表
createtablesaleinfo(
idintidentity(1,1)primarykey,
salesmannvarchar(50)null,
salemonthnvarchar(50)null,
achievementint
)
--载入模拟数据
insertintosaleinfo(salesman,salemonth,achievement)values('A','1月5日',10)
insertintosaleinfo(salesman,salemonth,achievement)values('A','1月7日',20)
insertintosaleinfo(salesman,salemonth,achievement)values('A','2月4日',30)
insertintosaleinfo(salesman,salemonth,achievement)values('A','2月7日',40)
insertintosaleinfo(salesman,salemonth,achievement)values('A','3月2日',50)
insertintosaleinfo(salesman,salemonth,achievement)values('A','3月9日',60)
insertintosaleinfo(salesman,salemonth,achievement)values('B','1月5日',70)
insertintosaleinfo(salesman,salemonth,achievement)values('B','1月7日',80)
insertintosaleinfo(salesman,salemonth,achievement)values('B','2月4日',90)
insertintosaleinfo(salesman,salemonth,achievement)values('B','2月7日',10)
insertintosaleinfo(salesman,salemonth,achievement)values('B','3月2日',20)
insertintosaleinfo(salesman,salemonth,achievement)values('B','3月9日',30)
insertintosaleinfo(salesman,salemonth,achievement)values('C','1月5日',40)
insertintosaleinfo(salesman,salemonth,achievement)values('C','1月7日',50)
insertintosaleinfo(salesman,salemonth,achievement)values('C','2月4日',60)
insertintosaleinfo(salesman,salemonth,achievement)values('C','2月7日',70)
insertintosaleinfo(salesman,salemonth,achievement)values('C','3月2日',80)
insertintosaleinfo(salesman,salemonth,achievement)values('C','3月9日',90)
--显示数据
selectsalesmanas业务员,salemonthas月份,achievementas业绩fromsaleinfo
declare@sqlvarchar(8000)
set@sql='selectsalesmanas业务员'
select@sql=@sql+',sum(caseleft(salemonth,2)when'''+left(salemonth,2)+'''thenachievementelse0end)['+left(salemonth,2)+']'
from(selectdistinctleft(salemonth,2)assalemonthfromsaleinfo)asa
set@sql=@sql+',sum(achievement)as业绩fromsaleinfogroupbysalesman'
exec(@sql)

G. 如何用sql语句统计出一个部门的总销售额

select dept,sum(mny) mny from sale
group by dept

H. 我用sql写了一个查询第一季度的销售总量,却发现多了三行多余的, SQL语句如下:

select Employee as '雇员',
'第一季度' as '季度',
Sum(Case when month(OrderDate) in (1, 2, 3) then Quantity else 0 end) as '销售量'
from dbo.Sales
group by Employee
;
季度字段在销售量里面就体现出来了,你那么写没有意义

I. 试用SQL语句写出下列查询:检索上海市各种商品的销售总数量

select p.Pno,p.pname,sum(s.qty)
from s left join p on s.pno=p.pno left join m on p.Mno=m.Mno
where m.city='上海市'
group by p.Pno,p.pname,p.city,p.color

J. SQL查询各商品的销售情况,列出商品名,商品单价,总价,并按总销售量降序排序

1、首先打开SQL Server Management管理工具,运用Select语句查询出要排序的数据,如下图所示。