首页 文章

带有WHERE子句的MS ACCESS - OFFSET

提问于
浏览 次
0

问题

我想要查询的是我的数据中每个“分组”的第二个结果 . 例如，带有星号的行 .

我试图在[Awardee]，[State]，[City]上分组，这应该是一个独特的人，我想获得他们最近的第二年[年] .

OFFSET函数可以解决问题，但MS ACCESS在其库中没有 . 我查找了使用嵌套查询 TOP n 的变通方法，但在使用变通方法时会产生更多问题 . 我可以使用某种类型的 JOIN 语句来完成此操作吗？

样本数据

------------------------------------
 |_Awardee_|_State_|_City__|_Year___|
 |  John   |  OH   | Cinci | 2015   |
*|  John   |  OH   | Cinci | 2013   |
 |  John   |  OH   | Cinci | 2011   |
 |  Margret|  IN   | Indy  | 2015   |
*|  Margret|  IN   | Indy  | 2012   |
 |  Margret|  IN   | Indy  | 2011   |
 |  Bob    |  IN   | Indy  | 2011   |
*|  Bob    |  IN   | Indy  | 2010   |
 |  Jeff   |  OH   | Cinci | 2016   |
*|  Jeff   |  OH   | Cinci | 2015   |
 |  Jeff   |  OH   | Cinci | 2013   |
 |  Jeff   |  OH   | Cinci | 2012   |
 |  Susan  |  IN   | Indy  | 2012   |
 |  Spencer|  IN   | Indy  | 2016   |
*|  Spencer|  IN   | Indy  | 2015   |
 -------------------------------------

Example OFFSET workaround

通过按降序选择前2条记录，然后按升序选择前1条记录，我可以将结果偏移1 .

SELECT TOP 1 * FROM 
(SELECT TOP 2 [Awardee], [State], [City], [Year] FROM [Table1]
ORDER BY [Awardee] ASC, [Year] DESC)
ORDER BY [Awardee] ASC, [Year] ASC;

工作SQL

这个SQL查询将获得他们最近的奖励年份（记录只有上面有一行astricks的1行） . 但是，正如我们可以看到我是否要使用上面的OFFSET解决方法替换子查询，我最终会得到2个子查询 . 最内部的子查询将无法在WHERE子句中重新标记“CDFI1” . MS ACCESS子查询无法看到最近的邻居查询 .

SELECT *
FROM [T09a: CDFI Award Records] AS CDFI1
WHERE CDFI1.[ID] IN
(SELECT TOP 1 ID FROM [T09a: CDFI Award Records] as CDFI2
WHERE CDFI2.[Awardee] = CDFI1.[Awardee] AND CDFI2.[City] = CDFI1.[City] AND CDFI2.[ST] = CDFI1.[ST]
ORDER BY CDFI2.[Awardee] ASC, CDFI2.[Year] DESC)
ORDER BY CDFI1.Awardee, CDFI1.Year DESC;

EDIT: Accepted Answer

感谢@krokodilko下面的演示和MYSQL帮助 . 为了在ACCESS中工作，有一些小的语法怪癖，主要是使用方括号，如下所示 .

SELECT t.[Awardee], t.[ST], t.[City], max( t.[Year] ) as Year
FROM ([T09a: CDFI Award Records] as t
INNER JOIN (
  SELECT [Awardee], [ST], [City], max( [Year] ) as maxYear
  FROM [T09a: CDFI Award Records]
  GROUP BY Awardee, ST, City
) as x
ON x.Awardee = t.Awardee 
  AND x.[ST] = t.[ST]
  AND x.[City] = t.[City]
  AND t.[Year] < x.maxYear)
GROUP BY t.[Awardee], t.[ST], t.[City]
sql ms-access where-clause offset

1 回答

  • 1

    我无权访问MS，
    但是我会告诉你使用基本的SQL功能在MySql上做到这一点：JOIN GROUP BY MAX：

    SELECT t.Awardee, t.State, t.City, max( t.Year ) as Year
FROM table1 t
JOIN (
  SELECt Awardee, State, City, max( Year ) as Year
  FROM table1
  GROUP BY Awardee, State, City
) x
ON x.Awardee = t.Awardee 
   AND x.State = t.State
   AND x.City = t.City
   AND t.Year < x.year
GROUP BY t.Awardee, t.State, t.City

    演示：http://sqlfiddle.com/#!9/690e47/7

    我相信你会设法让它在MS Access上运行 .

    回复于

相关问题