SQLite 中查询结果行之间的时差:如何?

对于 CustomerName 的第一个条目，该值将是 0.我在这里询问是否可以将其计算为原始查询的一部分.如果没有，我打算通过一系列查询来做到这一点 - 最初创建一个新的 TABLE 选择上面查询的结果 - 然后 ALTERING 添加新列并使用 UPDATE/strftime 通过使用 rowid-1 来获取时间差异(不知何故)... 解决方案 要计算从一个 ReviewDT 行到下一行经过的秒数: SELECT q.CustomerName, q.ReviewDT,strftime('%s',q.ReviewDT)- strftime('%s',coalesce((从评论中选择 r.ReviewDT 作为 r其中 r.CustomerName = q.CustomerName和 r.ReviewDT 要获取每个 ReviewDT 及其前面的 CustomerName 行的 DT:SELECT q.CustomerName, q.ReviewDT,合并((从评论中选择 r.ReviewDT 作为 r其中 r.CustomerName = q.CustomerName和 r.ReviewDT Consider the following reviews table contents:CustomerName ReviewDTDoe,John 2011-06-20 10:13:24Doe,John 2011-06-20 10:54:45Doe,John 2011-06-20 11:36:34Doe,Janie 2011-06-20 05:15:12The results are ordered by ReviewDT and grouped by CustomerName, such as:SELECT CustomerName, ReviewDTFROM ReviewsWHERE CustomerName NOT NULLORDER BY CustomerName ASC, ReviewDT ASC;I'd like to create a column of the time difference between each row of this query for each Customer... rowid gives the original row, and there is no pattern to the inclusion from the rowid etc...For the 1st entry for a CustomerName, the value would be 0. I am asking here incase this is something that can be calculated as part of the original query somehow. If not, I was planning to do this by a series of queries - initially creating a new TABLE selecting the results of the query above - then ALTERING to add the new column and using UPDATE/strftime to get the time differences by using rowid-1 (somehow)... 解决方案 To compute the seconds elapsed from one ReviewDT row to the next: SELECT q.CustomerName, q.ReviewDT, strftime('%s',q.ReviewDT) - strftime('%s',coalesce((select r.ReviewDT from Reviews as r where r.CustomerName = q.CustomerName and r.ReviewDT < q.ReviewDT order by r.ReviewDT DESC limit 1), q.ReviewDT)) FROM Reviews as q WHERE q.CustomerName NOT NULL ORDER BY q.CustomerName ASC, q.ReviewDT ASC;To get the DT of each ReviewDT and its preceding CustomerName row:SELECT q.CustomerName, q.ReviewDT, coalesce((select r.ReviewDT from Reviews as r where r.CustomerName = q.CustomerName and r.ReviewDT < q.ReviewDT order by r.ReviewDT DESC limit 1), q.ReviewDT) FROM Reviews as q WHERE q.CustomerName NOT NULL ORDER BY q.CustomerName ASC, q.ReviewDT ASC; 这篇关于SQLite 中查询结果行之间的时差:如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！