Id price time
----------- ----------- -----------------------
...
1 87 2013-01-08 16:31:15.000
2 140 2013-01-09 09:05:14.000
3 44 2013-02-09 09:05:14.000
4 12 2013-03-09 09:05:14.000
5 31 2013-04-09 09:05:14.000
6 525 2013-05-09 09:05:14.000
7 188 2013-06-13 09:12:46.000
...
...
...
我想查出详细+每个月总和+到这个月总和,有什么好方法,求大神编号 价格 时间
----------- ----------- -----------------------
1 87 2013-01-08 16:31:15.000
2 140 2013-01-09 09:05:14.000
本月小结:227 2013-01-31
累计:227 2013-01-31
3 44 2013-02-09 09:05:14.000
本月小结:44 2013-02-28
累计:271 2013-02-28
4 12 2013-03-09 09:05:14.000
本月小结:12 2013-03-31
累计:283 2013-03-31
5 31 2013-04-09 09:05:14.000
.....
.....SQL
----------- ----------- -----------------------
...
1 87 2013-01-08 16:31:15.000
2 140 2013-01-09 09:05:14.000
3 44 2013-02-09 09:05:14.000
4 12 2013-03-09 09:05:14.000
5 31 2013-04-09 09:05:14.000
6 525 2013-05-09 09:05:14.000
7 188 2013-06-13 09:12:46.000
...
...
...
我想查出详细+每个月总和+到这个月总和,有什么好方法,求大神编号 价格 时间
----------- ----------- -----------------------
1 87 2013-01-08 16:31:15.000
2 140 2013-01-09 09:05:14.000
本月小结:227 2013-01-31
累计:227 2013-01-31
3 44 2013-02-09 09:05:14.000
本月小结:44 2013-02-28
累计:271 2013-02-28
4 12 2013-03-09 09:05:14.000
本月小结:12 2013-03-31
累计:283 2013-03-31
5 31 2013-04-09 09:05:14.000
.....
.....SQL
解决方案 »
- 求sql语句
- 怎样和上期的数据对比
- 怎么样将查询的结果放在select中比较再次查询?select中查询出结果然后显示出
- 急:华为公司部诚聘开发/测试人才(工作地点:南京)短期有效
- SQLServer2005服务端端口号改为非1433后(如:1148)客户端无法链接
- 特急!邹大哥请进:异类查询要求为连接设置 ANSI_NULLS 和 ANSI_WARNINGS 选项。这将确保一致的查询语义。请启用这些选项,然后重新发出查
- 有关delete trigger的问题
- 如何备份ODBC数据源管理器中系统DNS下的那个表??谢谢大家了,帮一下忙吧,
- 如何让计算结果172.4954等于173?详见内文
- 關於存儲過程的執行錯誤,怎麼把撤消已執行的語句影響?? 謝謝!!
- sql的多表更新
- 求一个SQL语句 -多表联合查询
(
select 1,87,'2013-01-08 16:31:15.000' union all
select 2,140,'2013-01-09 09:05:14.000' union all
select 3,44,'2013-02-09 09:05:14.000' union all
select 4,12,'2013-03-09 09:05:14.000' union all
select 5,31,'2013-04-09 09:05:14.000' union all
select 6,525,'2013-05-09 09:05:14.000' union all
select 7,188,'2013-06-13 09:12:46.000'
)
,a2 as
(
select convert(char(7),time,20) time,sum(price) price from a1 group by convert(char(7),time,20)
)
,a3 as
(
select time,(select sum(price) from a2 where time<=a.time) price from a2 a
)
select id,'' descr,price,time from a1
union all
select 0,'本月小结',price,dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a2
union all
select 0,'累计',price,dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a3
order by time
with a1 (Id,price,time) as
(
select 1,87,'2013-01-08 16:31:15.000' union all
select 2,140,'2013-01-09 09:05:14.000' union all
select 3,44,'2013-02-09 09:05:14.000' union all
select 4,12,'2013-03-09 09:05:14.000' union all
select 5,31,'2013-04-09 09:05:14.000' union all
select 6,525,'2013-05-09 09:05:14.000' union all
select 7,188,'2013-06-13 09:12:46.000'
)
,a2 as
(
select convert(char(7),time,20) time,sum(price) price from a1 group by convert(char(7),time,20)
)
,a3 as
(
select time,(select sum(price) from a2 where time<=a.time) price from a2 a
)
select CAST(id AS varchar) id,CAST(price AS VARCHAR) price,time from a1
union all
select '','本月小结:'+CAST(price AS VARCHAR),dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a2
union all
select '','累计:'+CAST(price AS VARCHAR),dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a3
order by time
(
select 1,87,'2013-01-08 16:31:15.000' union all
select 2,140,'2013-01-09 09:05:14.000' union all
select 3,44,'2013-02-09 09:05:14.000' union all
select 4,12,'2013-03-09 09:05:14.000' union all
select 5,31,'2013-04-09 09:05:14.000' union all
select 6,525,'2013-05-09 09:05:14.000' union all
select 7,188,'2013-06-13 09:12:46.000'
) --select * From a1select b.*,
(select sum(Price) from a1 where Convert(varchar(7),time,120)=Convert(varchar(7),b.time,120) ) as [本月小结] ,
(select sum(Price) from a1 where Convert(varchar(7),time,120)<=Convert(varchar(7),b.time,120) ) as [累计]
from a1 b