主页 / dba / 问题 / 200352
Accepted
Rahul Hendawe
Asked: 2018-03-16 05:06:49 +0800 CST

如何忽略 CTE Select 语句中的重复行？

  • 772

我试图忽略 CTE 中的重复行，但我无法做到这一点，似乎 CTE 不允许使用ROWNUM()变量来WHERE子句，因为它在尝试这样做时显示“无效的列名‘numrows’ ”错误.

SELECT在 a 中使用时如何忽略重复行CTE？

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 个回答

  1. 不要INSERTED.*在这里使用：

    OUTPUT INSERTED.* INTO @myTableVariable

    相反，明确列出列：

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

    这*意味着目标数据集的所有列。您案例中的目标数据集是一个 CTE，除了普通列外，它还返回一个计算列。语法也包括计算列，INSERTED.*但在该上下文中不允许引用计算列。列出所有必需的列可以明确解决该问题。

    您可以在该更新上使用numrows过滤器（但不能在 CTE 内部）。numrows除了 OUTPUT 子句和 SET 子句中赋值的左侧之外，该引用在任何地方都有效。

    这是一个演示，无耻地借用了 ypercubeᵀᴹ 的设置以及他的 UPDATE 语句。

    • 5
  2. Best Answer

    我希望它能工作但没有表定义，我们不能确定：

    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 ;

    一个简化的测试：dbfiddle.uk

    但是查询还有更多问题：

    • NOLOCK使用提示背后的原因是什么？我建议您删除它，除非您有理由这么做——并且您已经阅读了后果并接受了这种行为。
    • 你SELECT TOP (@BatchSize)没有特定的ORDER BY. 这意味着将选择任意行由该子查询返回。
    • 结合WHERE numrows = 1您要在下一步中应用的 ，这意味着更新可能影响的行少于指定的行@Batchsize，甚至可能只影响一个。
    • 4

  3. 不要nolock在这里 使用

    PARTITION BY t.EventID ORDER BY t.EventID将是任意顺序。你确定那是你想要的吗？

    top没有和订购是arbitrary。您正在部分批处理，但随后排除了部分批处理。这对我来说似乎是错误的。

    您可以只numrows在最终选择中使用。

    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

    为什么输出所有行。看起来您只使用了 t.EventID。

    • -1

相关问题