VB acess2000 数据库按月统计问题

select from table where year(日期)='2009' and month（日期)='9'

select from table where left(日期,6)='2009-9'

select from table where 日期 between #2009-8-31# and #2009-10-1#

select a单位名称,a本月销量,a本月销售金额,b本月回款金额,c本年累计销售量,c本年累计销售金额,d本年累计回款金额,

e总销量,e总销售金额,f总回款金额,g年度总销量,g年度总销售金额,h年度总回款金额 from

(select 1 as 编号,单位名称,sum(数量) as 本月销量,sum(金额) as 本月销售金额 from 基本数据 where 销售日期 between '2009-07-26' and '2009-08-25' group by 单位名称) a

inner join

(select 客户名称,sum(回款金额) as 本月回款金额 from 用户回款 where 销售日期 between '2009-07-26' and '2009-08-25' group by 客户名称) b on a单位名称=b客户名称

inner join

(select 单位名称,sum(数量) as 本年累计销售量,sum(金额) as 本年累计销售金额 from 基本数据 where 销售日期 between '2009-01-01' and '2009-12-31' group by 单位名称) c on a单位名称=c单位名称

inner join

(select 客户名称,sum(回款金额) as 本年累计回款金额 from 用户回款 where 销售日期 between '2009-01-01' and '2009-12-31' group by 客户名称) d on a单位名称=d客户名称

inner join

(select 单位名称,sum(数量) as 总销量,sum(金额) as 总销售金额 from 基本数据) e on a单位名称=e单位名称

inner join

(select 客户名称,sum(回款金额) as 总回款金额 from 用户回款) f on a单位名称=f客户名称

inner join

(select 1 as 编号,sum(数量) as 年度总销量,sum(金额) as 年度总销售金额 from 基本数据 where 销售日期 between '2009-01-01' and '2009-12-31') g on a编号=g编号

inner join

(select 1 as 编号,sum(回款金额) as 年度总回款金额 from 用户回款 where 销售日期 between '2009-01-01' and '2009-12-31') h on a编号=h编号

麻烦在于假设一个月一条也没有的情况，最好是先构造月，由此避免这种现象

select convert(varchar(6),日期,112),count(col) from table group by convert(varchar(6),日期,112)

或者构造月实现(以2013年为例)

select amon,count(bcol) from

(

SELECT '201301' AS mon

UNION ALL

SELECT '201302' AS mon

UNION ALL

SELECT '201303' AS mon

UNION ALL

SELECT '201304' AS mon

UNION ALL

SELECT '201305' AS mon

UNION ALL

SELECT '201306' AS mon

UNION ALL

SELECT '201307' AS mon

UNION ALL

SELECT '201308' AS mon

UNION ALL

SELECT '201309' AS mon) a left join table b on amon=convert(varchar(6),日期,112)

group by amon

最简单的方法是就用两列，一列是日期，一列是当日数据

日期 数据值

20170421 100

20170422 300

类似上边这样

这样的话，可以统计你所说的年月日的数据

以上就是关于VB acess2000 数据库按月统计问题全部的内容，包括:VB acess2000 数据库按月统计问题、SQL按时间统计客户的月销售量和年销售量、数据库如何查询每月的数据等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！