查询/查看：每个指标的2个最新周期

FROM table1 AS t WHERE t.kpiID = a.kpiID） （上述两个查询均未经过测试 - 请参阅 www.aspfaq.com/5006 你更喜欢 a测试回复）。 - Hugo Kornelis，SQL Server MVP 我觉得这很聪明 - 不得不看一下它来确定它是如何运作的。 在这种情况下，请尝试以下方法： SELECT a.kpiID，a.periodID，a.Actual，b.periodID，b .Actual FROM table1 AS a LEFT JOIN table1 AS b ON b.kpiID = a.kpiID AND b.periodID =（SELECT MAX（c.periodID） FROM table1 AS c 在哪里c.kpiID = a.kpiID AND c.periodID< a.periodID） WHERE.periodID =（SELECT MAX（t.periodID） FROM table1 AS t WHERE t.kpiID = a.kpiID） 也适用 - 需要进行小调整：将第5-8行移至末尾。 问候 Ryan Hi, I''m working on a simple performance-program, where I need to extractinformation from the 2 newest periods for every performance-indicator- And from there calculate a trend between these results. The problem is, that I can''t find a simple way to extract the 2 latestresults. The Table (Table1) looks like this:kpiIDperiodIDActualAcceleration23Acceleration54Speed1100Speed4200Speed7220Speed9180Weight122Weight332Weight721Weight1033 If I want to extract the newest I use something like this (made it inMS Access, so the syntax might differ slightly from SQLServer): SELECT table1.kpiID, table1.periodID, table1.ActualFROM table1 WHERE table1.periodID = (SELECT max(t.periodID) fromtable1 as t WHERE t.kpiID=table1.kpiID); BUT - how how do I get the second-newest period as well? Preferably I would like the final result to be a View with thefollowing fields:kpiID, periodID_newest, Actual_newest, periodID_sec_newest,Actual_sec_newest Alternatively a View with 2 posts for each performace-indicator. Thanks in advanceRyan 解决方案 In that case, try this instead: SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.ActualFROM table1 AS aLEFT JOIN table1 AS bON b.kpiID = a.kpiIDAND b.periodID = (SELECT MAX(c.periodID)FROM table1 AS cWHERE c.kpiID = a.kpiIDAND c.periodID < a.periodID)WHERE a.periodID = (SELECT MAX(t.periodID)FROM table1 AS tWHERE t.kpiID = a.kpiID) (Both queries above are untested - see www.aspfaq.com/5006 if you prefera tested reply). --Hugo Kornelis, SQL Server MVP I find this to be quite clever - had to look at it some time to figureout how it works. In that case, try this instead:SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.ActualFROM table1 AS aLEFT JOIN table1 AS b ON b.kpiID = a.kpiID AND b.periodID = (SELECT MAX(c.periodID) FROM table1 AS c WHERE c.kpiID = a.kpiID AND c.periodID < a.periodID)WHERE a.periodID = (SELECT MAX(t.periodID) FROM table1 AS t WHERE t.kpiID = a.kpiID)Works as well - minor adjustment needed: Move lines 5-8 to the end. RegardsRyan 这篇关于查询/查看：每个指标的2个最新周期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！