Permintaan ini hampir melakukan apa yang saya inginkan

SELECT staging.dbo.ITEM_CODES.ITEM_CODE, MAX(dbo.OC_VDAT_AUX.UDL40) AS SAMPLEDATE,
           CONCAT(RTRIM(dbo.OC_VDATA.UDL1), RTRIM(dbo.OC_VDATA.UDL6)) as LinkID
FROM dbo.OC_VDATA 
    INNER JOIN dbo.OC_VDAT_AUX ON dbo.OC_VDATA.PARTNO = dbo.OC_VDAT_AUX.PARTNOAUX AND dbo.OC_VDATA.DATETIME = dbo.OC_VDAT_AUX.DATETIMEAUX 
    INNER JOIN stagingPLM.dbo.ITEM_CODES ON LEFT(dbo.OC_VDATA.PARTNO, 12) = staging.dbo.ITEM_CODES.SPEC_NO 
    AND LEFT(dbo.OC_VDAT_AUX.PARTNOAUX, 12) = stagingPLM.dbo.ITEM_CODES.SPEC_NO 
    INNER JOIN stagingPLM.dbo.PLANTS ON dbo.OC_VDATA.UDL1 = staging.dbo.PLANTS.PLANT_CODE
WHERE  (CONVERT(DATETIME, dbo.OC_VDAT_AUX.UDL40) > DATEADD(day, - 30, GETDATE()))
GROUP BY CONCAT(RTRIM(dbo.OC_VDATA.UDL1), RTRIM(dbo.OC_VDATA.UDL6)),staging.dbo.ITEM_CODES.ITEM_CODE

Tabel Contoh yang dihasilkan oleh kueri: masukkan deskripsi gambar di sini sql-server-2016

1
Six 19 November 2020, 22:35

1 menjawab

Jawaban Terbaik

Saya akan melakukannya dalam kasus Anda dengan ROW_NUMBER fungsi jendela dan CTE.

Solusinya bisa seperti ini:

WITH FilterCTE AS
(
    SELECT staging.dbo.ITEM_CODES.ITEM_CODE, MAX(dbo.OC_VDAT_AUX.UDL40) AS SAMPLEDATE,
               CONCAT(RTRIM(dbo.OC_VDATA.UDL1), RTRIM(dbo.OC_VDATA.UDL6)) AS LinkID,
               ROW_NUMBER() OVER (PARTITION BY CONCAT(RTRIM(dbo.OC_VDATA.UDL1), RTRIM(dbo.OC_VDATA.UDL6)) ORDER BY MAX(dbo.OC_VDAT_AUX.UDL40)) AS RowNumber
    FROM dbo.OC_VDATA 
        INNER JOIN dbo.OC_VDAT_AUX ON dbo.OC_VDATA.PARTNO = dbo.OC_VDAT_AUX.PARTNOAUX AND dbo.OC_VDATA.DATETIME = dbo.OC_VDAT_AUX.DATETIMEAUX 
        INNER JOIN stagingPLM.dbo.ITEM_CODES ON LEFT(dbo.OC_VDATA.PARTNO, 12) = staging.dbo.ITEM_CODES.SPEC_NO 
        AND LEFT(dbo.OC_VDAT_AUX.PARTNOAUX, 12) = stagingPLM.dbo.ITEM_CODES.SPEC_NO 
        INNER JOIN stagingPLM.dbo.PLANTS ON dbo.OC_VDATA.UDL1 = staging.dbo.PLANTS.PLANT_CODE
    WHERE  (CONVERT(DATETIME, dbo.OC_VDAT_AUX.UDL40) > DATEADD(day, - 30, GETDATE()))
    GROUP BY CONCAT(RTRIM(dbo.OC_VDATA.UDL1), RTRIM(dbo.OC_VDATA.UDL6)),staging.dbo.ITEM_CODES.ITEM_CODE
)
SELECT *
FROM FilterCTE
WHERE RowNumber = 1
0
Emin Mesic 20 November 2020, 06:00