select sum(分数) from tblsalr group by datepart(month,日期) where 日期 between #2003-1-1# and #2003-12-31#
select sum(分数),month(日期) as cmonth from tblsalr group by month(日期) where 日期 between '2003-1-1' and '2003-12-31'
select sum(分数) from tblsalr group by datepart(month,日期) where datediff(day,日期 ,'2003-1-1') <=0 and datediff(day,日期 ,'2003-12-31') >=0
下面是在sqlserver的查询分析器皿里的测试: declare @table1 table (分数 int ,日期 datetime) insert @table1 values (12,'2003-1-2') insert @table1 values (456,'2003-10-2') insert @table1 values (543,'2003-6-2') insert @table1 values (34,'2003-4-2') insert @table1 values (54,'2003-2-2') insert @table1 values (23,'2003-7-2') insert @table1 values (432,'2003-3-2') insert @table1 values (23,'2003-2-2') insert @table1 values (54,'2003-9-2') insert @table1 values (78,'2003-12-2')SELECT month(日期) ,SUM(分数) FROM @TABLE1 GROUP BY month(日期) ORDER BY month(日期)
select sum(分数) from tblsalr group by month(日期) where 日期 between '2003-1-1' and '2003-12-31'你的是access吧!
你的是access? select sum(分数) from tblsalr group by month(日期) where 日期 between '2003-1-1' and '2003-12-31'
select sum(分数) from tblsalr where datediff(day,日期 ,'2003-1-1') <=0 and datediff(day,日期 ,'2003-12-31') >=0 group by datepart(month,日期)
declare @table1 table (分数 int ,日期 datetime) insert @table1 values (12,'2003-1-2') insert @table1 values (456,'2003-10-2') insert @table1 values (543,'2003-6-2') insert @table1 values (34,'2003-4-2') insert @table1 values (54,'2003-2-2') insert @table1 values (23,'2003-7-2') insert @table1 values (432,'2003-3-2') insert @table1 values (23,'2003-2-2') insert @table1 values (54,'2003-9-2') insert @table1 values (78,'2003-12-2')SELECT month(日期) ,SUM(分数) FROM @TABLE1 where 日期 between '2003-2-2'and '2004-1-1' GROUP BY month(日期) ORDER BY month(日期)
我用的是access数据库,日期格式是加#,关键是分组时怎么取得“月”?select sum(分数) from tblsalr group by datepart(month,日期) 还是有错啊1
问题已经解决了,如下(access数据库): SELECT month(日期)as 月份,Sum(分数) FROM indata where 日期 between #2003-1-1# and #2003-12-31# GROUP BY Month(日期)GROUP BY必须在where子句之后。 谢谢大家!
to: jakieliu 到Access版問問吧,SQL server的代碼在access中不一定能用。
--以为你是SQL ,不过SQL中的,我也写错了,一样的顺序有问题:select sum(分数) from tblsalr where 日期 between '2003-1-1' and '2003-12 group by month(日期)
不好意思,各位: 一上来就看到语法有明显错误,不过好象上来迟了,人家又更正了 select sum(分数) from tblsalr group by month(日期) !!!! where 日期 between '2003-1-1' and '2003-12-31'|||||||||||||||||| select sum(分数) from tblsalr where 日期 between '2003-1-1' and '2003-12-31' group by month(日期) 这样的结构才合理嘛
from tblsalr
group by datepart(month,日期)
where 日期 between #2003-1-1# and #2003-12-31#
from tblsalr
group by datepart(month,日期)
where datediff(day,日期 ,'2003-1-1') <=0
and datediff(day,日期 ,'2003-12-31') >=0
declare @table1 table (分数 int ,日期 datetime)
insert @table1 values (12,'2003-1-2')
insert @table1 values (456,'2003-10-2')
insert @table1 values (543,'2003-6-2')
insert @table1 values (34,'2003-4-2')
insert @table1 values (54,'2003-2-2')
insert @table1 values (23,'2003-7-2')
insert @table1 values (432,'2003-3-2')
insert @table1 values (23,'2003-2-2')
insert @table1 values (54,'2003-9-2')
insert @table1 values (78,'2003-12-2')SELECT month(日期) ,SUM(分数) FROM @TABLE1
GROUP BY month(日期) ORDER BY month(日期)
group by month(日期)
where 日期 between '2003-1-1' and '2003-12-31'你的是access吧!
select sum(分数) from tblsalr
group by month(日期)
where 日期 between '2003-1-1' and '2003-12-31'
from tblsalr
where datediff(day,日期 ,'2003-1-1') <=0
and datediff(day,日期 ,'2003-12-31') >=0
group by datepart(month,日期)
insert @table1 values (12,'2003-1-2')
insert @table1 values (456,'2003-10-2')
insert @table1 values (543,'2003-6-2')
insert @table1 values (34,'2003-4-2')
insert @table1 values (54,'2003-2-2')
insert @table1 values (23,'2003-7-2')
insert @table1 values (432,'2003-3-2')
insert @table1 values (23,'2003-2-2')
insert @table1 values (54,'2003-9-2')
insert @table1 values (78,'2003-12-2')SELECT month(日期) ,SUM(分数) FROM @TABLE1
where 日期 between '2003-2-2'and '2004-1-1'
GROUP BY month(日期) ORDER BY month(日期)
from tblsalr
group by datepart(month,日期) 还是有错啊1
SELECT month(日期)as 月份,Sum(分数)
FROM indata
where 日期 between #2003-1-1# and #2003-12-31#
GROUP BY Month(日期)GROUP BY必须在where子句之后。
谢谢大家!
到Access版問問吧,SQL server的代碼在access中不一定能用。
where 日期 between '2003-1-1' and '2003-12
group by month(日期)
select sum(分数) from tblsalr
group by month(日期) !!!!
where 日期 between '2003-1-1' and '2003-12-31'||||||||||||||||||
select sum(分数) from tblsalr
where 日期 between '2003-1-1' and '2003-12-31'
group by month(日期)
这样的结构才合理嘛