---------------------------------------------------------------- -- Author :TravyLee(走自己的路,让狗去叫吧!) -- Date :2013-11-12 14:38:00 -- Version: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[the_date] datetime,[the_value] varchar(1)) insert [tb] select 1,'2013-11-1','A' union all select 2,'2013-11-1','A' union all select 3,'2013-11-2','A' union all select 4,'2013-11-2','A' union all select 5,'2013-11-2','A' union all select 6,'2013-11-3','A' union all select 7,'2013-11-3','A' union all select 8,'2013-11-4','A' union all select 9,'2013-11-4','A' union all select 10,'2013-11-4','A' goselect px=(select count(1) from tb b where a.[the_date]=b.[the_date] and a.[the_value]=b.[the_value] and a.id>=b.id) ,* from tb a /* px id the_date the_value --------------------------------------------- 1 1 2013-11-01 00:00:00.000 A 2 2 2013-11-01 00:00:00.000 A 1 3 2013-11-02 00:00:00.000 A 2 4 2013-11-02 00:00:00.000 A 3 5 2013-11-02 00:00:00.000 A 1 6 2013-11-03 00:00:00.000 A 2 7 2013-11-03 00:00:00.000 A 1 8 2013-11-04 00:00:00.000 A 2 9 2013-11-04 00:00:00.000 A 3 10 2013-11-04 00:00:00.000 A */
-- Author :TravyLee(走自己的路,让狗去叫吧!)
-- Date :2013-11-12 14:38:00
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[the_date] datetime,[the_value] varchar(1))
insert [tb]
select 1,'2013-11-1','A' union all
select 2,'2013-11-1','A' union all
select 3,'2013-11-2','A' union all
select 4,'2013-11-2','A' union all
select 5,'2013-11-2','A' union all
select 6,'2013-11-3','A' union all
select 7,'2013-11-3','A' union all
select 8,'2013-11-4','A' union all
select 9,'2013-11-4','A' union all
select 10,'2013-11-4','A'
goselect
px=(select count(1) from tb b where a.[the_date]=b.[the_date] and
a.[the_value]=b.[the_value] and a.id>=b.id)
,* from tb a
/*
px id the_date the_value
---------------------------------------------
1 1 2013-11-01 00:00:00.000 A
2 2 2013-11-01 00:00:00.000 A
1 3 2013-11-02 00:00:00.000 A
2 4 2013-11-02 00:00:00.000 A
3 5 2013-11-02 00:00:00.000 A
1 6 2013-11-03 00:00:00.000 A
2 7 2013-11-03 00:00:00.000 A
1 8 2013-11-04 00:00:00.000 A
2 9 2013-11-04 00:00:00.000 A
3 10 2013-11-04 00:00:00.000 A
*/