不算難題,也不算经典:PSELECT SUM(INCOME) From TABLENAME GROUP BY CONVERT(Varchar(7),dt,126)
select sum(income) from table1 group by datepart(yy,dt),datepart(mm,dt)
呵呵,這也叫難麼?select sum(income) from table1 group by convert(varchar(6),dt,112) order by convert(varchar(6),dt,112)
select sum(income) as a,CONVERT(Varchar(7),dt,126) as b from table1 group by CONVERT(Varchar(7),dt,126) order by CONVERT(Varchar(7),dt,126)
select convert(char(6),dt,112) as mon ,sum(income) as income into #tmp from tablename group by convert(char(6),dt,112) select a.mon,sum(b.income) from #tmp a,#tmp b where b.mon<=a.mon group by a.mon ----------------------------------------------------------- 怎么用一句sql比较有效率地完成呢?
------ Declare @p table (income decimal(15,1) ,dt datetime) insert into @p values (10.1,'2002/02/01') insert into @p values (10.2,'2002/02/23') insert into @p values (10.3,'2002/04/16') Select dt,(SELECT SUM(Income) from (SELECT convert(varchar(7),dt,126) as dt ,SUM(income) as Income from @p group by convert(varchar(7),dt,126)) tmp where tmp.dt<=p.dt) as Income from ( SELECT convert(varchar(7),dt,126) as dt ,SUM(income) as Income from @p group by convert(varchar(7),dt,126) ) as p
嗯~~~太雜了,肯定還有更好的方法...思考ing...
select (cast(年度 as varchar(4))+'.'+cast(月份 as varchar(2))) as 月份,汇总 from ( select year(dt) as 年度,month(dt) as 月份,sum(income) as 汇总 from table1 group by year(dt),month(dt) ) tmp1 同意:icevi(按钮工厂)
这个应该可以,比在select里用子查询效率会好很多。 select c1.d,sum(c2.s) from (select distinct convert(char(6),dt,112) d from table1) c1, (select convert(char(6),dt,112) d, sum(income) s from table1 group by convert(char(6),dt,112) ) c2 where c1.d >= c2.d group by c1.d order by c1.d
老兄我怎么不能运行你们的程序.错误提示为:The sum or average aggregate operation cannot take a nvarchar data type as an argument.
from table1
group by convert(varchar(6),dt,112)
order by convert(varchar(6),dt,112)
from table1
group by CONVERT(Varchar(7),dt,126)
order by CONVERT(Varchar(7),dt,126)
into #tmp from tablename group by convert(char(6),dt,112)
select a.mon,sum(b.income) from #tmp a,#tmp b where b.mon<=a.mon
group by a.mon
-----------------------------------------------------------
怎么用一句sql比较有效率地完成呢?
Declare @p table (income decimal(15,1) ,dt datetime)
insert into @p values (10.1,'2002/02/01')
insert into @p values (10.2,'2002/02/23')
insert into @p values (10.3,'2002/04/16')
Select dt,(SELECT SUM(Income) from
(SELECT convert(varchar(7),dt,126) as dt ,SUM(income) as Income from @p group by convert(varchar(7),dt,126)) tmp where tmp.dt<=p.dt) as Income
from
(
SELECT convert(varchar(7),dt,126) as dt ,SUM(income) as Income from @p group by convert(varchar(7),dt,126)
) as p
(
select year(dt) as 年度,month(dt) as 月份,sum(income) as 汇总
from table1 group by year(dt),month(dt)
) tmp1
同意:icevi(按钮工厂)
剛才net_steven說的是不是你想要的?
select c1.d,sum(c2.s)
from (select distinct convert(char(6),dt,112) d from table1) c1,
(select convert(char(6),dt,112) d,
sum(income) s
from table1
group by convert(char(6),dt,112) ) c2
where c1.d >= c2.d
group by c1.d
order by c1.d
N_Chow(Red.pan_Nipsan)
我已经圆满完成任务了,但是“吃素的狼”兄说的有道理!我想他说的情况在很多情况下会非常有用的,比如我想知道一年的产值,画一条曲线,虽然不用SQL 语句也能实现,但是要是能用的话何乐而不为呢?你说是吗?
他说的意思是:
回复人: net_steven(吃素的狼) ( ) 信誉:100 2002-04-18 17:44:00 得分:0
错了,我要的结果应该是:
20.3 //2002.2月份数据
30.6 //2002.4月份数据好象是从元月累计到4月,呵呵