C1的第1列未指定列

Question

我需要帮助，请我已经被困在这3天了，我已经无休止地谷歌，但我似乎无法解决这个问题，请有人帮助我解决这个问题，并解释我做错了什么?

我一直得到No Column是为C1错误指定的，当我试图执行它时，我一直得到

Msg 4112, Level 15, State 1, Procedure XXX, Line 27 [Batch Start Line 7]函数'ROW_NUMBER'必须有一个ORDER BY的OVER子句。

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);

--Test Code--
--Channel 1
With C1 AS (SELECT  @ReoDeviceId,CN.ChannelNumber,C.ChannelID,D.Totaliser1Units TotaliserUnits, C.ChannelName, 'CW', @DTSourceRecId,'B', C.ChannelName,
RN = ROW_NUMBER() OVER(PARTITION BY @DTSourceRecId)
FROM DevicesCW D
JOIN #CName C
ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
JOIN #CNumber CN
ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
LEFT JOIN ReoDeviceMeter RDM
ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = 1
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,'CW',@DTSourceRecId,'B',ChannelName
FROM C1
WHERE RN = 1
Group BY @ReoDeviceId,ChannelNumber,ChannelID,TotaliserUnits,ChannelName,'CW',@DTSourceRecId,'B',ChannelName

-- Test Code---

我正试图走上述路线来解决一个问题，其中吹代码重复每个通道数据库中的数据。如果我有2个通道，它会为CH1和CH2插入2次数据，而不是只为每个通道插入数据

--Channel1
INSERT INTO ReoMeter(ReoDeviceId, MeterNumber, ChannelNumber, ChanUnitOfMeasure, Description, DtSource, DtSourceRecId, MeterType, SourceDescription)
SELECT  @ReoDeviceId,CN.ChannelNumber,C.ChannelID,D.Totaliser1Units, C.ChannelName, 'CW', @DTSourceRecId,'B', C.ChannelName
FROM DevicesCW D
JOIN #CName C
ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
JOIN #CNumber CN
ON D.DataIndex = C.DataIndex AND C.ChannelID = 1
LEFT JOIN ReoDeviceMeter RDM
ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = 1
WHERE D.Id = @DTSourceRecId AND RDM.ReoMeterId IS NULL
--Channel2
INSERT INTO ReoMeter(ReoDeviceId, MeterNumber, ChannelNumber, ChanUnitOfMeasure, Description, DtSource, DtSourceRecId, MeterType, SourceDescription)
SELECT  @ReoDeviceId,CN.ChannelNumber,C.ChannelID,D.Totaliser2Units, C.ChannelName, 'CW', @DTSourceRecId, 'B', C.ChannelName
FROM DevicesCW D
JOIN #CName C
ON D.DataIndex = C.DataIndex AND C.ChannelID = 2
JOIN #CNumber CN
ON D.DataIndex = C.DataIndex AND C.ChannelID = 2
LEFT JOIN ReoDeviceMeter RDM
ON D.DataIndex = RDM.DeviceIndex AND RDM.ChannelNumber = 2
WHERE D.Id = @DTSourceRecId AND RDM.ReoMeterId IS NULL

Answer 1

您需要为C1的所有列提供alias来解决这个问题。

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);
--Test Code--
--Channel 1
WITH c1
AS (SELECT @ReoDeviceId      AS ReoDeviceId,
CN.channelnumber,
C.channelid,
D.totaliser1units TotaliserUnits,
C.channelname,
'CW'              AS CW,
@DTSourceRecId    AS DTSourceRecId,
'B'               AS B,
C.channelname,
RN = Row_number()
OVER(
partition BY @DTSourceRecId)
FROM   devicescw D
JOIN #cname C
ON D.dataindex = C.dataindex
AND C.channelid = 1
JOIN #cnumber CN
ON D.dataindex = C.dataindex
AND C.channelid = 1
LEFT JOIN reodevicemeter RDM
ON D.dataindex = RDM.deviceindex
AND RDM.channelnumber = 1
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,
cw,
dtsourcerecid,
b,
channelname
FROM   c1
WHERE  rn = 1
GROUP  BY reodeviceid,
channelnumber,
channelid,
totaliserunits,
channelname,
cw,
dtsourcerecid,
b,
channelname