12 2010-02-02
13 2010-02-03
14 2010-02-04
15 2010-02-05
16 2010-02-06希望得到的结果如下:
12 2010-02-02
25 2010-02-03
39 2010-02-04
54 2010-02-05
70 2010-02-06就是当前日期加上前一天的数据~~~
13 2010-02-03
14 2010-02-04
15 2010-02-05
16 2010-02-06希望得到的结果如下:
12 2010-02-02
25 2010-02-03
39 2010-02-04
54 2010-02-05
70 2010-02-06就是当前日期加上前一天的数据~~~
解决方案 »
- 多大的mdf文件才算大
- 关于三个表组成的主从关系,求 SUM问题, 在线等 有 答案既结帖
- sql如何在导入TXT文本数据时.在最后自动加个时间字段??
- 在mssql裡面,如何用循環,不用遊標
- ASP访问sqlserver中decimal类型出错,求解
- 非执行事务,报“访问接口 SQLNCLI 无法启动分布式事务“错误,请大家帮忙看一下
- 写存储过程语法错误 错误7405
- 怎样动态地指定DBCHART的datasource中的dataset???
- 匹配度排序实现方法??(能解决1000分拿去)
- 请教如何清除SQL系统运行日志
- 大家帮帮忙,解决了把老本都奉献上!(联查数据重复问题)
- 难题!量化考核方面
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (col int, col2 datetime )
insert tb select
12, '2010-02-02' union select
13, '2010-02-03' union select
14, '2010-02-04' union select
15, '2010-02-05' union select
16, '2010-02-06'
go
select value=SUM(a.col),convert(varchar(10),b.col2,120) as 日期
from tb a join tb b on a.col2<=b.col2
group by b.col2
/*
value 日期
----------- ----------
12 2010-02-02
25 2010-02-03
39 2010-02-04
54 2010-02-05
70 2010-02-06*/
drop table #
go
create table #(
ID int
,time datetime
)
go
insert into #
select 12,'2010-02-02' union all
select 13,'2010-02-03' union all
select 14,'2010-02-04' union all
select 15,'2010-02-05' union all
select 16,'2010-02-06'
goselect
[sum_ID] = sum(b.ID)
,[time] = a.time
from
# a
inner join # b
on a.time >= b.time
group by a.time--结果
/*
sum_ID time
----------- -----------------------
12 2010-02-02 00:00:00.000
25 2010-02-03 00:00:00.000
39 2010-02-04 00:00:00.000
54 2010-02-05 00:00:00.000
70 2010-02-06 00:00:00.000(5 行受影响)
*/
from xxxx t
order by 1
insert tb select
12, '2010-02-02' union select
13, '2010-02-03' union select
14, '2010-02-04' union select
15, '2010-02-05' union select
16, '2010-02-06'
select sum(b.col) as sm,a.col2
from tb a,tb b
where a.col2>=b.col2
group by a.col2 sm col2
----------- -----------------------
12 2010-02-02 00:00:00.000
25 2010-02-03 00:00:00.000
39 2010-02-04 00:00:00.000
54 2010-02-05 00:00:00.000
70 2010-02-06 00:00:00.000(5 行受影响)
from tb a join tb b on a.date>=b.date
group by a.date
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-06 20:20:11
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] int,[col2] datetime)
insert [tb]
select 12,'2010-02-02' union all
select 13,'2010-02-03' union all
select 14,'2010-02-04' union all
select 15,'2010-02-05' union all
select 16,'2010-02-06'
--------------开始查询--------------------------
select col=(select sum(col1) as col1 from [tb] where col1<=t.col1),convert(varchar(10),col2,120) as col2 from tb t
----------------结果----------------------------
/*col col2
----------- ----------
12 2010-02-02
25 2010-02-03
39 2010-02-04
54 2010-02-05
70 2010-02-06(5 行受影响)
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (col int, col2 datetime )
insert tb select
12, '2010-02-02' union select
13, '2010-02-03' union select
14, '2010-02-04' union select
15, '2010-02-05' union select
16, '2010-02-06'
goselect [date]=convert(varchar,col2,23),
[sum]=(select sm=sum(a.col) from tb a where a.col2<=tb.col2)
from tb
go
基本上都可以完成该功能那么,能不能有一个人站出来,说明一下是哪一个SQL语句效率更高,更优秀了?我期盼这样的人
create table tb (col int, col2 datetime )
insert tb select
12, '2010-02-02' union select
13, '2010-02-03' union select
14, '2010-02-04' union select
15, '2010-02-05' union select
16, '2010-02-06'select col2,col1=(select sum(col) from tb where col2<=a.col2)
from tb a
--结果col2 col1
2010-02-02 00:00:00.000 12
2010-02-03 00:00:00.000 25
2010-02-04 00:00:00.000 39
2010-02-05 00:00:00.000 54
2010-02-06 00:00:00.000 70
insert tb select
12, '2010-02-02' union select
13, '2010-02-03' union select
14, '2010-02-04' union select
15, '2010-02-05' union select
16, '2010-02-06'
select sum(b.col) as sm,a.col2
from tb a,tb b
where a.col2>=b.col2
group by a.col2 sm col2
----------- -----------------------
12 2010-02-02 00:00:00.000
25 2010-02-03 00:00:00.000
39 2010-02-04 00:00:00.000
54 2010-02-05 00:00:00.000
70 2010-02-06 00:00:00.000(5 行受影响)
-- Author : htl258(Tony)
-- Date : 2010-04-07 10:40:20
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[date] DATETIME)
INSERT [tb]
SELECT 12,N'2010-02-02' UNION ALL
SELECT 13,N'2010-02-03' UNION ALL
SELECT 14,N'2010-02-04' UNION ALL
SELECT 15,N'2010-02-05' UNION ALL
SELECT 16,N'2010-02-06'
GO
--SELECT * FROM [tb]-->SQL查询如下:
declare @i int
update tb set
id=@i,@i=ISNULL(id+@i,id)
select * from tb
/*
id date
----------- -----------------------
12 2010-02-02 00:00:00.000
25 2010-02-03 00:00:00.000
39 2010-02-04 00:00:00.000
54 2010-02-05 00:00:00.000
70 2010-02-06 00:00:00.000(5 行受影响)
*/
select (select sum(table.num) from table where table.date<=aa.date) as nnum,aa.date from table aa