SQL拼接动态创建表A关联数据库表B查询（数据分区储存）

最新推荐文章于 2024-06-21 15:34:27 发布

runningbar

最新推荐文章于 2024-06-21 15:34:27 发布

阅读量211

点赞数

文章标签：数据库 sql servlet

本文链接：https://blog.csdn.net/u013597888/article/details/126301111

版权

/****** Object: StoredProcedure [dbo].[DataCsvs_QueryDoMethod] Script Date: 2022/8/12 0:16:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Team QIUBB>
-- Create date: <2022.08.11>
-- Description: 按月查询动态表数据
-- =============================================
ALTER PROCEDURE [dbo].[DataCsvs_QueryDoMethod]
@I_Sender NVARCHAR(200)='', --客户端执行按钮
@I_ReturnMessage NVARCHAR(MAX)='' OUTPUT, --返回的信息,支持多语言
@I_ExceptionFieldName NVARCHAR(100)='' OUTPUT, --向客户端报告引起冲突的字段
@I_LanguageId CHAR(1)='1', --客户端传入的语言ID
@I_PlugInCommand VARCHAR(5)='', --插件命令
@I_OrBitUserId CHAR(12)='', --用户ID
@I_OrBitUserName NVARCHAR(100)='', --用户名
@I_ResourceId CHAR(12)='', --资源ID(如果资源不在资源清单中，那么它将是空的)
@I_ResourceName nvarchar(100)='', --资源名
@I_PKid char(12) ='', --主键
@I_ParentPKId char(12)='', --父级主键
@I_Parameter nvarchar(100)='', --插件参数
--以上变量为系统服务固定接口参数，必须在每一个DoEvent过程中实现.
@HostCoordinate NVARCHAR(50)='',
@HostName NVARCHAR(50)='',
@StartDate datetime=null,
@EndDate datetime=NULL,
@Mouth NVARCHAR(50)='',
@Year NVARCHAR(50)='',
@HostNumber NVARCHAR(50)=''

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @Sql NVARCHAR(MAX)
   DECLARE @tablename NVARCHAR(MAX)
-- DECLARE @firstday DATETIME
   --DECLARE @lastday DATETIME
   --SET @firstday=CONVERT(VARCHAR(10),DATEADD(DAY,-DATEPART(DAY,GETDATE())+1,GETDATE()),23) +' 00:00:00' --当月第一天
   --SET @lastday=CONVERT(VARCHAR(10),DATEADD(DAY,-1,DATEADD(MONTH,1,GETDATE()-DAY(GETDATE())+1)),23)+' 23:59:59' --当月最后一天

   -- 检查查询条件
   IF @HostName = '' AND @HostCoordinate = '' AND @Mouth IS NULL
   BEGIN
   SET @I_ReturnMessage = 'ServerMessage:请选择查询条件：设备品牌/设备产线/月份！'

   RETURN -1
   end
   --产线不能为空
   IF ISNULL(@HostCoordinate,'')=''
   BEGIN
   SET @I_ReturnMessage = 'ServerMessage:请选择设备产线!'

   RETURN -1
   end
   --日期不能为空
   IF ISNULL(@Mouth,'')=''

   BEGIN
   SET @I_ReturnMessage = 'ServerMessage:请选择需要查询几月的数据!'

   RETURN -1
   end

--查询1月数据
   IF @Mouth='01'
   BEGIN
       SET @Mouth='01'
       SET @Year=cast(datepart(year,GETDATE()) as nvarchar(50))
       SET @tablename='DataCsv'+@Year+@Mouth
       --SET @Sql='SELECT * FROM '+@tablename+' ORDER BY DetectionTime ASC'
       --SET @Sql='SELECT * FROM '+@tablename+' WHERE DetectionTime BETWEEN '''+CONVERT(VARCHAR(30),@firstday,21)+''' AND '''+CONVERT(VARCHAR(30),@lastday,21)+''' ORDER BY DetectionTime ASC'
       SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
       '+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
       '+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
       '+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
       '+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
       '+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
       '+@tablename+'. States
       FROM '+@tablename+' ,SYDeviceHost
       WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
       EXEC (@Sql)
   END
   --查询2月数据
   IF @Mouth='02'
   BEGIN
       SET @Mouth='02'
       SET @Year=cast(datepart(year,GETDATE()) as nvarchar(50))
       SET @tablename='DataCsv'+@Year+@Mouth
       SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
       '+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
       '+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
       '+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
       '+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
       '+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
       '+@tablename+'. States
       FROM '+@tablename+' ,SYDeviceHost
       WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
       EXEC (@Sql)
   END
   --查询3月数据
   IF @Mouth='03'
   BEGIN
       SET @Mouth='03'
       SET @Year=cast(datepart(year,GETDATE()) as nvarchar(50))
       SET @tablename='DataCsv'+@Year+@Mouth
       SET @Sql='SELECT SYDeviceHost.HostNumber, SYDeviceHost.HostName, SYDeviceHost.HostCoordinate,
       '+@tablename+'.DetectionTime, '+@tablename+'. ProductQRCode,'+@tablename+'.ProductCode,
       '+@tablename+'.TestTtems, '+@tablename+'. RelativeVUnit,
       '+@tablename+'.AbsoluteVUnit, '+@tablename+'. UpperLimit,
       '+@tablename+'.LowerLimit, '+@tablename+'. NominalValue,
       '+@tablename+'.RelatValue, '+@tablename+'. AbsoluteValue,
       '+@tablename+'. States
       FROM '+@tablename+' ,SYDeviceHost
       WHERE SYDeviceHost.HostCoordinate ='''+@HostCoordinate+''' and SYDeviceHost.HostNumber='+@tablename+'. HostNumber ORDER BY DetectionTime ASC'
       EXEC (@Sql)
   END