如何在表（Oracle和MySQL）中添加'sequence'列以進行PK

我有一個關于在我擁有的表中添加序列列的問題。

桌子看起來像這樣。

-SQL(MySQL) :

SELECT A.CI_NUM, 
       A.DL_DT, 
       A.DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
      UNION ALL
      SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
      UNION ALL 
      SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
      UNION ALL 
      SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;

-結果 ：

在這個表中，我想添加一個序列列來為這個表做一個PK。

我想要的表看起來像這樣。

我想使用 ALTER TABLE 添加 SQ 列，并希望將其應用于 Oracle 表和 MySQL 表。

如果你回答這個問題，我真的很感激。

uj5u.com熱心網友回復：

對于 Oracle 12c 及更高版本，您可以使用generated ... as identity來創建自動增量列。然后將其設為主鍵，因為如果您在單個操作中執行此操作，Oracle 會拋出錯誤。

create table t(val int)

insert into t values(0)

alter table t add (
  id int generated by default as identity
)

alter table t add (
  constraint t_pk primary key (id)
)

select *
from t

價值 | ID
--: | -：
  0 | 1

db<>在這里擺弄

對于 MySQL，它可以一步完成，因為它強制您將其宣告為 pk：

create table t(val int)

insert into t values(0)

alter table t add (
  id int auto_increment primary key
)

select *
from t

價值 | ID
--: | -：
  0 | 1

db<>在這里擺弄

uj5u.com熱心網友回復：

嗨，您可以使用視窗函式獲取 SQ 值，請參閱下面的 sql：

SELECT A.CI_NUM, 
   A.DL_DT,
   row_number() over (partition by A.CI_NUM,A.DL_DT) AS SQ,
   A.DL_PRC
FROM (SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 700 AS DL_PRC
  UNION ALL
  SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 500 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210701' AS DL_DT, 600 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 600 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 350 AS DL_PRC
  UNION ALL 
  SELECT '1000000001' AS CI_NUM, '20210805' AS DL_DT, 400 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 300 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 350 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210510' AS DL_DT, 200 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 700 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 650 AS DL_PRC
  UNION ALL 
  SELECT '2000000001' AS CI_NUM, '20210603' AS DL_DT, 400 AS DL_PRC
) A;

如果您的資料庫中有該表，您首先需要添加具有一些默認值的 SQ 列，然后使用上面的 sql 并撰寫一個用于更新原始表的 cte，然后您可以像這樣在表上添加一個復合 PK ：

ALTER TABLE MyTble
   ADD CONSTRAINT PK_MyTable PRIMARY KEY(CI_NUM,DL_DT,SQ);

繼續前進，您還需要觸發器或以某種方式獲取每個組（CI_NUM，DL_DT）中的下一個值，并將其用作插入陳述句中 SQ 的值。

希望我給你一個想法如何解決你的問題。

uj5u.com熱心網友回復：

這可能是你正在尋找的：

SELECT CI_NUM, DL_DT,
       ROW_NUMBER() OVER (PARTITION BY CI_NUM, DL_DT) AS SQ, /*generate SQ*/
       DL_PRC
  FROM ( 
      ...your subquery..
   ) A;

如果您使用的是支持ROW_NUMBER()功能的版本，那么此解決方案就足夠了。但如果您使用的是舊版本，請嘗試以下操作：

SELECT CI_NUM, DL_DT,
       CASE WHEN @cn = CI_NUM AND @dd = DL_DT THEN @rownum := @rownum 1
            ELSE @rownum := 1 END AS SQ,
       DL_PRC,
       @cn := CI_NUM,
       @dd := DL_DT
  FROM (SELECT @cn := NULL, @dd:= NULL, @rownum := 1) v
  CROSS JOIN ( 
      ...your subquery..
   ) A
;

更長的查詢，有點難以理解，但使用ROW_NUMBER().

演示小提琴

標籤：mysql sql 甲骨文

上一篇：OracleApex經典報告PL/SQL函式體回傳SQL查詢

下一篇：為什么我無法在oraclecloud的AlwaysFree計劃中創建第二個amd實體？