Sql Server 获取第一个匹配值

Question

我有两个表 History 和 Historyvalues:

历史

HID(uniqeidentifier) | Version(int)
a1 | 1
a2 | 2
a3 | 3
a4 | 4

历史值(value)

HVID(uniqeidentifier) | HID(uniqeidentifier) | ControlID(uniqeidentifier) | Value(string) 
b1 | a1 | c1 | value1 
b2 | a2 | c1 | value2 
b3 | a2 | c2 | value3

现在我需要一个查询，我可以在其中获取包含特定版本中每个控件的最后一个历史值的列表，例如:

从版本 3 获取最后一个值 -> 接收 ->

HVID | ControlID | Value
b2 | c1 | value2
b3 | c2 | value3

我试过这样的事情:

Select HVID, ControlId, max(Version), Value from 
(   
    Select HVID, ControlId, Version, Value
    from History inner JOIN
         Historyvalues ON History.HID = Historyvalues.HID
    where Version <= 3  
) as a
group by ControlId
order by Version desc

但这不起作用。

有什么想法吗？

非常感谢您的帮助。

此致

Answer 1

每个控件的最新版本与您的特定版本 ( WHERE t1.Version <= 3 )

询问:

SQLFIDDLEExample

SELECT HVID, ControlId, Version, Value 
FROM
(   
    SELECT t2.HVID, t2.ControlId, t1.Version, t2.Value,
           ROW_NUMBER() OVER(PARTITION BY t2.ControlId ORDER BY t1.Version DESC) as rnk
    FROM History t1 
     JOIN Historyvalues t2 
      ON t1.HID = t2.HID
    WHERE t1.Version <= 3  
) AS a
WHERE a.rnk = 1
ORDER BY a.Version desc

结果:

| HVID | CONTROLID | VERSION |  VALUE |
|------|-----------|---------|--------|
|   b2 |        c1 |       2 | value2 |
|   b3 |        c2 |       2 | value3 |