向数据库表添加每日更改

Question

我试图建立一个简单的数据库，跟踪用户位置属性的任何变化。每天生成用户、日期、位置的当前信息，并上传到sql server中的临时表中。我正在尝试找出正确的sql来查询新用户，修改用户和删除用户。

查找新用户很容易:

SELECT table1.UserGuid,table1.Location
FROM table1
WHERE table1.UserGuid NOT IN 
(
  SELECT DISTINCT table2.UserGuid
  FROM table2
)

我遇到的问题是找到修改的位置和删除的用户。

对于修改的用户，我试图返回他们在数据库中的最后位置与每日临时数据库中的当前位置不匹配的用户。这是我所拥有的，但我认为它是不正确的:

SELECT table1.UserGuid,table1.Location
FROM table1
WHERE EXISTS 
(
  SELECT TOP 1 table2.UserGuid,table2.Location
  FROM table2
  WHERE (table2.UserGuid = table1.UserGuid) AND (table2.Location != table1.Location)
  ORDER BY table2.Date DESC
)

对于已删除的用户，我正在尝试以下sql来识别主表中不存在的任何用户，并且没有删除的位置。(如果返回任何已删除的用户，那么我将它们添加到主表中，位置为deleted，因此下次不会返回它们)

SELECT table2.UserGuid,table2.Location
FROM table2
WHERE table2.UserGuid NOT IN
(
  SELECT UserGuid
  FROM table1
)
AND table2.Location != 'deleted'

在我运行所有3个查询以查找添加，修改和删除后，我将它们与当前日期一起添加到主表中，并在第二天重复。因此，主表有3列(UserGuid, Date, Location)，并且每天添加新的行，其中包含更改的信息。到目前为止，我的新用户sql是唯一一个可靠的工作。有更简单的方法吗?

Answer 1

所以我认为这抓住了你所有的需求。

    Select 
table1.*,
    case when table2.userguid is null then 'INSERT'
    when table1.userguid is null and table2.location != 'deleted' then 'DELETE'
    when table1.location != table2.location then 'UPDATE'
     from table1
    full join (select max(date) as lastEntry, userGuid from Table2) lastRecords
    inner join table2 on table2.date = lastRecords.lastEntry and table2.userGuid = lastRecords.userGuid
     on lastRecords.userguid = table1.userguid

Answer 2

对于第二个查询，尝试:

SELECT table1.UserGuid,table1.Location
FROM table1
WHERE table1.UserGuid IN
(
  SELECT table2.UserGuid
  FROM table2
  WHERE table2.UserGuid = table2.UserGuid AND table2.Location <> table2.Location
)

Answer 3

对于这类检查，我倾向于使用EXISTS

    --INSERTS
    SELECT table1.UserGuid,table1.Location
    FROM table1
    WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.UserGuid = table1.UserGuid)
    UNION ALL
    --UPDATES
    SELECT table1.UserGuid,table1.Location
    FROM table1
    WHERE EXISTS 
    (
        SELECT 1 FROM table2 
        WHERE table2.UserGuid = table1.UserGuid 
        AND ISNULL(table2.Location,'') <> ISNULL(table1.Location,'')
    )
    UNION ALL
    --DELETES
    SELECT table2.UserGuid,table2.Location
    FROM table2
    WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table2.UserGuid = table1.UserGuid)

我在你的位置可能为空的事件中包含了ISNULL检查;