如何插入序列号为 no 的多键表

发布于 2024-11-25 02:08:54 字数 2350 浏览 3 评论 0原文

我想为每个唯一的 ID_COST_CENTRE、NM_ROSTER 插入一个新任务到下表中，并将 No_Order_Number 的值设置为 MAX(No_Order_Number)+1。

我可以设置NM_TASK，TY_TASK & ID_TASK_TYPE 值

INSERT INTO [Microster].[dbo].[MIC_CC_Task_Edit]
   ([ID_COST_CENTRE],[NM_ROSTER],[NM_TASK],[No_Order_Number],[TY_TASK],[ID_TASK_TYPE])
   VALUES
   (<ID_COST_CENTRE, char(15),>
   ,<NM_ROSTER, char(25),>
   ,<NM_TASK, char(15),>
   ,<No_Order_Number, smallint,>
   ,<TY_TASK, smallint,>
   ,<ID_TASK_TYPE, char(15),>)

例如，我想将任务 AL 添加到以下并获取下面的结果。

0006    0006 ADMIN              CLERICAL ASSIST 1   1   Worked Task 
0006    0006 ADMIN          COORD       2   1   Worked Task 
0006    0006 ADMIN          MRT             3   1   Worked Task 
0006    0006 ADMIN          ORIENT      4   1   Worked Task 
0006    0006 MRT'S          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 MRT'S          COORD       2   1   Worked Task 
0006    0006 MRT'S          MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      CLERICAL ASSIST 1   1   Worked Task 
0006    0006 SONOGRAPHER'S      COORD       2   1   Worked Task 
0006    0006 SONOGRAPHER'S      MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      ORIENT      4   1   Worked Task 
0006    0006 SONOGRAPHER'S      SONOGRAPHER     5   1   Worked Task

插入后

0006    0006 ADMIN          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 ADMIN          COORD       2   1   Worked Task 
0006    0006 ADMIN          MRT             3   1   Worked Task 
0006    0006 ADMIN          ORIENT      4   1   Worked Task 
0006    0006 ADMIN          AL          5   3   AL 
0006    0006 MRT'S          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 MRT'S          COORD       2   1   Worked Task 
0006    0006 MRT'S          MRT             3   1   Worked Task 
0006    0006 MRT'S          AL          4   3   AL 
0006    0006 SONOGRAPHER'S      CLERICAL ASSIST 1   1   Worked Task 
0006    0006 SONOGRAPHER'S      COORD       2   1   Worked Task 
0006    0006 SONOGRAPHER'S      MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      ORIENT      4   1   Worked Task 
0006    0006 SONOGRAPHER'S      SONOGRAPHER     5   1   Worked Task 
0006    0006 SONOGRAPHER'S      AL      6   3   AL

原文

I want to insert a new Task into the following table for each unique ID_COST_CENTRE, NM_ROSTER and set the value of No_Order_Number to the MAX(No_Order_Number)+1.

I can set the NM_TASK, TY_TASK & ID_TASK_TYPE values

INSERT INTO [Microster].[dbo].[MIC_CC_Task_Edit]
   ([ID_COST_CENTRE],[NM_ROSTER],[NM_TASK],[No_Order_Number],[TY_TASK],[ID_TASK_TYPE])
   VALUES
   (<ID_COST_CENTRE, char(15),>
   ,<NM_ROSTER, char(25),>
   ,<NM_TASK, char(15),>
   ,<No_Order_Number, smallint,>
   ,<TY_TASK, smallint,>
   ,<ID_TASK_TYPE, char(15),>)

For example I want to add the task AL to the following ang get the result below.

0006    0006 ADMIN              CLERICAL ASSIST 1   1   Worked Task 
0006    0006 ADMIN          COORD       2   1   Worked Task 
0006    0006 ADMIN          MRT             3   1   Worked Task 
0006    0006 ADMIN          ORIENT      4   1   Worked Task 
0006    0006 MRT'S          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 MRT'S          COORD       2   1   Worked Task 
0006    0006 MRT'S          MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      CLERICAL ASSIST 1   1   Worked Task 
0006    0006 SONOGRAPHER'S      COORD       2   1   Worked Task 
0006    0006 SONOGRAPHER'S      MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      ORIENT      4   1   Worked Task 
0006    0006 SONOGRAPHER'S      SONOGRAPHER     5   1   Worked Task

after insert

0006    0006 ADMIN          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 ADMIN          COORD       2   1   Worked Task 
0006    0006 ADMIN          MRT             3   1   Worked Task 
0006    0006 ADMIN          ORIENT      4   1   Worked Task 
0006    0006 ADMIN          AL          5   3   AL 
0006    0006 MRT'S          CLERICAL ASSIST 1   1   Worked Task 
0006    0006 MRT'S          COORD       2   1   Worked Task 
0006    0006 MRT'S          MRT             3   1   Worked Task 
0006    0006 MRT'S          AL          4   3   AL 
0006    0006 SONOGRAPHER'S      CLERICAL ASSIST 1   1   Worked Task 
0006    0006 SONOGRAPHER'S      COORD       2   1   Worked Task 
0006    0006 SONOGRAPHER'S      MRT             3   1   Worked Task 
0006    0006 SONOGRAPHER'S      ORIENT      4   1   Worked Task 
0006    0006 SONOGRAPHER'S      SONOGRAPHER     5   1   Worked Task 
0006    0006 SONOGRAPHER'S      AL      6   3   AL

分享到QQ

分享到微博

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

发布评论

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

鹿童谣 2024-12-02 02:08:54

首先尝试不使用 INSERT INTO：

INSERT INTO [Microster].[dbo].[MIC_CC_Task_Edit]
   ( [ID_COST_CENTRE]
   , [NM_ROSTER]
   , [NM_TASK]
   , [No_Order_Number]
   , [TY_TASK]
   , [ID_TASK_TYPE]
   )
  SELECT 
       ID_COST_CENTRE
     , NM_ROSTER
     , 'AL'
     , MAX(No_Order_Number) + 1
     , 3
     , 'AL'
  FROM
      [Microster].[dbo].[MIC_CC_Task_Edit]
  GROUP BY
       ID_COST_CENTRE
     , NM_ROSTER

Try it first without the INSERT INTO:

INSERT INTO [Microster].[dbo].[MIC_CC_Task_Edit]
   ( [ID_COST_CENTRE]
   , [NM_ROSTER]
   , [NM_TASK]
   , [No_Order_Number]
   , [TY_TASK]
   , [ID_TASK_TYPE]
   )
  SELECT 
       ID_COST_CENTRE
     , NM_ROSTER
     , 'AL'
     , MAX(No_Order_Number) + 1
     , 3
     , 'AL'
  FROM
      [Microster].[dbo].[MIC_CC_Task_Edit]
  GROUP BY
       ID_COST_CENTRE
     , NM_ROSTER

回复收藏 0 原文

~没有更多了~