SQL报错No column was specified for C1、ROW_NUMBER缺ORDER BY及插入重复数据求助

• No Column was specified for C1 错误：你在定义CTE C1的SELECT语句中，@ReoDeviceId、常量'CW'、@DTSourceRecId、常量'B'这几个字段没有指定别名，CTE要求所有列必须有明确的列名，因此触发该报错。
• ROW_NUMBER必须带OVER ORDER BY 错误：SQL Server的ROW_NUMBER()窗口函数要求OVER子句中必须指定ORDER BY排序规则，你仅配置了PARTITION BY @DTSourceRecId，缺少排序逻辑，因此触发4112报错。

除了解决上述两个报错，还对原有代码做了优化：

1. 合并了Channel1、Channel2的重复逻辑，无需分开写两段INSERT语句，后续新增通道也不需要新增代码
2. 去掉了多余的GROUP BY语句，通过ROW_NUMBER分区取第一行直接解决重复插入问题

SELECT TOP 1 @DataIndex = DataIndex,
             @ChannelDescription = Description,
             @UserConfigurationData = UserConfigurationData
FROM DevicesCW WHERE Id = @DTSourceRecId

SELECT @DataIndex DataIndex,Column_Id ChannelID, value ChannelName INTO #CName FROM dbo.[fn_split_string_to_column] (@ChannelDescription,@delimiter)
SELECT @DataIndex DataIndex,Column_Id ChannelID, value ChannelNumber INTO #CNumber FROM dbo.[fn_split_string_to_column] (@UserConfigurationData,@delimiter);

WITH C1 AS (
    SELECT  
        @ReoDeviceId AS ReoDeviceId,
        CN.ChannelNumber,
        C.ChannelID,
        -- 按通道号匹配对应的Totaliser字段
        CASE C.ChannelID WHEN 1 THEN D.Totaliser1Units WHEN 2 THEN D.Totaliser2Units END AS TotaliserUnits,
        C.ChannelName,
        'CW' AS DtSource,
        @DTSourceRecId AS DtSourceRecId,
        'B' AS MeterType,
        C.ChannelName AS SourceDescription,
        -- 按设备ID+通道号分区，每个分区只取第一行解决重复插入
        RN = ROW_NUMBER() OVER(PARTITION BY @DTSourceRecId, C.ChannelID ORDER BY (SELECT 1))
    FROM DevicesCW D
    JOIN #CName C ON D.DataIndex = C.DataIndex
    JOIN #CNumber CN ON D.DataIndex = CN.DataIndex AND C.ChannelID = CN.ChannelID
    LEFT JOIN ReoDeviceMeter RDM ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = C.ChannelID
    WHERE D.Id = @DTSourceRecId AND RDM.ReoMeterId IS NULL
)
INSERT INTO ReoMeter(ReoDeviceId, MeterNumber, ChannelNumber, ChanUnitOfMeasure, Description, DtSource, DtSourceRecId, MeterType, SourceDescription)
SELECT ReoDeviceId,ChannelNumber,ChannelID,TotaliserUnits,ChannelName,DtSource,DtSourceRecId,MeterType,SourceDescription
FROM C1
WHERE RN = 1

内容的提问来源于stack exchange，提问作者Andrew Liebenberg