使用sql插入语句增加非唯一字段

发布于 2024-10-09 18:44:14 字数 2068 浏览 9 评论 0原文

我有三个表 TelNoRefNoRef_Check_NoRefNoRef_Check_No 分别包含 Telephone_IdEntity_Id 列以及其他列,而 TelNo > 具有 Telephone_IdEntity_Id。我想要做的是将 Ref_Check_No 中的所有记录插入 RefNo 表中。 TelNo 表包含每个Entity_IdTelephone_Id。所有提到的这些列都不是唯一的。

问题是我知道如何从 Ref_Check_No 表插入数据。但是,我似乎无法为每个 Entity_Id 插入 Telephone_Id 值。我想要做的是从 TelNo 表中检索每个 Entity_Id 的最大 Telephone_Id 号码,并为每个 将其增加 1使用插入语句在 RefNo 表中获取 Entity_Id。下面是我希望在 RefNo 表中实现的示例:

             **TelNo Table**                  **RefNo Table**
        Telephone_Id   Entity_Id        Telephone_Id    Entity_Id
             1         ABCD               4              ABCD
             2         ABCD               5              ABCD
             3         ABCD               6              ABCD
             89       EFGH               7              ABCD
             90          EFGH              96             EFGH
             95          EFGH              97             EFGH

基本上,我想要做的是找到每个 Entity_IdTelephone_Id 最大数量强>(ABCD 和 EFGH)来自 TelNo 表,并为 RefNo 表中每个相应的 Entity_Id 将其加 1。

下面是我正在编写的代码,但我似乎无法正确执行。

 INSERT INTO
RefNo
(
Telephone_Id,
Entity_ID,
ContactName
) 
SELECT     
  (SELECT COUNT(*) FROM Ref_Check_No b WHERE(a.Entity_Id = b.Entity_Id) 
    ISNULL ((SELECT MAX(Telephone_Id) AS Telephone_Id FROM TelNo 
    WHERE (Entity_Id = a.Entity_Id)), 0) AS Telephone_Id,

Entity_ID,
ContactName               
FROM         
Ref_Check_No

有没有办法在 RefNo 中插入数据时使用相关子查询找到最大 Telephone_Id 号码,并为每个 Entity_Id 将其增加 1同时上桌?

I have three tables TelNo , RefNo and Ref_Check_No. RefNo and Ref_Check_No has columns Telephone_Id and Entity_Id respectively along with other columns, while TelNo has Telephone_Id and Entity_Id. What I would like to do is to insert all the records from Ref_Check_No into the RefNo table. The TelNo Table contains the Telephone_Id for each Entity_Id. All of these columns mentioned are not unique.

The thing is I know how to insert the data from the Ref_Check_No table. However, I can't seem to insert the Telephone_Id values for each Entity_Id. What I would like to do is to retrieve the MAX Telephone_Id number per Entity_Id from the TelNo table and increment it by 1 for each Entity_Id in the RefNo table using an insert statement. Below is an example of what I would like to achieve in the RefNo Table:

             **TelNo Table**                  **RefNo Table**
        Telephone_Id   Entity_Id        Telephone_Id    Entity_Id
             1         ABCD               4              ABCD
             2         ABCD               5              ABCD
             3         ABCD               6              ABCD
             89       EFGH               7              ABCD
             90          EFGH              96             EFGH
             95          EFGH              97             EFGH

Basically what I want to do is to find the MAX Telephone_Id number per Entity_Id (ABCD and EFGH) from the
TelNo table and increment it by 1 for each corresponding Entity_Id in the RefNo table.

Below is the code that I was working on which I can't seem to get right.

 INSERT INTO
RefNo
(
Telephone_Id,
Entity_ID,
ContactName
) 
SELECT     
  (SELECT COUNT(*) FROM Ref_Check_No b WHERE(a.Entity_Id = b.Entity_Id) 
    ISNULL ((SELECT MAX(Telephone_Id) AS Telephone_Id FROM TelNo 
    WHERE (Entity_Id = a.Entity_Id)), 0) AS Telephone_Id,

Entity_ID,
ContactName               
FROM         
Ref_Check_No

Is there a way to find the MAX Telephone_Id number and increment it by 1 for each Entity_Id using a correlated sub query while inserting data in the RefNo table at the same time?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

九命猫 2024-10-16 18:44:14

我很难理解你的例子。您想要在 RefNo 中为 Ref_Check_No 中的每条记录创建一行,并使用相同的 Entity_ID 和递增的 Telephone_ID 来标记每条记录?

如果是这种情况,请尝试以下操作(对于 MSSQL):

INSERT INTO RefNo (Entity_ID, Telephone_ID)    
SELECT r.Entity_ID, 
  t.MAXID + ROW_NUMBER() OVER(Partition BY r.Entity_ID order by r.Telephone_ID) AS Telephone_ID
FROM ref_check_no r
  INNER JOIN (
    SELECT Entity_ID, Max(Telephone_ID) as MaxID
    FROM TelNo 
      GROUP BY Entity_ID) t 
   ON r.Entity_ID=t.Entity_ID

I am having a hard time understanding your example. You want to create one row in RefNo for each record in Ref_Check_No, labelling each with the same Entity_ID and an increasing Telephone_ID?

If that is the case, try this (for MSSQL):

INSERT INTO RefNo (Entity_ID, Telephone_ID)    
SELECT r.Entity_ID, 
  t.MAXID + ROW_NUMBER() OVER(Partition BY r.Entity_ID order by r.Telephone_ID) AS Telephone_ID
FROM ref_check_no r
  INNER JOIN (
    SELECT Entity_ID, Max(Telephone_ID) as MaxID
    FROM TelNo 
      GROUP BY Entity_ID) t 
   ON r.Entity_ID=t.Entity_ID
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文