asp.net - MSSQL DateDiff 两个日期之间以及所有行的平均耗时

Question

我一直试图获取某一天所有行的两个日期之间的总体平均耗时。

这是我的设置:

表A
身份证号
创建日期

表B
身份证号
ID_A
最后更新

这是一对多(表 A)到多(表 B)的关系

所以我需要:

1 - 仅获取表 A 中每一行的第一个 LastUpdate(表 B)以及表 A 中给定日期(即 2011-08-19)的 CreatedDate。

2 - 然后我需要获取 CreatedDate(表 A)和 LastUpdate(表 B)之间耗时

3 - 一旦我们获得了每行的运行时间，就可以计算出所有行的平均运行时间，如果您愿意的话，可以计算出总体运行时间。

我们将非常感谢您提供的任何帮助

编辑

根据要求的测试数据:

create table tablea(id int, createddate datetime)
create table tableb(id int, id_a int, lastupdate datetime)

insert into tablea select 1,'2010-12-09 21:37:32.137'
insert into tablea select 2,'2010-12-09 21:38:22.033'
insert into tablea select 3,'2010-12-27 09:48:30.723'
insert into tablea select 15,'2011-03-02 17:38:47.043'
insert into tablea select 16,'2011-03-02 18:02:39.497'
insert into tablea select 18,'2011-03-27 12:23:50.060'
insert into tablea select 19,'2011-03-27 12:50:55.887'
insert into tablea select 20,'2011-03-27 12:57:11.833'
insert into tablea select 21,'2011-03-27 13:05:24.157'
insert into tablea select 22,'2011-03-28 19:57:02.290'
insert into tablea select 24,'2011-04-05 18:17:22.707'
insert into tablea select 25,'2011-04-06 18:41:50.737'
insert into tablea select 26,'2011-04-25 17:52:14.603'
insert into tablea select 28,'2011-04-27 08:51:52.780'
insert into tablea select 29,'2011-04-27 08:52:15.100'
insert into tablea select 30,'2011-04-27 08:55:46.570'
insert into tablea select 31,'2011-04-27 08:56:24.447'
insert into tablea select 32,'2011-04-27 09:33:22.107'
insert into tablea select 33,'2011-04-27 09:34:15.400'
insert into tablea select 34,'2011-04-29 08:30:30.137'
insert into tablea select 35,'2011-04-29 09:58:45.050'
insert into tablea select 36,'2011-08-15 14:07:42.290'
insert into tablea select 37,'2011-08-15 14:07:42.390'
insert into tablea select 38,'2011-08-15 14:27:59.283'
insert into tablea select 39,'2011-08-15 14:27:59.353'
insert into tablea select 40,'2011-08-16 11:51:46.167'
insert into tablea select 41,'2011-08-16 11:51:46.220'
insert into tablea select 62,'2011-08-19 09:21:00.400'
insert into tablea select 63,'2011-08-19 09:21:00.467'
insert into tablea select 64,'2011-08-19 09:21:03.467'
insert into tablea select 65,'2011-08-19 09:39:09.930'
insert into tablea select 66,'2011-08-19 09:39:09.967'
insert into tablea select 67,'2011-08-19 09:44:34.617'
insert into tablea select 68,'2011-08-19 09:44:34.647'
insert into tablea select 71,'2011-08-19 09:49:57.760'
insert into tablea select 72,'2011-08-19 09:49:57.787'
insert into tablea select 73,'2011-08-19 09:51:57.403'
insert into tablea select 74,'2011-08-19 09:51:57.480'
insert into tablea select 75,'2011-08-19 09:52:00.490'
insert into tablea select 89,'2011-08-19 10:57:48.153'
insert into tablea select 90,'2011-08-19 10:57:48.247'
insert into tablea select 17,'2011-03-09 17:07:19.593'
insert into tablea select 23,'2011-03-28 20:51:18.337'
insert into tablea select 27,'2011-04-26 09:33:22.890'
insert into tablea select 42,'2011-08-16 14:03:28.067'
insert into tablea select 43,'2011-08-16 14:03:28.110'
insert into tablea select 44,'2011-08-16 14:03:31.283'
insert into tablea select 45,'2011-08-16 14:05:56.460'
insert into tablea select 46,'2011-08-16 14:05:56.487'
insert into tablea select 47,'2011-08-16 14:05:59.500'
insert into tablea select 48,'2011-08-18 06:06:08.460'
insert into tablea select 49,'2011-08-18 06:06:08.493'
insert into tablea select 50,'2011-08-18 06:06:11.553'
insert into tablea select 51,'2011-08-19 08:53:35.783'
insert into tablea select 52,'2011-08-19 08:53:35.813'
insert into tablea select 53,'2011-08-19 08:53:38.837'
insert into tablea select 54,'2011-08-19 08:58:43.103'
insert into tablea select 55,'2011-08-19 08:58:43.137'
insert into tablea select 56,'2011-08-19 09:07:54.663'
insert into tablea select 57,'2011-08-19 09:07:54.700'
insert into tablea select 58,'2011-08-19 09:07:57.687'
insert into tablea select 59,'2011-08-19 09:14:31.000'
insert into tablea select 60,'2011-08-19 09:14:31.070'
insert into tablea select 61,'2011-08-19 09:15:01.140'
insert into tablea select 69,'2011-08-19 09:47:20.577'
insert into tablea select 70,'2011-08-19 09:47:20.637'
insert into tablea select 76,'2011-08-19 09:58:24.527'
insert into tablea select 77,'2011-08-19 09:58:24.563'
insert into tablea select 78,'2011-08-19 09:58:27.727'
insert into tablea select 79,'2011-08-19 10:24:43.583'
insert into tablea select 80,'2011-08-19 10:24:43.620'
insert into tablea select 81,'2011-08-19 10:39:31.607'
insert into tablea select 82,'2011-08-19 10:39:31.650'
insert into tablea select 83,'2011-08-19 10:48:24.317'
insert into tablea select 84,'2011-08-19 10:48:24.343'
insert into tablea select 85,'2011-08-19 10:48:27.390'
insert into tablea select 86,'2011-08-19 10:53:39.003'
insert into tablea select 87,'2011-08-19 10:53:39.050'
insert into tablea select 88,'2011-08-19 10:53:42.037'
insert into tablea select 91,'2011-08-19 11:07:13.280'
insert into tablea select 92,'2011-08-19 11:07:13.313'
insert into tablea select 93,'2011-08-19 11:07:16.450'
insert into tableb select 1,1,'2010-12-09 21:37:32.223'
insert into tableb select 2,2,'2010-12-09 21:38:22.053'
insert into tableb select 3,1,'2010-12-18 12:05:43.213'
insert into tableb select 4,2,'2010-12-24 11:28:00.857'
insert into tableb select 5,3,'2010-12-27 09:48:30.757'
insert into tableb select 46,2,'2011-02-28 20:54:08.247'
insert into tableb select 47,15,'2011-03-02 17:38:47.153'
insert into tableb select 49,16,'2011-03-02 18:02:39.513'
insert into tableb select 52,17,'2011-03-09 17:24:54.683'
insert into tableb select 13,2,'2011-01-03 12:39:49.850'
insert into tableb select 53,17,'2011-03-10 19:37:31.677'
insert into tableb select 54,17,'2011-03-10 19:40:02.457'
insert into tableb select 55,17,'2011-03-10 19:40:59.930'
insert into tableb select 56,17,'2011-03-10 19:41:54.623'
insert into tableb select 58,17,'2011-03-13 09:19:26.663'
insert into tableb select 64,17,'2011-03-26 10:05:41.793'
insert into tableb select 65,17,'2011-03-26 10:13:06.773'
insert into tableb select 66,18,'2011-03-27 12:23:50.200'
insert into tableb select 67,19,'2011-03-27 12:50:55.933'
insert into tableb select 68,20,'2011-03-27 12:57:11.833'
insert into tableb select 69,21,'2011-03-27 13:05:24.173'
insert into tableb select 70,22,'2011-03-28 19:57:02.570'
insert into tableb select 71,22,'2011-03-28 19:57:26.857'
insert into tableb select 72,22,'2011-03-28 19:57:51.630'
insert into tableb select 73,23,'2011-03-28 20:51:18.383'
insert into tableb select 74,23,'2011-03-28 20:51:37.643'
insert into tableb select 75,24,'2011-04-05 18:17:22.770'
insert into tableb select 76,24,'2011-04-05 19:25:42.460'
insert into tableb select 77,24,'2011-04-05 21:08:40.787'
insert into tableb select 78,24,'2011-04-05 23:06:11.670'
insert into tableb select 79,24,'2011-04-05 23:18:14.483'
insert into tableb select 81,24,'2011-04-05 23:31:50.060'
insert into tableb select 82,24,'2011-04-05 23:39:37.403'
insert into tableb select 83,24,'2011-04-05 23:45:48.253'
insert into tableb select 84,24,'2011-04-06 18:03:29.003'
insert into tableb select 85,24,'2011-04-06 18:09:37.817'
insert into tableb select 86,24,'2011-04-06 18:41:47.423'
insert into tableb select 87,25,'2011-04-06 18:41:50.757'
insert into tableb select 88,24,'2011-04-06 20:23:39.847'
insert into tableb select 89,24,'2011-04-06 20:30:37.447'
insert into tableb select 90,24,'2011-04-06 20:58:40.903'
insert into tableb select 91,24,'2011-04-06 21:08:43.977'
insert into tableb select 92,26,'2011-04-25 17:52:14.667'
insert into tableb select 94,28,'2011-04-27 08:51:52.887'
insert into tableb select 95,29,'2011-04-27 08:52:15.110'
insert into tableb select 96,30,'2011-04-27 08:55:46.580'
insert into tableb select 98,32,'2011-04-27 09:33:22.127'
insert into tableb select 99,33,'2011-04-27 09:34:15.410'
insert into tableb select 102,34,'2011-04-29 08:30:30.173'
insert into tableb select 103,35,'2011-04-29 09:58:45.063'
insert into tableb select 104,28,'2011-05-21 14:59:42.567'
insert into tableb select 105,35,'2011-06-15 19:53:18.547'
insert into tableb select 109,36,'2011-08-15 14:07:42.310'
insert into tableb select 110,37,'2011-08-15 14:07:42.400'
insert into tableb select 111,38,'2011-08-15 14:27:59.333'
insert into tableb select 112,39,'2011-08-15 14:27:59.360'
insert into tableb select 113,40,'2011-08-16 11:51:46.180'
insert into tableb select 135,62,'2011-08-19 09:21:00.450'
insert into tableb select 136,63,'2011-08-19 09:21:00.477'
insert into tableb select 137,64,'2011-08-19 09:21:03.477'
insert into tableb select 138,65,'2011-08-19 09:39:09.950'
insert into tableb select 139,66,'2011-08-19 09:39:09.973'
insert into tableb select 140,67,'2011-08-19 09:44:34.627'
insert into tableb select 141,68,'2011-08-19 09:44:34.650'
insert into tableb select 144,71,'2011-08-19 09:49:57.770'
insert into tableb select 145,72,'2011-08-19 09:49:57.797'
insert into tableb select 146,73,'2011-08-19 09:51:57.447'
insert into tableb select 147,74,'2011-08-19 09:51:57.487'
insert into tableb select 148,75,'2011-08-19 09:52:00.510'
insert into tableb select 152,78,'2011-08-19 09:59:46.097'
insert into tableb select 153,77,'2011-08-19 09:59:46.170'
insert into tableb select 154,76,'2011-08-19 09:59:46.263'
insert into tableb select 155,75,'2011-08-19 09:59:46.320'
insert into tableb select 156,74,'2011-08-19 09:59:46.347'
insert into tableb select 157,73,'2011-08-19 09:59:46.387'
insert into tableb select 158,72,'2011-08-19 09:59:46.413'
insert into tableb select 159,71,'2011-08-19 09:59:46.437'
insert into tableb select 160,70,'2011-08-19 09:59:46.463'
insert into tableb select 161,69,'2011-08-19 09:59:46.487'
insert into tableb select 162,68,'2011-08-19 09:59:46.513'
insert into tableb select 163,67,'2011-08-19 09:59:46.537'
insert into tableb select 164,66,'2011-08-19 09:59:46.563'
insert into tableb select 165,65,'2011-08-19 09:59:46.597'
insert into tableb select 166,64,'2011-08-19 09:59:46.620'
insert into tableb select 167,63,'2011-08-19 09:59:46.647'
insert into tableb select 168,62,'2011-08-19 09:59:46.670'
insert into tableb select 6,3,'2011-01-01 20:04:37.693'
insert into tableb select 97,31,'2011-04-27 08:56:24.467'
insert into tableb select 106,31,'2011-06-15 19:53:19.027'
insert into tableb select 14,2,'2011-01-03 12:55:42.507'
insert into tableb select 15,2,'2011-01-03 12:56:04.507'
insert into tableb select 107,27,'2011-06-15 19:53:19.427'
insert into tableb select 108,23,'2011-06-15 19:53:19.783'
insert into tableb select 114,41,'2011-08-16 11:51:46.230'
insert into tableb select 169,60,'2011-08-19 09:59:46.697'
insert into tableb select 170,59,'2011-08-19 09:59:46.720'
insert into tableb select 171,58,'2011-08-19 09:59:46.747'
insert into tableb select 172,57,'2011-08-19 09:59:46.770'
insert into tableb select 173,56,'2011-08-19 09:59:46.797'
insert into tableb select 174,55,'2011-08-19 09:59:46.820'
insert into tableb select 175,54,'2011-08-19 09:59:46.847'
insert into tableb select 176,53,'2011-08-19 09:59:46.870'
insert into tableb select 177,52,'2011-08-19 09:59:46.897'
insert into tableb select 178,51,'2011-08-19 09:59:46.923'
insert into tableb select 179,50,'2011-08-19 09:59:46.947'
insert into tableb select 180,49,'2011-08-19 09:59:46.973'
insert into tableb select 181,48,'2011-08-19 09:59:46.997'
insert into tableb select 182,47,'2011-08-19 09:59:47.023'
insert into tableb select 183,46,'2011-08-19 09:59:47.047'
insert into tableb select 184,45,'2011-08-19 09:59:47.073'
insert into tableb select 185,44,'2011-08-19 09:59:47.097'
insert into tableb select 186,43,'2011-08-19 09:59:47.123'
insert into tableb select 187,42,'2011-08-19 09:59:47.147'
insert into tableb select 188,41,'2011-08-19 09:59:47.173'
insert into tableb select 189,40,'2011-08-19 09:59:47.197'
insert into tableb select 190,39,'2011-08-19 09:59:47.223'
insert into tableb select 191,38,'2011-08-19 09:59:47.263'
insert into tableb select 192,37,'2011-08-19 09:59:47.287'
insert into tableb select 193,36,'2011-08-19 09:59:47.313'
insert into tableb select 204,89,'2011-08-19 10:57:48.163'
insert into tableb select 205,90,'2011-08-19 10:57:48.263'
insert into tableb select 206,90,'2011-08-19 11:00:20.850'
insert into tableb select 207,89,'2011-08-19 11:00:20.877'
insert into tableb select 208,88,'2011-08-19 11:00:20.900'
insert into tableb select 209,87,'2011-08-19 11:00:20.927'
insert into tableb select 210,86,'2011-08-19 11:00:20.950'
insert into tableb select 211,85,'2011-08-19 11:00:20.977'
insert into tableb select 212,84,'2011-08-19 11:00:21.000'
insert into tableb select 213,83,'2011-08-19 11:00:21.027'
insert into tableb select 214,82,'2011-08-19 11:00:21.050'
insert into tableb select 215,81,'2011-08-19 11:00:21.077'
insert into tableb select 216,80,'2011-08-19 11:00:21.100'
insert into tableb select 217,79,'2011-08-19 11:00:21.127'
insert into tableb select 57,17,'2011-03-10 19:44:18.340'
insert into tableb select 93,27,'2011-04-26 09:33:22.990'
insert into tableb select 42,1,'2011-02-28 19:59:31.450'
insert into tableb select 43,2,'2011-02-28 20:20:14.560'
insert into tableb select 44,2,'2011-02-28 20:30:17.653'
insert into tableb select 45,2,'2011-02-28 20:53:33.747'
insert into tableb select 48,15,'2011-03-02 17:39:21.980'
insert into tableb select 50,16,'2011-03-02 18:03:40.917'
insert into tableb select 51,17,'2011-03-09 17:07:20.093'
insert into tableb select 63,17,'2011-03-19 09:02:55.330'
insert into tableb select 80,24,'2011-04-05 23:25:20.910'
insert into tableb select 59,17,'2011-03-14 18:28:45.263'
insert into tableb select 60,17,'2011-03-14 18:29:00.523'
insert into tableb select 61,17,'2011-03-14 18:33:34.380'
insert into tableb select 62,17,'2011-03-19 08:57:42.847'
insert into tableb select 115,42,'2011-08-16 14:03:28.077'
insert into tableb select 116,43,'2011-08-16 14:03:28.130'
insert into tableb select 117,44,'2011-08-16 14:03:31.290'
insert into tableb select 118,45,'2011-08-16 14:05:56.470'
insert into tableb select 119,46,'2011-08-16 14:05:56.497'
insert into tableb select 120,47,'2011-08-16 14:05:59.510'
insert into tableb select 121,48,'2011-08-18 06:06:08.477'
insert into tableb select 122,49,'2011-08-18 06:06:08.503'
insert into tableb select 123,50,'2011-08-18 06:06:11.567'
insert into tableb select 124,51,'2011-08-19 08:53:35.797'
insert into tableb select 125,52,'2011-08-19 08:53:35.823'
insert into tableb select 126,53,'2011-08-19 08:53:38.853'
insert into tableb select 127,54,'2011-08-19 08:58:43.120'
insert into tableb select 128,55,'2011-08-19 08:58:43.143'
insert into tableb select 129,56,'2011-08-19 09:07:54.680'
insert into tableb select 130,57,'2011-08-19 09:07:54.707'
insert into tableb select 131,58,'2011-08-19 09:07:57.703'
insert into tableb select 132,59,'2011-08-19 09:14:31.050'
insert into tableb select 133,60,'2011-08-19 09:14:31.077'
insert into tableb select 134,61,'2011-08-19 09:15:01.150'
insert into tableb select 142,69,'2011-08-19 09:47:20.620'
insert into tableb select 143,70,'2011-08-19 09:47:20.647'
insert into tableb select 149,76,'2011-08-19 09:58:24.537'
insert into tableb select 150,77,'2011-08-19 09:58:24.570'
insert into tableb select 151,78,'2011-08-19 09:58:27.783'
insert into tableb select 194,79,'2011-08-19 10:24:43.603'
insert into tableb select 195,80,'2011-08-19 10:24:43.627'
insert into tableb select 196,81,'2011-08-19 10:39:31.627'
insert into tableb select 197,82,'2011-08-19 10:39:31.670'
insert into tableb select 198,83,'2011-08-19 10:48:24.327'
insert into tableb select 199,84,'2011-08-19 10:48:24.350'
insert into tableb select 200,85,'2011-08-19 10:48:27.400'
insert into tableb select 201,86,'2011-08-19 10:53:39.017'
insert into tableb select 202,87,'2011-08-19 10:53:39.060'
insert into tableb select 203,88,'2011-08-19 10:53:42.050'
insert into tableb select 218,91,'2011-08-19 11:07:13.297'
insert into tableb select 219,92,'2011-08-19 11:07:13.320'
insert into tableb select 220,93,'2011-08-19 11:07:16.460'
insert into tableb select 100,32,'2011-04-27 09:36:53.943'
insert into tableb select 101,32,'2011-04-27 09:37:44.207'

select avg(datediff(s, tablea.createddate, b.lastupdate)) as Avg_Elapsed
from tablea 
inner join (select id_a, min(lastupdate) as lastupdate from tableb group by id_a) b
  on tablea.id = b.id_a
where tablea.createddate >= '2011-08-19'

drop table tablea
drop table tableb

无论是否包含 where 子句，上面的结果都是 0。

Answer 1

创建一个子查询来获取 ID 列表及其最近更新的最小值。然后，使用 AVG 和 DATEDIFF 函数获得所需的结果

select avg(datediff(d, tablea.createddate, b.lastupdate)) as AvgDaysElapsed
from tablea 
inner join (select id_a, min(lastupdate) as lastupdate from tableb group by id_a) b
  on tablea.id = b.id_a

编辑:以下测试数据/查询对我来说效果很好，因此您需要发布更多信息来说明查询不起作用的原因。我怀疑这与你的过滤器有关......

create table tablea(id int, createddate datetime)
create table tableb(id int, id_a int, lastupdate datetime)
insert into tablea select 1,'1/1/2000'
insert into tableb select 1,1,'1/1/2000 12:49'
insert into tableb select 2,1,'1/1/2000 12:50'
insert into tablea select 2,'1/1/2000 8:54'
insert into tableb select 3,2,'1/1/2000 8:55'
insert into tableb select 4,2,'1/1/2000 8:56'

select avg(datediff(s, tablea.createddate, b.lastupdate)) as AvgDaysElapsed
from tablea 
inner join (select id_a, min(lastupdate) as lastupdate from tableb group by id_a) b
  on tablea.id = b.id_a

drop table tablea
drop table tableb