如何在没有游标的情况下获取一些记录，而无需在 T-SQL 中交叉应用

Question

我有一个名为Objects的表，其中包含一些文件，例如：

1. 用户
2. 老师

还有另一个表（States）保存这些对象的可能状态，例如：

1. 积极
2. 怠
3. 教学
4. 休息
5. 创作

还有第三个表（联结表），它记录每个对象的每个状态更改。在第三个表（ObjectStates）中，记录如下：

1， 1， 日期时间 1
1. （用户在日期时间 1 上处于活动状态）
2， 5， 日期时间 2
2. （教师在日期时间 2 上创作）

等。

现在，我想要的是一个查询来获取每个对象及其最新状态（不是状态历史记录）。可以使用 cursors 或Cross Apply命令获取此结果。但是，我想知道是否有其他方法可以从这三个表中获取每个对象的最新状态？因为cursors很贵。

Answer 1

使用row_number()窗口函数...

select * 
from
(
select objects.*,
       state.state,
       objectstates.changedate,
       row_number() over (partition by object.objectid order by changedate desc) rn
from 
    objects
         inner join
    objectstates
         on objects.id = objectstates.objectid
         inner join
    states 
         on objectstates.stateid = states.stateid
) v
where rn = 1

例如，如果由于使用的是 SQL 2000 而无法使用row_number，则可以对max/group by查询使用联接。

select objects.*,
       state.state,
       objectstates.changedate,
from 
    objects
         inner join
    objectstates
         on objects.id = objectstates.objectid
         inner join
    states 
         on objectstates.stateid = states.stateid
    inner join
         (select objectid, max(changedate) as maxdate from objectstates group by objectid) maxstates
         on objectstates.objectid=maxstates.objectid
         and objectstates.changedate = maxstates.maxdate

Answer 2

您可以在ObjectStates桌上加入两次。表的第一个连接将获得每个objectid的max(activedate)。 第二次，您将同时加入objectid和max(activedate)的值，这将获得与该值关联的state：

select o.name o_name,
  s.name s_name,
  os1.activedate
from objects o
left join
(
  select max(activeDate) activedate, objectid
  from objectstates
  group by objectid
) os1
  on o.id = os1.objectid
left join ObjectStates os2
  on os1.objectid = os2. objectid
  and os1.activedate = os2.activedate
left join states s
  on os2.stateid = s.id

请参阅带有演示的 SQL 小提琴

Answer 3

您可以使用分区来查找每个对象的最新行，如下所示

create table #ObjectState
(
    Object int NOT NULL,
    State int NOT NULL,
    TimeStamp datetime NOT NULL
)
INSERT INTO #ObjectState (Object, State, TimeStamp) VALUES (1, 1, '2012-01-01')
INSERT INTO #ObjectState (Object, State, TimeStamp) VALUES (1, 2, '2012-01-02')
INSERT INTO #ObjectState (Object, State, TimeStamp) VALUES (1, 3, '2012-01-03')
INSERT INTO #ObjectState (Object, State, TimeStamp) VALUES (2, 4, '2012-01-01')
INSERT INTO #ObjectState (Object, State, TimeStamp) VALUES (2, 2, '2012-01-02')
select *, ROW_NUMBER() over (partition by Object order by TimeStamp desc) as RowNo from #ObjectState
select InnerSelect.Object, InnerSelect.State, InnerSelect.TimeStamp FROM
(
select *, ROW_NUMBER() over (partition by Object order by TimeStamp desc) as RowNo from #ObjectState
) InnerSelect
where InnerSelect.RowNo = 1

DROP TABLE #ObjectState

给出输出

Object  State  TimeStamp
1       3      2012-01-03 00:00:00.000
2       2      2012-01-02 00:00:00.000

对于最后一个选择

Answer 4

在过去，我们只是使用标量子查询。

select o.*, (select top(1) s.description
               from objectstates os
               join states s on s.id = os.state_id
              where os.object_id = o.id
           order by os.recorded_time desc) last_state 
  from objects o;

哪个交叉应用替换。 为了将其扩展到更多领域，必须扩展如下

select *
  from (
select o.*, (select top(1) os.id
               from objectstates os
              where os.object_id = o.id
           order by os.recorded_time desc) last_state 
  from objects o
       ) x
   join objectstates os on os.id = x.last_state
   join states s on s.id = os.state_id;