sql - 没有为 C1 的第 1 列指定任何列
问题描述
我需要帮助,我已经坚持了 3 天了,我一直在谷歌上搜索,但我似乎无法解决这个问题,有人可以帮我解决这个问题并解释我做错了什么吗?
我不断收到 No Column is specified for C1 错误,当我尝试执行它时,我不断收到
消息 4112,级别 15,状态 1,过程 XXX,第 27 行 [批处理开始第 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
解决方案
您需要为 C1 的所有列提供 Aleas 以解决此问题。
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
推荐阅读
- r - 为所有类别生成图表
- php - 如何编写 preg_replace_callback() 模式?
- pytorch - Pytorch 顺序数据加载器
- firebase - 如何在同一列表视图中获取 Flutter Firestore 中具有唯一名称的文档的所有数据
- python - 保存 Keras ML 模型时出错
- python - Python 嵌套循环解释
- php - PHP Cookie 未存储在用户浏览器、php 7.4、Google App Engine 上
- python - 如何清除 kivy 中的小部件?
- react-spring - 如何在 codepen 中设置 React-spring?如何
- react-native - 语义问题@RCTxxBridge