IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
userid VARCHAR(4),
trading DATETIME,
)
insert into #tb
values('0001','2012-12-01 10:10:01'),
('0001','2012-12-02 09:21:10'),
('0001','2012-12-03 11:10:13'),
('0001','2012-12-04 14:12:51'),
('0001','2012-12-05 17:22:10'),
('0001','2012-12-06 14:14:00'),
('0001','2013-01-01 07:10:01'),
('0001','2013-01-02 11:13:55'),
('0001','2013-01-10 18:25:00'),
('0002','2014-01-01 18:09:00'),
('0002','2014-01-02 19:19:12'),
('0002','2014-01-03 10:18:33'),
('0002','2014-02-03 11:22:00'),
('0002','2014-02-04 10:18:10'),
('0002','2014-02-07 16:19:30'),
('0002','2014-03-05 08:22:00'),
('0002','2014-03-06 19:33:30')
IF OBJECT_ID('TEMPDB.DBO.#tc') IS NOT NULL DROP TABLE #tc
GO
CREATE TABLE #tc
(
userid VARCHAR(4),
trading DATE,
)
insert into #tc
values('0001','2012-12-06'),
('0002','2014-01-03'),
('0002','2014-02-07'),
('0002','2014-03-06')
分别有以上两张表，第一张表是记录明细表，第二张是已知的具体日期时间点表。想根据第二张的每个userid的具体时间点对第一张明细记录进行分类。结果如下：
userid trading stype
0001 2012-12-01 10:10:01.000 1
0001 2012-12-02 09:21:10.000 1
0001 2012-12-03 11:10:13.000 1
0001 2012-12-04 14:12:51.000 1
0001 2012-12-05 17:22:10.000 1
0001 2012-12-06 14:14:00.000 1
0001 2013-01-01 07:10:01.000 2
0001 2013-01-02 11:13:55.000 2
0001 2013-01-10 18:25:00.000 2
0002 2014-01-01 18:09:00.000 1
0002 2014-01-02 19:19:12.000 1
0002 2014-01-03 10:18:33.000 1
0002 2014-02-03 11:22:00.000 2
0002 2014-02-04 10:18:10.000 2
0002 2014-02-07 16:19:30.000 2
0002 2014-03-05 08:22:00.000 3
0002 2014-03-06 19:33:30.000 3
------解决方案--------------------
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb
(
userid VARCHAR(4),
trading DATETIME,
)
insert into #tb
values('0001','2012-12-01 10:10:01'),
('0001','2012-12-02 09:21:10'),
('0001','2012-12-03 11:10:13'),
('0001','2012-12-04 14:12:51'),
('0001','2012-12-05 17:22:10'),
('0001','2012-12-06 14:14:00'),
('0001','2013-01-01 07:10:01'),
('0001','2013-01-02 11:13:55'),
('0001','2013-01-10 18:25:00'),
('0002','2014-01-01 18:09:00'),
('0002','2014-01-02 19:19:12'),
('0002','2014-01-03 10:18:33'),
('0002','2014-02-03 11:22:00'),
('0002','2014-02-04 10:18:10'),
('0002','2014-02-07 16:19:30'),
('0002','2014-03-05 08:22:00'),
('0002','2014-03-06 19:33:30')
IF OBJECT_ID('TEMPDB.DBO.#tc') IS NOT NULL DROP TABLE #tc
GO
CREATE TABLE #tc
(
userid VARCHAR(4),
trading DATE,
)
insert into #tc
values('0001','2012-12-06'),
('0002','2014-01-03'),
('0002','2014-02-07'),
('0002','2014-03-06')
SELECT b.userid, b.trading, ISNULL(S.RN, 0) + 1 AS stype
FROM #tb b
OUTER APPLY (SELECT TOP 1 c.RN FROM (SELECT userid, trading, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY trading) AS RN FROM #tc ) c WHERE c.userid = b.userid AND CONVERT(varchar(10),b.trading,102) >CONVERT(varchar(10),c.trading,102) ORDER BY c.trading DESC ) S
ORDER BY b.userid, b.trading
userid trading stype
------ ----------------------- --------------------