您可以使用LAG或LEAD函数从结果集中的上一行或下一行中获取值。



查看如何从结果集中的上一行或下一行中获取数据 [ ^ ]



加法



也许类似

  SELECT  a.TicketId，
 a.Owner，
 a.OwnDate  -  b.OwnDate 
从 TicketTable a 
 LEFT   OUTER   JOIN  TicketTable b 
 ON  a.TicketId = b.TicketId 
 AND  a.Owner = b.Owner 
 AND  a.OwnDate< b.OwnDate 
 WHERE   NOT   EXISTS （ SELECT   1  
 FROM  TicketTable c 
 WHERE  c.TicketId = a.TicketId 
 AND  c .Owner = a.Owner 
 AND  c.OwnDate< a.OwnDate）

最后用我的要求解决了这个问题......

 选择 ticketid，
 owner1 = CONVERT（ varchar （ 10 ），owner2-owner1 ， 108 ），
 owner2 = CONVERT（ varchar （ 10 ），owner3-owner2， 108 ），
 owner3 = CONVERT（ varchar （ 10 ），owner4-owner3， 108 ），
 owner4 = CONVERT（ varchar （ 10 ），owner4， 108 ）
来自 
（
选择 ticketid，owndate，'  owner' + cast（ROW_NUMBER（） over  
（ partition   by  ticketid  order   by  owndate ） as   varchar （ 10 ）） as  sno  from  tkownerhistory 
）tbl 
 pivot 
（
 max（owndat e）
  sno  （owner1，owner2，owner3，owner4）

）pvt 

I have table with three columns ticketid,owner,owendate as-

ticketid   owner                     owndate

1001	   LEWIS	        2004-06-18 14:15:11
1001	   WILSON	        2004-06-18 14:16:54
1001	   WILSON	        2004-06-18 14:21:12
1001	   (null)	        2004-09-01 09:56:11
1001	   CALDONE	        2005-02-02 08:38:28
1001	   SINCLAIR	        2005-02-02 08:54:02
1002	   (null)	        2005-02-02 08:40:06
1002	   REID	                2015-01-16 15:18:05
1002	   SMALL	        2015-01-16 15:19:53
1002	   WILSON	        2004-06-18 14:33:47
1002	   (null)	        2004-08-31 15:12:46
1002	   (null)	        2004-09-24 10:03:09
1003	   RAMSDALE	        2004-09-24 10:04:24
1003	   MOTIKA	        2004-08-31 14:51:45
1003	   (null)	        2004-08-31 15:05:50
1003	   MURTHY	        2004-09-02 14:50:28
1004	   (null)	        2004-08-31 15:28:37
1004	   (null)	        2004-09-24 09:24:21
1005	   MOTIKA	        2004-08-31 14:47:19
1005	   MOTIKA	        2004-08-31 17:20:33
1005	   (null)	        2004-08-31 17:23:42
1005	   WILSON	        2004-09-02 16:29:43
1005	   DALEY	        2004-09-15 08:45:27
1005	   SINCLAIR	        2004-09-15 08:34:34

And I want to see output as ticket time difference for each ticket as...

ticketid                  owner1                          owner2               ...   
1001            datediff(owndate1-owndate2)     datediff(owndate2-owndate3)    ...

You can use either LAG or LEAD functions to fetch values from previous or next rows in the result set.

Have a look at How to fetch data from the previous or next rows in the resultset[^]

Addition

Perhaps something like

SELECT a.TicketId,
       a.Owner,
       a.OwnDate - b.OwnDate
FROM   TicketTable a
       LEFT OUTER JOIN TicketTable b
       ON a.TicketId = b.TicketId 
       AND a.Owner = b.Owner
       AND a.OwnDate < b.OwnDate
WHERE NOT EXISTS (SELECT 1
                  FROM  TicketTable c
                  WHERE c.TicketId = a.TicketId 
                  AND   c.Owner = a.Owner
                  AND   c.OwnDate < a.OwnDate)

Finally solved this problem with my requirements......

select ticketid,
owner1=CONVERT(varchar(10),owner2-owner1,108),
owner2=CONVERT(varchar(10),owner3-owner2,108),
owner3=CONVERT(varchar(10),owner4-owner3,108),
owner4=CONVERT(varchar(10),owner4,108)
from
(
select ticketid,owndate,'owner'+cast(ROW_NUMBER() over
(partition by ticketid order by owndate) as varchar(10)) as sno from tkownerhistory
) tbl
pivot
(
max(owndate)
for sno in(owner1,owner2,owner3,owner4)

)pvt