SQL查询，用于在一个表中搜索一条记录，并将其替换为另一个表的多条记录

Question

我有两个表-包和子包为：

CREATE TABLE Package (
Sequence int PRIMARY KEY,
Package int,
Mnemonic char(3),
MnemonicValue int
);
CREATE TABLE SubPackage (
Sequence int PRIMARY KEY,
SubPackage int,
Mnemonic char(3),
MnemonicValue int
);
INSERT INTO Package (Sequence, Package, Mnemonic, MnemonicValue)
VALUES (1, 111, 'XXX', 0), (2, 111, 'SUB', 153), (3, 111, 'DDD', 30), (4, 111, 'YYY', 20), (5, 111, 'ZZZ', 1000);
INSERT INTO SubPackage (Sequence, SubPackage, Mnemonic, MnemonicValue)
VALUES (1, 153, 'AAA', 20), (2, 153, 'BBB', 1000), (3, 153, 'CCC', 30);

要求是在包表中搜索助记符"SUB"，并将SUB记录替换为映射助记符值(153(的"Subpackage"表中的记录，并按[Result][2]表中所示对序列值重新排序。

我已经尝试使用更新语句作为：

UPDATE package
SET    Mnemonic = subpackage.Mnemonic
FROM   package
INNER JOIN subpackage
ON package.MnemonicValue = subpackage.SubPackage

但这只会将包表中的SUB记录替换为子包表中仅有的AAA记录。我希望用子包表中的所有记录替换SUB记录，并在一个新表中重新排列它的顺序，如Result表中所示。

期望结果：

Sequence | Particular | Mnemonic | MnemonicValue
1 |         111|       XXX|             0        
2 |         111|       AAA|            20
3 |         111|       BBB|          1000
4 |         111|       CCC|            30
5 |         111|       DDD|            30
6 |         111|       YYY|            20
7 |         111|       ZZZ|          1000

Answer 1

您可以使用left join来获得您想要的结果：

select
p.sequence,
p.package,
coalesce(s.mnemonic, p.mnemonic) mnemonic,
coalesce(s.mnemonic_value, p.mnemonic_value) mnemonic_value
from package p
left join sub_package s 
on  p.mnemonic = 'SUB'
and p.mnemonic_value = s.sub_package

"替换"新行有点复杂。插入新行，然后删除原来的行可能会更简单(您可以在事务中这样做(：

start transaction;
insert into package(sequence, package, mnemonic, mnemonic_value)
select
p.sequence,
p.package,
coalesce(s.mnemonic, p.mnemonic),
coalesce(s.mnemonic_value, p.mnemonic_value)
from package p
left join sub_package s 
on  p.mnemonic = 'SUB'
and p.mnemonic_value = s.sub_package;
delete from package where mnemonic = 'SUB';
commit;

Answer 2

我不认为您可以更新Package表来获得您想要的确切结果，即，在您的示例中，问题是您对Sequence列进行了重新排序。

无论如何，这里是SQL脚本(基于GMB的回答(，它将生成您想要得到的结果：

select
ROW_NUMBER() OVER (ORDER BY p.Sequence) as Sequence,
p.Package,
coalesce(s.Mnemonic, p.Mnemonic) Mnemonic,
coalesce(s.MnemonicValue, p.MnemonicValue) MnemonicValue
from Package p
left join SubPackage s 
on p.MnemonicValue = s.SubPackage
order by p.Sequence

但是，AFAIK它只适用于MySQL 8

这里有一个指向SQLFiddle的链接，但要注意它是针对Postgres的(因为SQLFiddl不支持MySQL 8(。

如果你使用的是MySQL的早期版本，你可以尝试模拟row_number函数