数据表如下:日期
2009-11-02
2009-11-11
2009-11-29
2009-11-29
2009-11-30
2009-12-02
2009-12-08
2009-12-12
2009-12-13
2009-12-17
2009-12-17
2009-12-18
2009-12-19
2009-12-19
2009-12-19
2009-12-20
2009-12-29
2009-12-29
2009-12-31
2009-12-31
2010-01-06
2010-01-07
2010-01-07想统计从第一个日期开始(第一个日期即表中最小的日期)到表中存在的每个日期之间共有几行记录,请问该如何统计呀?想得到的结果如下:日期 截止到该日记录数
2009-11-02 1
2009-11-11 2
2009-11-29 4
2009-11-30 5
2009-12-02 6
2009-12-08 7
2009-12-12 8
2009-12-13 9
2009-12-17 11
2009-12-18 12
2009-12-19 15
2009-12-20 16
2009-12-29 18
2009-12-31 20
2010-01-06 21
2010-01-07 23
2009-11-02
2009-11-11
2009-11-29
2009-11-29
2009-11-30
2009-12-02
2009-12-08
2009-12-12
2009-12-13
2009-12-17
2009-12-17
2009-12-18
2009-12-19
2009-12-19
2009-12-19
2009-12-20
2009-12-29
2009-12-29
2009-12-31
2009-12-31
2010-01-06
2010-01-07
2010-01-07想统计从第一个日期开始(第一个日期即表中最小的日期)到表中存在的每个日期之间共有几行记录,请问该如何统计呀?想得到的结果如下:日期 截止到该日记录数
2009-11-02 1
2009-11-11 2
2009-11-29 4
2009-11-30 5
2009-12-02 6
2009-12-08 7
2009-12-12 8
2009-12-13 9
2009-12-17 11
2009-12-18 12
2009-12-19 15
2009-12-20 16
2009-12-29 18
2009-12-31 20
2010-01-06 21
2010-01-07 23
解决方案 »
- access 2007 的.accdb 想转为 dbf 的请教?小弟先感谢了!请前辈指点!
- sql server 模糊查询问题
- SQL Server 2005 支持关系型和 OLAP 数据
- 关于SQL SERVER事务能处理的范围
- 高手帮我看看
- 一个关于从EXECL内容导入SQL中然后,为其表设置主键的问题?请指教!!!
- 阶式加减问题!
- slq2008r2数据附加入vs2010sp1时出错
- 高分 请教 DBF文件导入SQL server的问题
- 求一sql 列转行
- SQL 2008 or 2005 如何生成主从表的xml
- SQL Server 2000,能用一条SQL语句就统计出物品的采购平均价格吗?
[截止到该日记录数]=(select count(*) from T where 日期<=A.日期)
from T as A
--> Author : js_szy
--> Target : ★★★
--> Date : 2010-01-07 14:06:29
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (日期 datetime)
insert into @tb
select '2009-11-02' union all
select '2009-11-11' union all
select '2009-11-29' union all
select '2009-11-29' union all
select '2009-11-30' union all
select '2009-12-02' union all
select '2009-12-08' union all
select '2009-12-12' union all
select '2009-12-13' union all
select '2009-12-17' union all
select '2009-12-17' union all
select '2009-12-18' union all
select '2009-12-19' union all
select '2009-12-19' union all
select '2009-12-19' union all
select '2009-12-20' union all
select '2009-12-29' union all
select '2009-12-29' union all
select '2009-12-31' union all
select '2009-12-31' union all
select '2010-01-06' union all
select '2010-01-07' union all
select '2010-01-07'select 日期,num=(select count(1) from @tb where 日期<=t.日期) from @tb t日期 num
----------------------- -----------
2009-11-02 00:00:00.000 1
2009-11-11 00:00:00.000 2
2009-11-29 00:00:00.000 4
2009-11-29 00:00:00.000 4
2009-11-30 00:00:00.000 5
2009-12-02 00:00:00.000 6
2009-12-08 00:00:00.000 7
2009-12-12 00:00:00.000 8
2009-12-13 00:00:00.000 9
2009-12-17 00:00:00.000 11
2009-12-17 00:00:00.000 11
2009-12-18 00:00:00.000 12
2009-12-19 00:00:00.000 15
2009-12-19 00:00:00.000 15
2009-12-19 00:00:00.000 15
2009-12-20 00:00:00.000 16
2009-12-29 00:00:00.000 18
2009-12-29 00:00:00.000 18
2009-12-31 00:00:00.000 20
2009-12-31 00:00:00.000 20
2010-01-06 00:00:00.000 21
2010-01-07 00:00:00.000 23
2010-01-07 00:00:00.000 23(23 行受影响)
加个distinct
[截止到该日记录数]=(select count(*) from T where 日期 <=A.日期)
from T as A
(
select 日期, row_number() over( order by 日期 ) as '记录数' from @tb
) T
group by 日期
-- Author : htl258(Tony)
-- Date : 2010-01-07 14:21:59
-- 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]([日期] DATETIME)
INSERT [tb]
SELECT N'2009-11-02' UNION ALL
SELECT N'2009-11-11' UNION ALL
SELECT N'2009-11-29' UNION ALL
SELECT N'2009-11-29' UNION ALL
SELECT N'2009-11-30' UNION ALL
SELECT N'2009-12-02' UNION ALL
SELECT N'2009-12-08' UNION ALL
SELECT N'2009-12-12' UNION ALL
SELECT N'2009-12-13' UNION ALL
SELECT N'2009-12-17' UNION ALL
SELECT N'2009-12-17' UNION ALL
SELECT N'2009-12-18' UNION ALL
SELECT N'2009-12-19' UNION ALL
SELECT N'2009-12-19' UNION ALL
SELECT N'2009-12-19' UNION ALL
SELECT N'2009-12-20' UNION ALL
SELECT N'2009-12-29' UNION ALL
SELECT N'2009-12-29' UNION ALL
SELECT N'2009-12-31' UNION ALL
SELECT N'2009-12-31' UNION ALL
SELECT N'2010-01-06' UNION ALL
SELECT N'2010-01-07' UNION ALL
SELECT N'2010-01-07'
GO
--SELECT * FROM [tb]-->SQL查询如下:
with t as
(
select 日期,COUNT(1) cnt
from tb
group by 日期
)
select 日期,
截止到该日记录数=(select SUM(cnt) from t where 日期<=a.日期)
from t a
/*
日期 截止到该日记录数
2009-11-02 00:00:00.000 1
2009-11-11 00:00:00.000 2
2009-11-29 00:00:00.000 4
2009-11-30 00:00:00.000 5
2009-12-02 00:00:00.000 6
2009-12-08 00:00:00.000 7
2009-12-12 00:00:00.000 8
2009-12-13 00:00:00.000 9
2009-12-17 00:00:00.000 11
2009-12-18 00:00:00.000 12
2009-12-19 00:00:00.000 15
2009-12-20 00:00:00.000 16
2009-12-29 00:00:00.000 18
2009-12-31 00:00:00.000 20
2010-01-06 00:00:00.000 21
2010-01-07 00:00:00.000 23
*/
-- Author : htl258(Tony)
-- Date : 2010-01-07 14:21:59
-- 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]([日期] DATETIME)
INSERT [tb]
SELECT N'2009-11-02' UNION ALL
SELECT N'2009-11-11' UNION ALL
SELECT N'2009-11-29' UNION ALL
SELECT N'2009-11-29' UNION ALL
SELECT N'2009-11-30' UNION ALL
SELECT N'2009-12-02' UNION ALL
SELECT N'2009-12-08' UNION ALL
SELECT N'2009-12-12' UNION ALL
SELECT N'2009-12-13' UNION ALL
SELECT N'2009-12-17' UNION ALL
SELECT N'2009-12-17' UNION ALL
SELECT N'2009-12-18' UNION ALL
SELECT N'2009-12-19' UNION ALL
SELECT N'2009-12-19' UNION ALL
SELECT N'2009-12-19' UNION ALL
SELECT N'2009-12-20' UNION ALL
SELECT N'2009-12-29' UNION ALL
SELECT N'2009-12-29' UNION ALL
SELECT N'2009-12-31' UNION ALL
SELECT N'2009-12-31' UNION ALL
SELECT N'2010-01-06' UNION ALL
SELECT N'2010-01-07' UNION ALL
SELECT N'2010-01-07'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select 日期,
截止到该日记录数=(select count(1) from tb where 日期 <=a.日期)
from tb a
group by 日期这样就可以了.
create table tb(日期 datetime)
insert into tb
select '2009-11-02' union all
select '2009-11-11' union all
select '2009-11-29' union all
select '2009-11-29' union all
select '2009-11-30' union all
select '2009-12-02' union all
select '2009-12-08' union all
select '2009-12-12' union all
select '2009-12-13' union all
select '2009-12-17' union all
select '2009-12-17' union all
select '2009-12-18' union all
select '2009-12-19' union all
select '2009-12-19' union all
select '2009-12-19' union all
select '2009-12-20' union all
select '2009-12-29' union all
select '2009-12-29' union all
select '2009-12-31' union all
select '2009-12-31' union all
select '2010-01-06' union all
select '2010-01-07' union all
select '2010-01-07'
goselect 截止到该日记录数 = (select count(distinct 日期) from tb where 日期 < t.日期) + 1 from tb tdrop table tb/*
截止到该日记录数
-----------
1
2
3
3
4
5
6
7
8
9
9
10
11
11
11
12
13
13
14
14
15
16
16(所影响的行数为 23 行)*/
insert into tb
select '2009-11-02' union all
select '2009-11-11' union all
select '2009-11-29' union all
select '2009-11-29' union all
select '2009-11-30' union all
select '2009-12-02' union all
select '2009-12-08' union all
select '2009-12-12' union all
select '2009-12-13' union all
select '2009-12-17' union all
select '2009-12-17' union all
select '2009-12-18' union all
select '2009-12-19' union all
select '2009-12-19' union all
select '2009-12-19' union all
select '2009-12-20' union all
select '2009-12-29' union all
select '2009-12-29' union all
select '2009-12-31' union all
select '2009-12-31' union all
select '2010-01-06' union all
select '2010-01-07' union all
select '2010-01-07'
goselect distinct 截止到该日记录数 from
(
select 截止到该日记录数 = (select count(日期) from tb where 日期 < t.日期) + 1 from tb t
) mdrop table tb/*
截止到该日记录数
-----------
1
2
3
5
6
7
8
9
10
12
13
16
17
19
21
22(所影响的行数为 16 行)
*/
select distinct 日期,num=(select count(1) from @tb where 日期<=t.日期) from @tb t