背景

  数据库（SQLServer）当初设计时采用的是按设备进行分表，以DOP_[设备编号]记录产量，现在需求是统计昨天8:30到今天8:30的产量显示到可视化中。乍一听有点懵，那就逐个拆解吧。

拆解

1、怎么在几百个表中筛出以DOP_开头的表？

SELECT name
FROM sys.tables
WHERE name LIKE 'DOP_%'

2、怎么逐个拿到表名进行分别查询？

• • 用游标循环

-- 声明游标查询符合条件的表
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR 
    SELECT name 
    FROM sys.tables 
    WHERE name LIKE 'DOP_%';
OPEN @MyCursor;
FETCH NEXT FROM @MyCursor INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '正在处理表：' + @tablename;
    FETCH NEXT FROM @MyCursor INTO @tablename;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;

3、怎么把字符串表名转为数据库能知道的表？

• • 使用 QUOTENAME(@tablename)

4、怎么执行动态SQL，动态拼接参数？

• • 使用 EXEC sp_executesql

5、怎么将单个设备的查询结果存入结果集？

• • 先定义好结果表结构

DECLARE @resTable TABLE (
    deviceNumber nvarchar(200),
    dop int
);

• • 再将查询结果插入到结果表

INSERT INTO @resTable EXEC sp_executesql @sql,
  -- 声明参数类型
N'@p_startTime datetime, @p_endTime datetime',
 -- 传递参数
@p_startTime = @startTime, @p_endTime = @endTime; 

6、为了保证各个设备都有结果，要将无结果的设备赋予默认值，那怎么知道它有没有查询结果呢？

• • @@ROWCOUNT是SQL Server的系统变量，用于返回当前批次中上一条语句影响的行数

-- 获取上一条 SQL 影响的行数
SET @rowCount = @@ROWCOUNT;
 -- 2. 若时间范围内无数据，插入默认记录0
IF @rowCount = 0
BEGIN
    SET @sql = N'
        SELECT TOP 1
            device_number AS deviceNumber,
            0 AS dop
        FROM ' + QUOTENAME(@tablename);
    INSERT INTO @resTable
    EXEC sp_executesql @sql;
END

7、为了增强存储过程的查询灵活性，把查询时间设置为入参？

ALTER PROCEDURE [dbo].[procDeviceDOPList]
  @startTime datetime,  -- 改为 datetime 类型（或 datetime2）
  @endTime datetime     -- 改为 datetime 类型
AS
BEGIN

END

8、怎么结合Mybatis-Plus调用存储过程？

// Mapper层
List<DeviceDOPItemDTO> selectDeviceDOPList(@Param("startTime") Date startTime, @Param("endTime") Date endTime);

<select id="selectDeviceDOPList" statementType="CALLABLE" resultType="cn.wetcland.datacollection.reportforms.dto.DeviceDOPItemDTO">
    {CALL procDeviceDOPList(#{startTime, jdbcType=DATE}, #{endTime, jdbcType=DATE})}
</select>

完整代码

ALTER PROCEDURE [dbo].[procDeviceDOPList]
  @startTime datetime,  -- 改为 datetime 类型（或 datetime2）
  @endTime datetime     -- 改为 datetime 类型
AS
BEGIN
  SET NOCOUNT ON;  -- 减少网络传输，优化性能

  DECLARE @resTable TABLE (
        deviceNumber nvarchar(200),
        dop int
    );

    DECLARE @tablename VARCHAR(100);
    DECLARE @sql nvarchar(max);
    DECLARE @rowCount INT;  -- 用于接收动态 SQL 的行数

    DECLARE @MyCursor CURSOR;
    SET @MyCursor = CURSOR FOR
        SELECT name
        FROM sys.tables
        WHERE name LIKE 'DOP_%';

    OPEN @MyCursor;

    FETCH NEXT FROM @MyCursor INTO @tablename;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 1. 查询时间范围内的数据，使用参数化传递时间
        SET @sql = N'
            SELECT
                device_number AS deviceNumber,
                SUM(output_diff) AS dop
            FROM ' + QUOTENAME(@tablename) + N'
            WHERE createdate >= @p_startTime
              AND createdate <= @p_endTime
            GROUP BY device_number';

        INSERT INTO @resTable
        EXEC sp_executesql 
            @sql,
            N'@p_startTime datetime, @p_endTime datetime',  -- 声明参数类型
            @p_startTime = @startTime, @p_endTime = @endTime;  -- 传递参数

        -- 获取上一条 SQL 影响的行数
        SET @rowCount = @@ROWCOUNT;

        -- 2. 若时间范围内无数据，插入默认记录（0）
        IF @rowCount = 0
        BEGIN
            SET @sql = N'
                SELECT TOP 1
                    device_number AS deviceNumber,
                    0 AS dop
                FROM ' + QUOTENAME(@tablename);

            INSERT INTO @resTable
            EXEC sp_executesql @sql;
        END

        FETCH NEXT FROM @MyCursor INTO @tablename;
    END;

    CLOSE @MyCursor;
    DEALLOCATE @MyCursor;

    SELECT * FROM @resTable;
END

阅读原文：原文链接