0

我的帖子

个人中心

设置

  发新话题
DECLARE
@i INT = 0 DECLARE
  @m INT = 30 DECLARE
   @tablename VARCHAR (MAX) = '' DECLARE
    @SQL VARCHAR (MAX) = ''
   WHILE (@i >= 0)
   BEGIN
   SET @tablename = 'ibs_history_hvac_' + CONVERT (
    CHAR (10),
    LEFT (
     CONVERT (
      CHAR (10),
      getdate() -@m *@i,
      112
     ),
     6
    )
   )
   SET @SQL = 'SELECT
SUM (
  CASE
  WHEN ibs_item_parameter.code = ''Channel5.8081.BinaryInput_121.PresentValue''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_124.PresentValue''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_127.PresentValue'' THEN
   CONVERT (
    FLOAT,
    ' +@tablename + '.
   VALUE
   )
  ELSE
   0
  END
) AS 机组投入,
SUM (
  CASE
  WHEN ibs_item.code = ''SY_LDS_G_TEMP1'' THEN
   CONVERT (
    FLOAT,
    ' +@tablename + '.
   VALUE
   )
  ELSE
   0
  END
) AS 冷冻水温,
SUBSTRING (
  CONVERT (
   CHAR (23),
    ' +@tablename + '.datetime,
   120
  ),
  1,
  16
) AS 时间
FROM
    ' +@tablename + '
LEFT JOIN ibs_item_parameter ON ' +@tablename + '.code = ibs_item_parameter.code
LEFT JOIN ibs_item ON ibs_item.id = ibs_item_parameter.item_id
WHERE
(
  ibs_item.code = ''SY_LDS_G_TEMP1''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_121.PresentValue''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_124.PresentValue''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_127.PresentValue''
)
GROUP BY
SUBSTRING (
  CONVERT (
   CHAR (23),
       ' +@tablename + '.datetime,
   120
  ),
  1,
  16
)
HAVING
SUM (
  CASE
  WHEN ibs_item_parameter.code = ''Channel5.8081.BinaryInput_121.PresentValue''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_124.PresentValue''
  OR ibs_item_parameter.code = ''Channel5.8081.BinaryInput_127.PresentValue'' THEN
   CONVERT (
    FLOAT,
        ' +@tablename + '.
   VALUE
   )
  ELSE
   0
END
) > 0 UNION ALL
'
   SET @i =@i - 1
   END
   SET @SQL = SUBSTRING (
    @SQL,
    1,
    len(@SQL) - LEN('
    UNION ALL
     ')
   ) EXEC (@SQL) PRINT @SQL




本帖最后由 cneleee 于 2020-11-20 04:00 编辑
[Err] 42000 - [SQL Server]“E”附近有语法错误。
似乎在倒数12行的END这里出错了!



已经搞定,是最后的len长度截取错误了



‹‹ 上一贴:SqlServer数据库长时间连接,客户端特别卡,是什么问题? ...   |   下一贴:excel2019连接SQL server2000进行数据查询问题 ... ››
  发新话题
快速回复主题
关于我们 | 诚聘英才 | 联系我们 | 网站大事 | 友情链接 |意见反馈 | 网站地图
Copyright©2005-2020 51CTO.COM
本论坛言论纯属发布者个人意见,不代表51CTO网站立场!如有疑义,请与管理员联系:bbs@51cto.com