Início / dba / Perguntas / 200352
Accepted
Rahul Hendawe
Asked: 2018-03-16 05:06:49 +0800 CST

Como ignorar linhas duplicadas na instrução CTE Select?

  • 772

Estou tentando ignorar linhas duplicadas de um CTE, mas não consigo fazer isso, parece que um CTE não permite usar ROWNUM()variável para WHEREcláusula, pois está mostrando o erro " Nome de coluna inválido 'numrows' " ao tentar fazê-lo .

Como ignoro as linhas duplicadas ao usar SELECTem um CTE?

Consulta SQL:

DECLARE @BatchID uniqueidentifier = NEWID();
DECLARE @ClusterID SMALLINT = 1;
DECLARE @BatchSize integer = 20000;
DECLARE @myTableVariable TABLE(
    EventID BIGINT,
    HotelID int,
    BatchStatus varchar(50),
    BatchID uniqueidentifier);

WITH PendingExtResSvcEventsData_Batch AS (
    SELECT TOP (@BatchSize) t.EventID, t.HotelID, t.BatchStatus, t.BatchID,
        ROW_NUMBER() OVER (PARTITION BY t.EventID ORDER BY t.EventID) numrows 
    FROM ExtResSvcPendingMsg t WITH (NOLOCK)
    WHERE t.ClusterID = @ClusterID
      AND numrows = 1
      -- Exclude ExtResSvcEventID and HotelID,
      -- which are partly included in in-progress batch.
      AND NOT EXISTS (
          select 1 from ExtResSvcPendingMsg t2
          where t2.BatchStatus = 'Batched'
            and t2.EventID = t.EventID and t2.HotelID = t.HotelID
          )
    )
UPDATE PendingExtResSvcEventsData_Batch
   SET BatchStatus='Batched',
       BatchID = @BatchID
OUTPUT INSERTED.* INTO @myTableVariable --WHERE numrows = 1

SELECT e.ExtResSvcEventID, e.HotelID, e.ID1, e.ID2, e.ExtResSvcEventType, e.HostID,
       e.StatusCode, e.ChannelID, e.RequestAtTime, e.ProcessTime, e.DateBegin, e.DateEnd,
       e.StatusMsg, em.MsgBodyOut, em.MsgBodyIn, e.ChannelResID 
FROM  ExtResSvcEvent e WITH (NOLOCK)
INNER JOIN @myTableVariable t ON e.ExtResSvcEventID = t.EventID
INNER JOIN ExtResSvcEventXML em WITH (NOLOCK) ON t.EventID = em.ExtResSvcEventID
ORDER BY e.ExtResSvcEventID
sql-server t-sql

3 respostas

  1. Não use INSERTED.*aqui:

    OUTPUT INSERTED.* INTO @myTableVariable

    Em vez disso, liste as colunas explicitamente:

    OUTPUT INSERTED.EventID,
       INSERTED.HotelID,
       INSERTED.BatchStatus,
       INSERTED.BatchID
INTO @myTableVariable

    O *significa todas as colunas do conjunto de dados de destino. O conjunto de dados de destino no seu caso é um CTE que, além das colunas normais, retorna uma coluna calculada. A INSERTED.*sintaxe também inclui a coluna calculada, mas a referência a uma coluna calculada não é permitida nesse contexto. Listar todas as colunas necessárias resolve o problema explicitamente.

    E você pode usar o numrowsfiltro nesse UPDATE (mas não dentro do CTE). A numrowsreferência será válida em todos os lugares, exceto na cláusula OUTPUT e no lado esquerdo de uma atribuição na cláusula SET.

    Aqui está uma demo , que descaradamente empresta a configuração de ypercubeᵀᴹ, bem como sua declaração UPDATE.

    • 5
  2. Best Answer

    Eu esperaria que isso funcionasse, mas sem definições de tabela, não podemos ter certeza:

    WITH 
PendingExtResSvcEventsData_Batch 
   (...),                           -- your CTE here
upd AS
  ( SELECT EventID, HotelID, BatchStatus, BatchID 
    FROM PendingExtResSvcEventsData_Batch 
    WHERE numrows = 1
  )
UPDATE upd
SET BatchStatus = 'Batched',  
    BatchID = @BatchID
OUTPUT INSERTED.* INTO @myTableVariable ;

    Um teste simplificado em: dbfiddle.uk

    No entanto, há mais problemas com a consulta:

    • Qual é a razão por trás do uso de NOLOCKdica? Sugiro que você o remova, a menos que tenha uma razão para isso - e tenha lido sobre as consequências e aceite o comportamento.
    • Você tem SELECT TOP (@BatchSize)sem um específico ORDER BY. Isso significa que linhas arbitrárias serão selecionadas para serem retornadas por essa subconsulta.
    • Combinado com o WHERE numrows = 1que você deseja aplicar na próxima etapa, isso significa que a atualização pode afetar menos linhas do que o especificado @Batchsize, possivelmente até mesmo apenas uma.
    • 4

  3. Não use nolockaqui

    PARTITION BY t.EventID ORDER BY t.EventIDserá uma ordem arbitrária. Tem certeza que é isso que você quer.

    topsem e ordenar por é arbitrary. Você está agrupando parcialmente, mas exclui lotes parciais. Isso me parece errado.

    Você pode apenas usar numrowsna seleção final.

    DECLARE @BatchID uniqueidentifier = NEWID();
DECLARE @ClusterID SMALLINT = 1;
DECLARE @BatchSize integer = 20000;
DECLARE @myTableVariable TABLE(EventID BIGINT,HotelID int, BatchStatus varchar(50),BatchID uniqueidentifier);

WITH PendingExtResSvcEventsData_Batch AS
(  SELECT TOP (@BatchSize) t.EventID, t.HotelID, t.BatchStatus, t.BatchID
       ,  ROW_NUMBER() OVER (PARTITION BY t.EventID ORDER BY t.EventID) numrows 
     FROM ExtResSvcPendingMsg t  
    WHERE t.ClusterID = @ClusterID 
      AND numrows = 1 
      AND NOT EXISTS ( select 1 
                         from ExtResSvcPendingMsg t2 
                        where t2.BatchStatus = 'Batched' 
                          and t2.EventID = t.EventID 
                          and t2.HotelID = t.HotelID       
                      )
)        
UPDATE PendingExtResSvcEventsData_Batch 
   SET BatchStatus = 'Batched',  
       BatchID = @BatchID
OUTPUT INSERTED.* INTO @myTableVariable --WHERE numrows = 1

SELECT e.ExtResSvcEventID, e.HotelID, e.ID1, e.ID2, e.ExtResSvcEventType, e.HostID, e.StatusCode, e.ChannelID, e.RequestAtTime, e.ProcessTime, e.DateBegin, e.DateEnd, e.StatusMsg, em.MsgBodyOut, em.MsgBodyIn, e.ChannelResID 
  FROM ExtResSvcEvent e WITH (NOLOCK) 
 INNER JOIN @myTableVariable t 
    ON e.ExtResSvcEventID = t.EventID    
   AND t.numrows = 1 
 INNER JOIN ExtResSvcEventXML em with (nolock) 
    on t.EventID = em.ExtResSvcEventID            
 ORDER BY e.ExtResSvcEventID

    Por que produzir todas as linhas. Parece que você está usando apenas t.EventID.

    • -1

relate perguntas