34,588
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE tbb
CREATE TABLE tbb
(
[NAME] VARCHAR(10),
[Event] VARCHAR(10),
[Time] datetime
)
INSERT INTO tbb
SELECT 'N1','E1','2010-01-02'
UNION ALL
SELECT 'N1','E2','2010-01-06'
UNION ALL
SELECT 'N2','E1','2010-01-08'
SELECT * FROM tbb t
SELECT [NAME],[Event],[Time],ISNULL(abs(span),0)span
FROM(
SELECT * ,SPAN=(SELECT datediff(dd,T.TIME ,TIME) FROM tbb
WHERE T.NAME=NAME AND T.[Event]<> [EVENT])
FROM tbb t
)M
NAME Event Time span
---------- ---------- ----------------------- -----------
N1 E1 2010-01-02 00:00:00.000 4
N1 E2 2010-01-06 00:00:00.000 4
N2 E1 2010-01-08 00:00:00.000 0
(3 row(s) affected)