如何使用行組中的值更新基于另一個表的表？

我有兩張桌子：

CREATE TABLE Employee (
   Site       ????         ????,
   WorkTypeId char(2)  NOT NULL,
   Emp_NO     int      NOT NULL,
   "Date"     ????     NOT NULL  
);

CREATE TABLE PTO (
   Site       ????         ????,
   WorkTypeId char(2)      NULL,
   Emp_NO     int      NOT NULL,
   "Date"     ????     NOT NULL  
);

我想更新PTO'sWorkTypeId列中的值：

1. EMP NO在Employee（查找表）中并且PTO應該匹配。
2. WorkTypeId應僅從該月的第一次出現中選擇一個值。

例如，給定此示例輸入資料：

TABLE Employee：

TABLE PTO：

例如：

• 與... Employee_Emp_NO = 1005
  • Emp_NO...表中有 3 行Employee，有 3 個不同的WorkTypeId值，但Date值不同。
  • 所以選擇WorkTypeId最早Date（2022-02-01）的值，即'MB'
  • 所以Emp_NO得到WorkTypeId = 'MB'。
  • And use that single value to fill 1005's WorkTypeId cells in the PTO table.
  • But also match by month.

So the expected output in the PTO table is

uj5u.com熱心網友回復：

從與查詢中的/運算式中使用的列不同的列中獲取值在 SQL 中仍然是一件非常困難的事情，雖然現代版本的 SQL 語言（和 SQL Server）使它更容易，但它們完全盡管查詢在概念上很簡單，但它必然涉及更高級的主題，例如 CTE、派生表（也稱為內部查詢）、自聯接和視窗函式，這對大多數人來說并不明顯且違反直覺。MINMAXGROUP BY

無論如何，在現代 SQL 中，通常有 3 或 4 種不同的方法來完成相同的任務，但有一些陷阱。

前言：

• 由于Site, Date,Year和Month都是 T-SQL 中的關鍵字，我用雙引號將它們轉義，這是符合 ISO/ANSI SQL 標準的保留字轉義方式。

  • SQL Server 默認支持此功能。如果（出于某些不敬虔的原因）您SET QUOTED IDENTIFIER OFF將雙引號更改為方括號：[]

• 我假設Site兩個表中的列只是一個普通的“ol”資料列，如下所示：

  • 它不是PRIMARY KEY成員列。
  • 它不應該用作GROUP BY.
  • 它不應該用在JOIN謂詞中。

• 以下所有方法均假定此資料庫狀態：

CREATE TABLE "Employee" (
    "Site"     int      NOT NULL,
    WorkTypeId char(2)  NOT NULL,
    Emp_NO     int      NOT NULL,
    "Date"     date     NOT NULL  
);

CREATE TABLE "PTO" (
    "Site"     int      NOT NULL,
    WorkTypeId char(2)      NULL,
    Emp_NO     int      NOT NULL,
    "Date"     date     NOT NULL  
);

GO

INSERT INTO "Employee" ( "Site", WorkTypeId, Emp_NO, "Date" )
VALUES
( 5015, 'MB', 1005, '2022-02-01' ),
( 5015, 'MI', 1005, '2022-02-04' ),
( 5015, 'PO', 1005, '2022-02-04' ),
( 5015, 'ME', 2003, '2022-01-01' ),
( 5015, 'TT', 2003, '2022-01-10' );

INSERT INTO "PTO" ( "Site", WorkTypeId, Emp_NO, "Date" )
VALUES
( 5015, NULL, 1005, '2022-02-03' ),
( 5015, NULL, 1005, '2022-02-14' ),
( 5014, NULL, 2003, '2022-01-09' );

• Both approaches define CTEs e and p that extend Employee and PTO respectively to add computed "Year" and "Month" columns, which avoids having to repeatedly use YEAR( "Date" ) AS "Year" in GROUP BY and JOIN expressions.
  • I suggest you add those as computed-columns in your base tables, if you're able, as they'll be useful generally anyway. Don't forget to index them appropriately too.

Approach 1: Composed CTEs with elementary aggregates, then UPDATE:

WITH
-- Step 1: Extend both the `Employee` and `PTO` tables with YEAR and MONTH columns (this simplifies things later on):
e AS (
    SELECT
        Emp_No,
        "Site",
        WorkTypeId,
        "Date",

        YEAR( "Date" ) AS "Year",
        MONTH( "Date" ) AS "Month"
    FROM
        Employee
),
p AS (
    SELECT
        Emp_No,
        "Site",
        WorkTypeId,
        "Date",

        YEAR( "Date" ) AS "Year",
        MONTH( "Date" ) AS "Month"
    FROM
        PTO
),
-- Step 2: Get the MIN( "Date" ) value for each group:
minDatesForEachEmployeeMonthYearGroup AS (
    SELECT
        e.Emp_No,
        e."Year",
        e."Month",

        MIN( "Date" ) AS "FirstDate"
    FROM
        e
    GROUP BY
        e.Emp_No,
        e."Year",
        e."Month"
),
-- Step 3: INNER JOIN back on `e` to get the first WorkTypeId in each group:
firstWorkTypeIdForEachEmployeeMonthYearGroup AS (
    /* WARNING: This query will fail if multiple rows (for the same Emp_NO, Year and Month) have the same "Date" value. This can be papered-over with GROUP BY and MIN, but I don't think that's a good idea at all). */
    SELECT
        e.Emp_No,
        e."Year",
        e."Month",

        e.WorkTypeId AS FirstWorkTypeId
    FROM
        e
        INNER JOIN minDatesForEachEmployeeMonthYearGroup AS q ON
            e.Emp_NO = q.Emp_NO
            AND
            e."Date" = q.FirstDate
)
-- Step 4: Do the UPDATE.
-- *Yes*, you can UPDATE a CTE (provided the CTE is "simple" and has a 1:1 mapping back to source rows on-disk).
UPDATE
    p
SET
    p.WorkTypeId = f.FirstWorkTypeId
FROM
    p
    INNER JOIN firstWorkTypeIdForEachEmployeeMonthYearGroup AS f ON
        p.Emp_No = f.Emp_No
        AND
        p."Year" = f."Year"
        AND
        p."Month" = f."Month"
WHERE
    p.WorkTypeId IS NULL;

Here's a screenshot of SSMS showing the contents of the PTO table from before, and after, the above query runs:

Approach 2: Skip the self-JOIN with FIRST_VALUE:

This approach gives a shorter, slightly simpler query, but requires SQL Server 2012 or later (and that your database is running in compatibility-level 110 or higher).

Surprisingly,

Approach 2b's plan looks like this:

相比之下，@SOS 的計劃要簡單得多……老實說，我不知道為什么，但它確實顯示了如今 SQL Server 的查詢優化器有多好：

uj5u.com熱心網友回復：

2002-03-05 更新

把這個留給后代，但我建議閱讀Dai 的關于解決這個問題的不同方法的優秀文章。

嘗試 CROSS APPLY 以獲取具有匹配月份和年份的第一個 Employee 記錄。

注意：使用 OUTER APPLY 始終回傳所有 PTO 記錄，即使未找到匹配的 WorkTypeId 也是如此。

SELECT p.Site
       , e.WorkTypeId
       , p.Emp_No
       , p.[Date]
FROM  PTO p CROSS APPLY 
        (
          SELECT TOP 1 WorkTypeId
          FROM   Employee e 
          WHERE  e.Emp_No = p.Emp_No
          AND    MONTH(e.[Date]) = MONTH(p.[Date])
          AND    YEAR(e.[Date]) = YEAR(p.[Date])
          ORDER BY [Date] ASC
        )e

結果：

網站 | 作業型別 ID | Emp_No | 日期      
---: | :--------- | -----: | :---------
5015 | MB | 1005 | 2022-02-03
5015 | MB | 1005 | 2022-02-14
5014 | 我 | 2003 | 2022-01-09

db<>在這里擺弄

標籤：sql sql-server join

上一篇：Mysql查詢首先使用IN子句從左到右優先查找

下一篇：簡單的mex檔案崩潰？