表中有如下数据:
code col1 col2 datetime
0001 A B 2014-1-1
0001 A B 2014-1-3
0001 A B 2014-1-12
能否实现下面的查询结果:
code col1 col2 datetime 次数
0001 A B 2014-1-12 3
0001 A B 2014-1-3 2
0001 A B 2014-1-1 1
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-06 14:44:00
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([code] varchar(4),[col1] varchar(1),[col2] varchar(1),[datetime] datetime)
insert [huang]
select '0001','A','B','2014-1-1' union all
select '0001','A','B','2014-1-3' union all
select '0001','A','B','2014-1-12'
--------------开始查询--------------------------
select * ,(SELECT COUNT(1) FROM huang b WHERE a.code=b.code AND a.col1=b.col1 AND a.col2=b.col2 AND a.[datetime]>=b.[datetime]) 次数
from [huang] a
ORDER BY 次数 DESC
----------------结果----------------------------
/*
code col1 col2 datetime 次数
---- ---- ---- ----------------------- -----------
0001 A B 2014-01-12 00:00:00.000 3
0001 A B 2014-01-03 00:00:00.000 2
0001 A B 2014-01-01 00:00:00.000 1
*/
------解决方案--------------------
我接二楼
with test (code, col1, col2 ,datetime) as
(select '0001','A','B','2014-1-1' union all
select '0001','A','B','2014-1-3' union all
select '0001','A','B','2014-1-12')
select * ,次数=row_number() over (partition by code,col1,col2 order by convert(datetime,datetime)) from test order by 次数 desc
------解决方案--------------------
SELECT [code],[col1],[col2],[datetime],(SELECT count(*)FROM #test t2 WHERE t1.[datetime]>=t2.[datetime]) as times
FROM #test t1
GROUP BY [code],[col1],[col2],[datetime]
ORDER BY [datetime]
