SQL Server - 使用另一个表中的第一行和最后一行更新一个表

I have a couple of tables which are used to log user activity for an application. The tables looks something like this (pseudo code from memory, may not be syntactically correct):

我有几个表用于记录应用程序的用户活动。这些表看起来像这样(来自内存的伪代码,可能在语法上不正确):

create table activity (
  sessionid uniqueidentifier not null,
  created smalldatetime not null default getutcdate()
);

create table activity_details (
  sessionid uniqueidentifier not null,
  activity_description varchar(100) not null,
  created smalldatetime not null default getutcdate()
);

My goal is to populate a summary table for reporting purposes that looks something like this:

我的目标是填充汇总表以用于报告目的,如下所示:

create table activity_summary (
  sessionid uniqueidentifier not null,
  first_activity_desc varchar(100) not null,
  last_activity_desc varchar(100) not null
);

First and last activity descriptions would be determined chronologically. My initial thought is to update the summary table like so:

第一个和最后一个活动描述将按时间顺序确定。我最初的想法是更新摘要表,如下所示:

truncate table activity_summary;

insert into activity_summary (sessionid)
select sessionid from activity;

update table activity_summary set
  first_activity_desc = (select top 1 activity_desc from activity_detail where sessionid = as.sessionid order by created asc),
  last_activity_summary = (select top 1 activity_desc from activity_detail where sessionid = as.sessionid order by created desc)
from activity_summary as;

However, this seems incredibly verbose and unnecessary to me. I'm just not sure how to shrink it down. My gut feel is that I could do it somehow all within the insert statement, but I'm stumped. Any suggestions?

然而,这对我来说似乎非常冗长和不必要。我只是不确定如何缩小它。我的直觉是我可以在insert语句中以某种方式完成它,但我很难过。有什么建议?

4 个解决方案

#1

There's probably more efficient ways to do this as well, but this is closest to your original:

也许有更有效的方法来做到这一点,但这最接近你原来的:

truncate table activity_summary;

insert into activity_summary (sessionid, first_activity_desc, last_activity_summary)
select a.sessionid
,(select top 1 ad.activity_desc from activity_detail AS ad where ad.sessionid = a.sessionid order by ad.created asc) AS first_activity_desc
,(select top 1 ad.activity_desc from activity_detail AS ad where ad.sessionid = a.sessionid order by ad.created desc) AS last_activity_summary
from activity AS a;

Something like this might be more efficient:

这样的事情可能更有效:

truncate table activity_summary;

WITH firsts AS (
    SELECT ad.sessionid
        ,ad.activity_desc
        ,ROW_NUMBER() OVER (ORDER BY ad.created ASC) as RowNumber
    FROM activity_detail AS ad
)
,lasts AS (
    SELECT ad.sessionid
        ,ad.activity_desc
        ,ROW_NUMBER() OVER (ORDER BY ad.created DESC) as RowNumber
    FROM activity_detail AS ad
)
insert into activity_summary (sessionid, first_activity_desc, last_activity_summary)
select a.sessionid
    ,firsts.activity_desc
    ,lasts.activity_desc
from activity AS a
INNER JOIN firsts ON firsts.sessionid = a.sessionid AND firsts.RowNumber = 1
INNER JOIN lasts ON lasts.sessionid = a.sessionid AND lasts.RowNumber = 1

#2

insert into activity_summary
    (sessionid, first_activity_desc, last_activity_desc)
select
    agg.sessionid,
    adf.activity_description,
    adl.activity_description
from
    (SELECT
         sessionid, MIN(created) as firstcreated, MAX(created) as lastcreated
    from
         activity_detail group by sessionid
    ) agg
    JOIN
    activity_details adf ON agg.sessionid = adf.sessionid AND agg.firstcreated = adf.created
    JOIN
    activity_details adl ON agg.sessionid = adl.sessionid AND agg.lastcreated = adl.created

#3

Roughly,

INSERT etc.

SELECT a.sessionid, d1.activity_description, d2.activity_description

SELECT a.sessionid,d1.activity_description,d2.activity_description

FROM activity a

来自活动a

JOIN detail d1 ON a.sessionid = d1.sessionid JOIN detail d2 ON a.sessionid = d2.sessionid

加入详细信息d1 on a.sessionid = d1.sessionid JOIN detail d2 ON a.sessionid = d2.sessionid

WHERE NOT EXISTS
(SELECT 1 FROM detail WHERE sessionid = a.sessionid AND created < d1.created)

什么不存在(SELECT 1 FROM FROM WHERE sessionid = a.sessionid AND created )

AND NOT EXISTS
(SELECT 1 FROM detail WHERE sessionid = a.sessionid AND created > d2.created)

AND NOT EXISTS(SELECT 1 FROM FROM WHERE sessionid = a.sessionid AND created> d2.created)

#4

Or,

INSERT etc.

(SELECT description FROM detail d1 WHERE d1.sessionid = a.sessionid
AND NOT EXISTS (SELECT 1 FROM detail WHERE created < d1.created)) AS desc1,

(SELECT description FROM detail d1 WHERE d1.sessionid = a.sessionid AND NOT EXISTS(SELECT 1 FROM detail WHERE created ))as>

(SELECT description FROM detail d2 WHERE d2.sessionid = a.sessionid
AND NOT EXISTS (SELECT 1 FROM detail d2 WHERE created > d1.created)) AS desc2

(SELECT description FROM detail d2 WHERE d2.sessionid = a.sessionid AND NOT EXISTS(SELECT 1 FROM detail d2 WHERE created> d1.created))AS desc2

FROM activity a

来自活动a

(I prefer this one myself.)

(我自己更喜欢这个。)