MySQL クロステーブルクエリとクロステーブル更新

SQL の基礎知識がある友人は、「クロステーブル クエリ」について聞いたことがあるはずですが、クロステーブル更新とは何でしょうか?

背景

プロジェクトでは、新しい人事データをインポートしました。これらの人の一部の部署名が変更され、連絡先情報も変更されました。このテーブルを次のように呼びます。

t_dept_membersに、システムには人事情報を記録する別のテーブルt_user_infoがあります。 t_dept_membersの変更情報をt_userテーブルに更新する必要があります。この要件は「クロステーブル更新」と呼ばれます。

愚かなSQLは即座に殺される

何も考えずに、次のSQLを書きました

後ろで DBA Xiao Duan が練習しているのを見て、彼に磨いてもらうよう頼もうと思い、彼に送ったところ、こんな感じで返ってきました:

この SQL 文を見たとき、私は唖然としました。どうしてこんな書き方ができるのか? 容赦ない嘲笑に、私は「KO」して地面に倒れてしまいました。君は死を覚悟しなければならない。何が起こっているのか調べなければならない。

MySQL 更新結合

多くの場合、 join別のテーブルに一致する行がある ( INNER JOINの場合) か、または別のテーブルに一致する行がない ( LEFT JOINの場合) テーブルから行をクエリするために使用します。

同様に、MySQL では、UPDATE ステートメントの JOIN 句を使用してテーブル間の更新を実行することもできます。構文は次のとおりです。

アップデートT1、T2、
[内部結合 | 左結合] T1 ON T1.C1 = T2.C1
T1.C2 = T2.C2 に設定し、
  T2.C3 = 式
WHERE条件

上記の構文を詳しく説明しましょう。

まず、UPDATE 句の後に、プライマリ テーブル (T1) と、プライマリ テーブルを結合するテーブル (T2) を指定します。 UPDATE 句の後に少なくとも 1 つのテーブルを指定する必要があることに注意してください。次に、使用する結合の種類 (INNER JOIN または LEFT JOIN) と結合述語を指定します。 JOIN 句は UPDATE 句の後に記述する必要があります (これは誰もが知っています)。次に、更新する T1 または T2 テーブルの列に新しい値を割り当てます。最後に、WHERE 句で条件を指定して、更新する行を制限します。

更新構文に従うと、テーブル間の更新も完了できる別の構文があることがわかります。

アップデート T1、T2
T1.c2 = T2.c2 を設定します。
   T2.c3 = 式
T1.c1 = T2.c1 AND条件

上記の構文では、実際には inner join キーワードが暗黙的に使用されており、これは次の構文とまったく同じです。

アップデート T1、T2
T2 を T1.C1 に内部結合 = T2.C1
T1.C2 = T2.C2 に設定し、
   T2.C3 = 式
WHERE条件

私の個人的な提案としては、 inner joinキーワードを追加することです。これにより、コードがより読みやすくスムーズになります。どう思われますか?

冗談を言いながらこれを見たのですが、これは魂の翻訳だと思います

口先だけは安い、コードを見せろ

更新結合の例

年末なので業績評価、いわゆるKPI（ありますか？）の時期ですね。KPIに応じて給与が調整されると聞きました。テーブルは2つあります

最初のテーブルは「従業員」です

テーブル作成ステートメントは次のとおりです。

従業員テーブルを作成する
（
  employee_id bigint auto_increment comment '従業員ID、主キー'、
  employee_name varchar(50) null コメント '従業員名',
  パフォーマンス int(4) null コメント 'パフォーマンススコア 1、2、3、4、5'、
  給与フロート null コメント '従業員の給与'、
  制約 employees_pk
    主キー (employee_id)
）
コメント '従業員テーブル';

2番目の表は「功績・業績辞書表」です

テーブル作成ステートメントは次のとおりです。

テーブルメリットを作成する
（
  パフォーマンス int(4) null、
  パーセンテージ 浮動小数点数 null
）
コメント 'パフォーマンス辞書テーブル';

まずシミュレーションデータを生成する

-- パフォーマンス辞書データを初期化する INSERT INTO merits(performance, percentage)
値 (1, 0)、
    （2, 0.01）、
    （3, 0.03）、
    （4, 0.05）、
    （５、０．０８）；


-- 従業員テーブルのデータを初期化します INSERT INTO employees(employee_name, performance, salary)
値（「公格」、1、1000）、
    （『小端宗』、3、20000）、
    （「大人」、4、18000）、
    （「司令官」、5、28000）、
    （『オールドシックス』、2、10000）、
    （『ロモン』、3、20000）

給与調整ルール:

当初給与 + （当初給与 × 現在の業績に応じた給与増加率）

給与調整ルールに従って更新ステートメントを記述します。

従業員の更新
  内部結合
  メリット ON employees.performance = merits.performance
SET 給与 = 給与 + 給与 * パーセンテージ;

ゴン・ゲは業績が良くなかったので昇給しませんでした…

横線が3本、縦線が1本、カッコウが1羽、子豚が4匹食べに来る、カッコウカッコウ、あと2匹来る

年末に2人の新しい同僚が入社しましたが、会社の年次業績評価は完了しているため、新入社員の業績はNULLです。

従業員に挿入(従業員名、業績、給与)
VALUES ('冯大', NULL, 8000),
    ('冯二', NULL, 5000);

新入社員がよい仕事をすれば、給与も1.5%上がるはずです。それでもUPDATE INNER JOIN使用すると、条件等式が成立しないため、上記の更新ステートメントを完了することはできません。このような場合にUPDATE LEFT JOINを使用する必要があります。

従業員の更新
  左結合
  メリット ON employees.performance = merits.performance
SET 給与 = 給与 + 給与 * 0.015
Merits.percentage が NULL の場合;

この時点で、新入社員の昇給も完了しました。ゴン・ゲは知識ポイントを十分に理解していなかったため、恥ずかしさを感じながら家に帰って新年を祝いました。