将用户服务的最新状态插入到SQL Server中的另一个表中

Question

我在SQL数据库中有一个表，并为所有用户插入服务激活日志。例如:服务a今天为mike停用，然后明天为他激活，然后再次停用。我需要在另一个表中为用户提供每个服务的最后状态。(唯一约束:userid,serviceid)

logid  |  userid     |  serviceid  |  status
-------+-------------+-------------+--------
1      |  mike       |      a      |      0
2      |  mike       |      b      |      1
3      |  mike       |      b      |      0
4      |  mike       |      a      |      1
5      |  Dave       |      c      |      1
6      |  Dave       |      a      |      0
7      |  mike       |      d      |      1
8      |  mike       |      c      |      1
9      |  mike       |      a      |      0

例如:在上面的表格中，我需要有以下表格:

userid     |  serviceid  |  laststatus
-------+-------------+-------------+--------
mike       |      a      |      0
mike       |      b      |      0
mike       |      c      |      1
mike       |      d      |      1
Dave       |      c      |      1
Dave       |      a      |      0

是否有while循环从table1中读取所有记录，并插入或更新table2以存储每个服务的最新状态?这样的:

while (select * from table1)
{
  IF EXISTS    
    (
    SELECT 1   
    FROM table2 
    where table2.userid=table1.userid and table2.serviceid=table1.serviceid
        )    
    BEGIN
      UPDATE table2  
      SET  table2.laststatus = table1.status 
      WHERE table2.userid=@userid and table2.serviceid=table1.serviceid
    END
ELSE
    INSERT INTO table2 VALUES ( table1.userid  ,table1.serviceid ,table1.status  )
}

最后状态是给定用户和服务的最大逻辑gid的状态

Answer 1

这可以通过MERGE语句解决，如下所示:

merge table2 as tgt
using
(
 select * from table1 
 where logid in
 (
  --Get the row which has the last status for that user-service combination
  select distinct max(logid) over (partition by userid, serviceid order by userid,serviceid) mid 
  from table1)
) as src
on tgt.userid = src.userid and tgt.serviceid = src.serviceid
when matched then
update
set tgt.[status] = src.[status]
when not matched then
insert (userid,serviceid, [status])
values(src.userid, src.serviceid, src.[status]);        

演示。

Answer 2

在表1上创建一个INSERT触发器，用于更新表2。

触发器将更新表2的laststatus。您可以像这样获取特定用户的最后状态:

declare @status int
select  top 1 @status = status from Table1 where userid = 'dave' and serviceid = 'c' 
order by logid desc
select @status  

希望能有所帮助。

Answer 3

我相信下面的代码会给你带来你正在寻找的结果。
使用下面的代码，您将在表2中插入表1中的所有记录，我不知道这是否是您正在寻找的场景。否则，您可以将它与MERGE语句组合为@shree。pat18建议。

WITH cte AS 
(
 SELECT
   userid
  ,serviceid
  ,status
  ,ROW_NUMBER() OVER (PARTITION BY userid, serviceid ORDER BY logid DESC) Rnk
 FROM table1
)
INSERT INTO table2
SELECT
  userid
 ,serviceid
 ,status AS laststatus
FROM cte
WHERE Rnk=1

Answer 4

无需触发。不需要合并。不需要partition by或其他疯狂的东西。

insert into my_second_table (userid, serviceid, laststatus)
select t1.userid, t1.serviceid, t2.status
from (
  select userid, serviceid, max(logid) as lastlogid
  from my_first_table
  group by userid, serviceid
) as t1
join my_first_table t2 on t1.lastlogid = t2.logid