查询/查看：每个指标的2个最新周期 [英] Query/View: The 2 newest periods for each indicator
问题描述
我正在制作一个简单的表演程序，我需要从每个2个最新时段中提取
信息性能指标
- 从那里计算出这些结果之间的趋势。
问题是，我找不到一种简单的提取方法最新的2个
结果。
表（表1）如下所示：
kpiID periodID Actual
加速2 3
加速5 4
速度1 100
速度4 200
速度7 220
速度9 180
重量1 22
重量3 32
重量7 21
重量10 33
如果我想提取最新的，我会使用类似这样的东西（在
MS Access中制作，所以语法可能略有不同来自SQLServer）：
SELECT table1.kpiID，table1.periodID，table1.Actual
FROM table1 WHERE table1.periodID =（SELECT max（t.periodID） ）来自
table1为t WHERE t.kpiID = table1.kpiID）;
但是我如何获得第二个最新时期？
我希望最终结果是以下字段为
的视图：
kpiID，periodID_newest，Actual_newest，periodID_sec_newest，
Actual_sec_newest
另外一个视图，每个性能指标有2个帖子。
提前致谢
Ryan
2006年3月24日星期五23:08:18 +0100，Ryan Dahl写道：
< blockquote class =post_quotes>
我正在研究一个简单的性能程序，我需要从每个性能指标的2个最新周期中提取
信息br /> - 从那里计算出这些结果之间的趋势。
问题是，我找不到一个简单的方法来提取最新的两个结果。
表格（表1）如下：
kpiID periodID实际
加速2 3
加速5 4
速度1 100
速度4 200
速度7 220
速度9 180
重量1 22
重量3 32
重量7 21
重量10 33
如果我想提取最新的我使用这样的东西（在
MS Access，因此语法可能与SQLServer略有不同）：
SELECT table1.kpiID，table1.periodID，table1.Actual
FROM table1 WHERE table1.periodID =（SELECT max（t .periodID）来自
table1作为t WHERE t.kpiID = table1.kpiID）;
但是我如何获得第二个最新时期呢？
嗨Ryan，
SELECT a.kpiID，a.periodID，a.Actual
FROM table1 AS a
WHERE（SELECT COUNT（*）
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID> = a.periodID）< = 2
我希望最终的结果是带有
以下字段的视图：
kpiID ，periodID_newest，Actual_newest，periodID_sec_newest，
Actual_sec_newest
在这种情况下，请尝试这样做：
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
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）
（上述两个查询均未经过测试 - 请参阅 www.aspfaq.com/5006 你更喜欢
a测试回复）。
-
Hugo Kornelis，SQL Server MVP
你好雨果，
非常感谢。我让他们都没有任何麻烦。
SELECT a.kpiID，a.periodID，a.Actual
FROM table1 AS
WHERE（SELECT COUNT（ *）
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID> = a.periodID）< = 2
我觉得这很聪明 - 不得不看一下它来确定它是如何运作的。
< blockquote class =post_quotes>
我希望最终结果是带有以下字段的视图：
kpiID，periodID_newest，Actual_newest，periodID_sec_newest，
Actual_sec_newest
在这种情况下，请尝试以下方法：
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
请发布DDL，以便人们不必猜测
$ b中的键，
约束，声明性参照完整性，数据类型等等$ b你的架构是。样本数据也是一个好主意，同时还有明确的
规格。
我对此有点困惑。 Aren''t加速度，速度和
重量"属性而不是值？当然你没有混合
meteadata和数据。
Hi,
I''m working on a simple performance-program, where I need to extract
information 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 latest
results.
The Table (Table1) looks like this:
kpiID periodID Actual
Acceleration 2 3
Acceleration 5 4
Speed 1 100
Speed 4 200
Speed 7 220
Speed 9 180
Weight 1 22
Weight 3 32
Weight 7 21
Weight 10 33
If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):
SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 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 the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest
Alternatively a View with 2 posts for each performace-indicator.
Thanks in advance
Ryan
On Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote:
Hi,
I''m working on a simple performance-program, where I need to extract
information 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 latest
results.
The Table (Table1) looks like this:
kpiID periodID Actual
Acceleration 2 3
Acceleration 5 4
Speed 1 100
Speed 4 200
Speed 7 220
Speed 9 180
Weight 1 22
Weight 3 32
Weight 7 21
Weight 10 33
If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):
SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);
BUT - how how do I get the second-newest period as well?
Hi Ryan,
SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2
Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest
In that case, try this instead:
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
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)
(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).
--
Hugo Kornelis, SQL Server MVP
Hi Hugo,
Thanks a lot. I got them both working without any hassle.
SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2
I find this to be quite clever - had to look at it some time to figure
out how it works.
Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest
In that case, try this instead:
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
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.
Regards
Ryan
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
I am a little confused on this. Aren''t "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.
这篇关于查询/查看：每个指标的2个最新周期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
