主页 / dba / 问题 / 316633
Accepted
Jefferson
Asked: 2022-09-09 06:14:18 +0800 CST

比较两个表并使用 endDate 更新

  • 772

我有两个表 CustomerProfile 和 NewData。CustomerProfile 有 CustomerId、ProfileId、startDate 和 endDate。NewData 表是传入的新数据，只有 ProfileId。我创建了一个临时表，其中包含两个名为 #groupedProfileIds 的表的 id。然后我循环临时表检查 id 是否在任何主表中。然后根据我执行 insert 或 update 的计数。我曾尝试使用 while 并检查两个表的计数，但想看看是否有更好的方法。

这样做的目的是在 newData 表的基础上更新 CustomerProfile 表。如果 id 不在 newData 表中，则 CustomerProfile 表将更新结束日期。如果它们是新的 ID，它们将被插入一个开始日期。我使用'@OldProfileId = 1 和@NewProfileId = 0'进行更新，因为数据不在新表中。对于插入，我执行“@OldProfileId = 0 和 @NewProfileId = 1”，因为数据就在 newData 中。我不需要检查 1 ，因为它们都在 table 中。

http://sqlfiddle.com/#!18/73845

            select * INTO #groupedProfileIds from(
            SELECT ProfileId FROM CustomerProfile
                UNION 
                SELECT ProfileId FROM NewData)  as #tmp

            declare @Rowcount int 
            select @Rowcount=count(*) from #groupedProfileIds
            while( @Rowcount>0)
            begin 
             select @Rowcount=@Rowcount-1;
             DECLARE @ProfileIds int
             set @ProfileIds = (SELECT Id FROM #groupedProfileIds order by Id desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY);
             print @ProfileIds

             DECLARE @OldProfileId INT 
             SET @OldProfileId = (SELECT Count(ProfileId) FROM CustomerProfile where ProfileId = @ProfileIds)

             DECLARE @NewProfileId INT
             SET @NewProfileId = (SELECT Count(ProfileId) FROM #NewData where ProfileId = @ProfileIds)

             IF @OldProfileId = 1 and @NewProfileId = 0
             BEGIN
                update DBO.CustomerProfile set endDate = GETDATE() where ProfileId = @ProfileIds 

             end

             IF @OldProfileId = 0 and @NewProfileId = 1
             BEGIN
                INSERT INTO DBO. CustomerProfile (CustomerId, ProfileId,startDate)
                VALUES (CustomerId,@ProfileIds,GETDATE())
             end
sql-server sql-server-2016

2 个回答

  1. 我不确定我是否以正确的方式理解了您的问题，但如果我的理解是正确的，下面的查询将比较两个表的 ProfileId 并将 endDate 字段更新为当前日期，它还会插入 newdata 表中不存在的 profileIds CustomerProfile（customerId 字段对他们不可用）并用当前日期填充 StartDate 字段：

    drop table if exists #groupedProfileIds
drop table if exists #groupedProfileIds_agg
select * INTO #groupedProfileIds 
from(SELECT ProfileId ,1 as flag FROM CustomerProfile
    UNION 
    SELECT ProfileId, 2 as flag FROM NewData)  as #tmp
-- we use this flag to determine the table which record came from-------
select * INTO #groupedProfileIds_agg
from (select  ProfileId,sum(flag) as flag_agg
      from #groupedProfileIds
      group by ProfileId) #temp2
-- we used second temp table to store summation of flag to avoid using aggregate function in where clause  
-- when flag is 1  it shows the ProfileId only exists in CustomerProfile, 
--when it is 2 the ProfileId only exists in NewData and it can be 3 if same profileId is available in both ----------

update DBO.CustomerProfile set endDate = GETDATE()
        where  ProfileId in (select ProfileId from #groupedProfileIds_agg
                                where flag_agg = 1) 
    
insert INTO DBO. CustomerProfile (ProfileId ,startDate) 
select ProfileId,getdate()
from #groupedProfileIds_agg
    where flag_agg=2
    • 1
  2. Best Answer

    最好的方法是创建一个包含所需数据的查询。然后您可以决定插入什么以及更新什么。

    如果配置文件 ID 不在 CustomerProfile 中，您想插入一个带有 customerId 的新行，您不会说您从哪里获得该 customerId，因为您所拥有的只是一个 profileId？

    您指定需求的缺陷是您找不到解决方案的原因。没有人能帮助你，因为你没有说出你想要的。突然在某处插入“癌症”会使情况变得更糟！

    没有人能回答你的问题，因为你没有说出你想要的。但是，如果我们做出一些假设，那么我们所做的最好的事情就是勾勒出一个你可以去的方向。

    我们能从你不完整的表模式中猜到什么吗？

    CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer NULL,
  profileId         integer,
  startDate         datetime,
  endDate           datetime NULL
)
  
CREATE TABLE NewData (
  profileId         integer
)

    这样写应该有一个Customer表和一个Profile表

    CREATE TABLE Customer (
  customerId        integer PRIMARY KEY,
  ...
)
  
CREATE TABLE Profie (
  profileId         integer PRIMARY KEY,
  ...
)

    那么你提到的表格应该完成为：

    CREATE TABLE CustomerProfile (
  customerProfileId integer PRIMARY KEY,
  customerId        integer REFERENCES Customer(customerId),
  profileId         integer REFERENCES Profile(profileId),
  startDate         timestamp,
  endDate           timestamp
)
  
CREATE TABLE NewData (
  profileId         integer REFERENCES Profile(profileId)
)

    我首先保留了您的 customerId NULL 非约束，让自己不必担心插入时如何从单纯的 profileId 推断出 customerId。但后来我想可能只是完成你的模式，让它合乎逻辑。这会创建额外的任务来插入新的客户和配置文件行，我知道我该怎么做，但你没有要求，所以我不会这样做。

    所以以上只是为了让我了解你想要的东西。你还是说得不够充分。

    我从您的表格中假设您的客户和个人资料之间的关系是多对多的。我对此表示怀疑。我认为您还没有真正考虑过您的业务数据逻辑。但对于我提出的建议，这并不重要。

    我也对 SQL Server 的东西不感兴趣，但我会使用标准 SQL（我的数据库是 PostgreSQL）。如果您不认为 SQL（语言，而不是您的软件），那么无论您想如何称呼自己，您都不会成为一名优秀的数据库设计师/程序员/管理员。

    在标准 SQL 中，“datetime”被称为“timestamp”，获取当前时间戳的标准方法是 current_timestamp。

    关于你的 endDate 你说：

    如果 id不在newData 表中，则 CustomerProfile 表将更新结束日期。

    所以当插入你的表时，我假设 startDate = current_timestamp 和 endDate = NULL。

    最后，我假设这些 id 是“序列”，但我将放弃对任何数据库的任何序列扩展，只确定下一个值

    从 Foo 中选择 max(fooId) + 1

    其中“Foo”可以是客户、配置文件或客户配置文件。

    有了所有这些假设，我开始，正如我所说，你应该总是开始的方式。编写一个 SELECT 它将为您提供最终所需的表，或者，在这里，我从 INSERT 部分开始：

    INSERT INTO CustomerProfile(customerProfileId, customerId, profileId, startDate, endDate)
WITH DistinctNewProfileIds AS (
  SELECT DISTINCT profileId FROM NewTable
), MaxCustomerProfileId AS (
  SELECT max(customerProfileId) AS maxCustomerProfileId
    FROM CustomerProfile
), MaxCustomerId AS (
  SELECT max(customerId) AS maxCustomerId
    FROM Customer
), ProfileIdWithOrdinal AS (
  SELECT rank() OVER (ORDER BY profileId) AS ordinal,
         profileId
    FROM DistinctNewProfileIds
)
SELECT maxCustomerProfileId + ordinal AS customerProfileId,
       maxCustomerId + ordinal AS customerId,
       profileId,
       current_timestamp AS startDate,
       NULL AS endDate
  FROM ProfileIdWithOrdinal 
  CROSS JOIN MaxCustomerProfileId
  CROSS JOIN CustomerId

    现在更新。您似乎希望所有尚未结束的 profileId 每次都出现在您的 NewData 中。我最初了解到，每次在 NewData 中找到 profileId 时，您都会扩展 endDate。这让我想知道如果 profileId 在您已经错过一次并且已经添加了 endDate 之后突然重新出现在 NewData 中会发生什么？您是否应该再次用 NULL 清除 endDate ？你没有要求，所以我不会这样做。但如果你不考虑这一点，你就没有考虑过你的设计。

    这是您在更新后更新的表的分区：

    WITH DistinctNewProfileIds AS (
  SELECT DISTINCT profileId FROM NewTable
)
SELECT customerProfileId, customerId, profileId, 
       startDate, 
       CASE WHEN p.profileId IS NULL
            THEN current_timestamp
            ELSE NULL END as endDate
  FROM CustomerProfile cp
  LEFT OUTER JOIN DistinctNewProfileIds p
    ON(p.profileId = cp.profileId)

    因此，如果 profileId 重新出现，我实际上会将您的 endDate 重置为 NULL 。但无论如何更新变得非常简单：

    UPDATE CustomerProfile 
   SET endDate = current_timestamp
 WHERE profileId NOT IN (SELECT profileId FROM NewTable)

    您首先运行 UPDATE 部分，然后运行 ​​INSERT。你完成了。

    对您的要求的最佳描述是一个 SELECT 查询，它将满足您的需求。然后，您 INSERT 与 UPDATE 的内容只是次要问题。如果您还必须插入 Customer 和 Profile 行以满足外键约束，则更是如此。在 PostgreSQL 中，我在一个查询中完成这些事情。在 Oracle 或 MS SQL Server 等较小的数据库中，我猜您必须在事务中执行多个步骤。

    • 1

相关问题