＃1很容易：（这本来是“我应该如何计算哪些交通事故落入网格表中x1，x2，y1，y2坐标的方形网格中？”）
SELECt DISTINCT grid.IDFROM crashesINNER JOIN grid ON crashes.x_coordinate BETWEEN grid.x1 AND grid.x2 And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
＃2只是有点难：（这最初是“在接下来的几年中，我将如何检索崩溃次数超过60的所有“方格”（x1，x2，y1，y2的元组…？”））
SELECt grid.ID, COUNT(*) AS CrashCountFROM crashesINNER JOIN grid ON crashes.x_coordinate BETWEEN grid.x1 AND grid.x2 And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2WHERe crashes.yearCol IN(2005, 2006, 2007)GROUP BY grid.IDHAVINg COUNT(*) >= 60
修订后的问题的解决方案…
对于＃1“如何计算每个方形网格的崩溃次数？”，这只是对原始＃2的简化：
SELECt grid.ID, COUNT(*) AS CrashCountFROM crashesINNER JOIN grid ON crashes.x_coordinate BETWEEN grid.x1 AND grid.x2 And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2WHERe crashes.yearCol IN(2005, 2006, 2007)GROUP BY grid.ID
对于＃2，“在接下来的几年（ 每年
）：2005、2006和2007年，我将如何检索崩溃次数超过60的所有’方格’（x1，x2，y1，y2的元组）？
SELECt *FROM( SELECt grid.ID, grid.x1, grid.x2, grid.y1, grid.y2, ( SELECT COUNT(*) FROM crashes WHERe yearCol = 2005 And crashes.x_coordinate BETWEEN grid.x1 AND grid.x2 And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2 ) As year05, ( SELECt COUNT(*) FROM crashes WHERe yearCol = 2006 And crashes.x_coordinate BETWEEN grid.x1 AND grid.x2 And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2 ) As year06, ( SELECt COUNT(*) FROM crashes WHERe yearCol = 2007 And crashes.x_coordinate BETWEEN grid.x1 AND grid.x2 And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2 ) As year07, FROM grid) As gridSumWHERe year05 >= 60 And year06 >= 60 And year07 >= 60
在TSQL中，我们可以使用WITH子句来简化此操作。
