如何對SQLServer的同一行中屬于SHIP_ID的值進行分組

我嘗試了所有方法，但仍然無法將屬于同一行的 SHIP_ID 的值分組。

如何在同一行中擁有屬于同一 SHIP_ID 的所有值？

例如，這是我的原始表：

預期成績：

先感謝您！

uj5u.com熱心網友回復：

我真的希望這個資料集得到修復，但是這里有一些代碼可以產生你正在尋找的輸出。我不知道您的示例是否與您的資料集的列數相同，但在我的示例中，有問題的表被稱為Table_1

我認為這UNIQUE_KEY一直在增加。

-- Start by obtaining all of the SHIP_IDs
WITH Ships AS (
    SELECT * 
    FROM Table_1 
    WHERE SHIP_ID IS NOT NULL

-- Compute the unique key of the next ship id for this set.
), NextShips AS (
    SELECT 
      Ships.UNIQUE_KEY as ShipKey, 
      MIN(NextShip.UNIQUE_KEY) as NextShipKey 
    FROM Ships
    LEFT JOIN Ships as NextShip ON NextShip.UNIQUE_KEY > Ships.UNIQUE_KEY
    GROUP BY Ships.UNIQUE_KEY

-- Due to the calculation above, the last ship id will always have NULL as the next ship id.
), NullFix AS (
    SELECT 
      ShipKey,
      CASE WHEN NextShipKey IS NULL THEN (SELECT TOP 1 UNIQUE_KEY FROM Table_1 ORDER BY UNIQUE_KEY DESC) ELSE NextShipKey END as NextShipKey
    FROM NextShips
)

-- Join the table onto itself, once for each column, being very mindful of which columns come from where.
SELECT 
  DistanceCodes.DISTANCE_CODE
, ShipId.SHIP_ID
, ShipCountries.SHIP_COUNTRY    
FROM NullFix 
JOIN Table_1 AS ShipId ON NullFix.ShipKey = ShipId.UNIQUE_KEY
JOIN Table_1 as DistanceCodes ON DistanceCodes.UNIQUE_KEY >= NullFix.ShipKey AND DistanceCodes.UNIQUE_KEY <= NullFix.NextShipKey AND DistanceCodes.DISTANCE_CODE IS NOT NULL
JOIN Table_1 as ShipCountries ON ShipCountries.UNIQUE_KEY >= NullFix.ShipKey AND ShipCountries.UNIQUE_KEY <= NullFix.NextShipKey AND ShipCountries.SHIP_COUNTRY IS NOT NULL 

uj5u.com熱心網友回復：

這是使用存盤程序的可能解決方案。

create procedure sp_seq
as

create table #shipping (
    [DISTANCE_CODE] [nvarchar](255) NULL,
    [SHIP_ID] [nvarchar](255) NULL,
    [SHIP_COUNTRY] [nvarchar](255) NULL,    
)

declare cur cursor for 

select [DISTANCE_CODE]
      ,[SHIP_ID]
      ,[SHIP_COUNTRY]
  from [dbo].[shipping] order by [Unique_key]

declare @DISTANCE_CODE varchar(255), @SHIP_ID varchar(255), @SHIP_COUNTRY varchar(255)

declare @distance varchar(255), @id varchar(255), @country varchar(255)

open cur
fetch next from cur into @DISTANCE_CODE,@SHIP_ID,@SHIP_COUNTRY

while @@FETCH_STATUS =0 
begin

    if (@SHIP_ID is not null and @SHIP_ID != @id) 
        select @distance=null,@id=null,@country=null

    if (@DISTANCE_CODE is not null) set @distance = @DISTANCE_CODE
    if (@SHIP_ID is not null) set @id = @SHIP_ID
    if (@SHIP_COUNTRY is not null) set @country = @SHIP_COUNTRY 
    
    if (@distance is not null and @id is not null and @country is not null)
        insert into #shipping(DISTANCE_CODE,SHIP_ID,SHIP_COUNTRY) 
        select @distance,@id,@country
            
    fetch next from cur into @DISTANCE_CODE,@SHIP_ID,@SHIP_COUNTRY
end
close cur
deallocate cur

select * from #shipping

go

結果：

exec sp_seq

/*
DISTANCE_CODE   SHIP_ID     SHIP_COUNTRY
1C254           1245_COD    Tunisia
E9663           1100_COD    Oman
J9668           1100_COD    Oman
R9664           1100_COD    Oman
S9669           1100_COD    Oman
T9662           1100_COD    Oman
79663           1100_COD    Oman
E1661           1100_COD    Oman
99Z254          1245_CR     Chile
55X33           1245_CR     Chile
*/

uj5u.com熱心網友回復：

只是想我會分享另一個解決方案。

WITH cte_set AS (
    -- Generate GroupID that increments each time SHIP_ID is populated
    SELECT s.DISTANCE_CODE, s.SHIP_ID, s.SHIP_COUNTRY
        , GroupID = COUNT(s.SHIP_ID) OVER (ORDER BY s.Unique_Key ROWS UNBOUNDED PRECEDING)
    FROM #ship s
), cte_ships AS (
    -- Return table of SHIP_ID SHIP_COUNTRY to GroupID mappings
    SELECT x.GroupID
        , SHIP_ID = MAX(x.SHIP_ID), SHIP_COUNTRY = MAX(x.SHIP_COUNTRY)
    FROM cte_set x
    GROUP BY x.GroupID
), cte_dc AS (
    -- Return table of DISTANCE_CODE to GroupID mappings
    SELECT x.DISTANCE_CODE, x.GroupID
    FROM cte_set x
    WHERE x.DISTANCE_CODE IS NOT NULL
)
SELECT d.DISTANCE_CODE, s.SHIP_ID, s.SHIP_COUNTRY
FROM cte_dc d
    JOIN cte_ships s ON s.GroupID = d.GroupID;

這里的概念是我首先根據是否SHIP_ID填充了唯一的“GroupID” 。每次SHIP_ID填充時，GroupID都會增加。

然后從該資料集中，我生成了兩個新集……一個集包含SHIP_ID, SHIP_COUNTRY, GroupID。第二組包含DISTANCE_CODE, GroupID。然后我加入了這一切。

我還想指出，它也相當有效，至少與我嘗試過的其他方法相比。我針對一個 200 萬條記錄表運行了這個程式，將結果輸出到臨時表需要 19 秒。

樣本資料

IF OBJECT_ID('tempdb..#ship','U') IS NOT NULL DROP TABLE #ship; --SELECT * FROM #ship
CREATE TABLE #ship (
    DISTANCE_CODE varchar(6) NULL,
    SHIP_ID varchar(8) NULL,
    SHIP_COUNTRY varchar(20) NULL,
    Unique_Key int NOT NULL PRIMARY KEY CLUSTERED,
)

INSERT INTO #ship (DISTANCE_CODE, SHIP_ID, SHIP_COUNTRY, Unique_Key)
VALUES (NULL,'1245_COD',NULL,54)
    ,  (NULL,NULL,'Tunisia',58)
    ,  ('1C254',NULL,NULL,60)
    ,  (NULL,'1100_COD',NULL,82)
    ,  (NULL,NULL,'Oman',85)
    ,  ('E9663',NULL,NULL,91)
    ,  ('J9668',NULL,NULL,93)
    ,  ('R9664',NULL,NULL,96)
    ,  ('S9669',NULL,NULL,98)
    ,  ('T9662',NULL,NULL,101)
    ,  ('79663',NULL,NULL,106)
    ,  ('E1661',NULL,NULL,108)
    ,  (NULL,'1245_CR',NULL,110)
    ,  (NULL,NULL,'Chile',115)
    ,  ('99Z254',NULL,NULL,116)
    ,  ('55X33',NULL,NULL,121);

標籤：sql sql-server

上一篇：游標和while回圈在同一程序sqlserver

下一篇：計數ISNULL轉換為varchar