时间戳

首页 > 解决方案 > 更改子查询以加入以更新表

sql - 更改子查询以加入以更新表

问题描述

我试图将子查询转换为加入更新语句，以便对其进行调整以防止死锁。我试图获得确切的更新是徒劳的，因为我必须获得前 1 名。

-- 创建关联表并插入示例数据

CREATE TABLE [dbo].[Associate](
    [pre_plan_id] [smallint] NULL,
    [pre_type_id] [smallint] NULL,
    [associate_id] [smallint] NOT NULL,
    [deleted] [bit] NOT NULL
)  

INSERT INTO Associate 
VALUES 
(NULL,  NULL,   -32768, 0),
(NULL,  NULL,   2,  1),
(NULL,  NULL,   3,  0)

-- 创建访问表并插入样本数据

CREATE TABLE [dbo].[Visit](
    [type_id] [smallint] NOT NULL,
    [plan_id] [smallint] NOT NULL,
    [associate_id] [smallint] NOT NULL,
    [time_in] [smalldatetime] NOT NULL
) 
INSERT INTO Visit 
VALUES
(390,   31, 3109,   '2009-09-02'),
(304,   32, 3109,   '2010-02-05'),
(388,   31, 3109,   '2010-09-24')

--- 更新声明

UPDATE Associate SET pre_plan_id = 
      (SELECT TOP 1 plan_id 
         FROM Visit  
        WHERE associate_id = Associate.associate_id 
          AND time_in > 90
        GROUP BY plan_id 
        ORDER BY Count(*) DESC)
 WHERE deleted = 0

谁能帮我这个？谢谢！

标签： sqlsql-serverjoinsubquery

解决方案

试试这个：

UPDATE a 
SET a.pre_plan_id = z.plan_id
FROM
  associate a
  INNER JOIN
  (
    SELECT associate_id, plan_id 
    FROM
    (
      SELECT associate_id, plan_id, ROW_NUMBER() OVER(PARTITION BY associate_id ORDER BY ctr DESC) rown
      FROM (SELECT associate_id, plan_id, count(*) as ctr FROM visit WHERE time_in > 90 GROUP BY associate_id, plan_id) x
    ) y
    WHERE rown = 1
  ) z
  ON z.associate_id = a.associate_id
WHERE deleted = 0

它只有 3 个子查询，因为我不记得是否可以在窗口函数中使用分组计数（*）。如果是，这可能有效：

UPDATE a 
SET a.pre_plan_id = z.plan_id
FROM
  associate a
  INNER JOIN
  (
    SELECT associate_id, plan_id 
    FROM
    (
      SELECT associate_id, plan_id, ROW_NUMBER() OVER(PARTITION BY associate_id ORDER BY COUNT(*) DESC) rown
      FROM visit 
      WHERE time_in > 90 
      GROUP BY associate_id, plan_id
    ) y
    WHERE rown = 1
  ) z
  ON z.associate_id = a.associate_id
WHERE deleted = 0

甚至这个，更短的形式：

UPDATE a 
SET a.pre_plan_id = z.plan_id
FROM
  associate a
  INNER JOIN
  (
      SELECT associate_id, plan_id, ROW_NUMBER() OVER(PARTITION BY associate_id ORDER BY COUNT(*) DESC) rown
      FROM visit 
      WHERE time_in > 90 
      GROUP BY associate_id, plan_id
  ) z
  ON z.associate_id = a.associate_id AND z.rown = 1
WHERE deleted = 0

看来您想计算访问次数并选择 plan_id 出现次数最多的访问次数，然后使用它来填充 pre_plan_id。通过分组并获取计数，然后对计数进行 row_number() 递减，这意味着最高计数将为 1，我们可以对其进行过滤并提取相关的 plan_id 作为 pre_plan_id

推荐阅读