表名:SF_BRJSK
姓名 总金额 收费日期
冯坤堂 28.00 2011112507:02:54
金瑞钦 200.00 2011112507:04:47
许秋平 200.00 2011112507:08:39
孙慧聚 40.80 2011112415:02:31
杨建华 18.00 2011112415:18:51
潘兴儒 1080.00 2011112415:21:11
杭治海 28.28 2011112307:08:27
钟瑜烨 453.05 2011112307:38:24
王一丽 0.02 2011112308:01:20
根据以上表内容,计算出每天的人均费用, 该如何用sql语句来表示?
姓名 总金额 收费日期
冯坤堂 28.00 2011112507:02:54
金瑞钦 200.00 2011112507:04:47
许秋平 200.00 2011112507:08:39
孙慧聚 40.80 2011112415:02:31
杨建华 18.00 2011112415:18:51
潘兴儒 1080.00 2011112415:21:11
杭治海 28.28 2011112307:08:27
钟瑜烨 453.05 2011112307:38:24
王一丽 0.02 2011112308:01:20
根据以上表内容,计算出每天的人均费用, 该如何用sql语句来表示?
SELECT
AVG(总金额)
FROM
SF_BRJSK
WHERE
收费日期 between '20111125000000' and '20111126000000'
收费日期为字符类型的话,就直接如下吧
SELECT LEFT(收费日期,8),
AVG(总金额)
FROM SF_BRJSK
GROUP by LEFT(收费日期,8)
--> 测试数据
create TABLE SF_BRJSK (name varchar(50),money decimal(12,2),cdate varchar(50))insert into SF_BRJSK
select
'冯坤堂', 28.00, '2011112507:02:54' union all
select
'金瑞钦', 200.00 ,'2011112507:04:47'union all
select
'许秋平' ,200.00, '2011112507:08:39'union all
select
'孙慧聚' ,40.80, '2011112415:02:31'union all
select
'杨建华' ,18.00, '2011112415:18:51'union all
select
'潘兴儒' ,1080.00, '2011112415:21:11'union all
select
'杭治海' ,28.28, '2011112307:08:27'union all
select
'钟瑜烨' ,453.05, '2011112307:38:24'union all
select
'王一丽' ,0.02, '2011112308:01:20'-->查询sql语句(前两列可以去掉)
select count(*) as rs,sum(money) as je,substring(cdate,0,9) as rq,
sum(money)/count(*) as '每天人均消费' from SF_BRJSK group by substring(cdate,0,9)
FROM SF_BRJSK
GROUP by LEFT(收费日期,8)
根本不用写存储过程,可以把本张表的所有日期内的平均费用算出来