对所有表执行查询并在新表中填入数据

I need to get the result table with there fields - table_name, min_date, max_date

我需要在那里得到结果表 - table_name，min_date，max_date

Here is my query, which I should execute for all tables

这是我的查询，我应该为所有表执行

SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate
FROM (SELECT  short_date, type, value,  count(*) as cnt
FROM testTable
GROUP BY  short_date
HAVING COUNT(*) > 1) as Duplicates

Then I found out how to get all table names I do it in this way

然后我发现了如何以这种方式获取所有表名

SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`
WHERE `TABLES`.`TABLE_SCHEMA` = 'test'
AND `TABLES`.`TABLE_NAME` LIKE 'test%'

But I don't know how to execute it for all table and fill in the result in a new table.

但我不知道如何为所有表执行它并在新表中填入结果。

I tried to do it in this way

我试着这样做

DECLARE @DB_Name varchar(50)
DECLARE @Command varchar(100);
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`
WHERE `TABLES`.`TABLE_SCHEMA` = 'test'
AND `TABLES`.`TABLE_NAME` LIKE 'test%') as TableNames

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate
FROM (SELECT  short_date, type, value,  count(*) as cnt
FROM ' + @DB_Name + '
WHERE type = ''test''
GROUP BY  short_date, type, value
HAVING COUNT(*) > 1) as Duplicates'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

But I got this error

但我得到了这个错误

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @DB_Name varchar(50) DECLARE @Command varchar(100)' at line 1

语法错误或访问冲突：1064 SQL语法中有错误;查看与您的MySQL服务器版本对应的手册，以便在第1行'DECLARE @DB_Name varchar（50）DECLARE @Command varchar（100）'附近使用正确的语法

UPD

UPD

CREATE PROCEDURE GetData()
BEGIN

DECLARE @DB_Name varchar(50), @Command varchar(100);
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`
WHERE `TABLES`.`TABLE_SCHEMA` = 'test'
AND `TABLES`.`TABLE_NAME` LIKE 'test%_') as TableNames

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate
FROM (SELECT  short_date, type, value,  count(*) as cnt
FROM ' + @DB_Name + '
WHERE type = ''test''
GROUP BY  short_date, type, value
HAVING COUNT(*) > 1) as Duplicates'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END;

CLOSE database_cursor 
DEALLOCATE database_cursor

END;

CALL GetData()

3 个解决方案

#1

Add DELIMITER $$ at the start; add DELIMITER ; after END.

一开始就添加DELIMITER $$;添加DELIMITER;在END之后。

Get rid of declaring Command. Instead use @command, which does not need to be declared.

摆脱声明命令。而是使用@command，它不需要声明。

Add SELECT @command; after the SELECT @command := ...; so that we can do some debugging.

添加SELECT @command;在SELECT @command之后：= ...;这样我们就可以做一些调试了。

The CLOSE and DEALLOCATE statements need ; to terminate them.

CLOSE和DEALLOCATE语句需要;终止他们。

Test for running out of rows to FETCH.

测试用尽FETCH的行。

You really need to look at a number of examples of Stored procedures, especially those with cursors.

您真的需要查看一些存储过程的示例，尤其是那些带有游标的示例。

Update

更新

Ugh, I did not spot even half the syntax errors. This might work (I can't tell because I don't have your particular tables or columns.):

呃，我甚至没有发现一半的语法错误。这可能有效（我无法分辨，因为我没有您的特定表或列。）：

DROP PROCEDURE IF EXISTS so42856538;

DELIMITER $$

CREATE PROCEDURE so42856538()
    LANGUAGE SQL
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN

DECLARE _TableName varchar(64);
DECLARE _done INT DEFAULT FALSE;
DECLARE database_cursor CURSOR FOR 
    SELECT TABLE_NAME
        FROM `information_schema`.`TABLES`
        WHERE `TABLE_SCHEMA` = 'test'
          AND `TABLE_NAME` LIKE 'test%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

OPEN database_cursor;
curs_loop: LOOP
    FETCH NEXT FROM database_cursor INTO _TableName;
    IF _done THEN  LEAVE curs_loop;  END IF;
    SET @Command := CONCAT(
           'SELECT MIN(short_date) as FirstDuplicatedDate,
                           MAX(short_date) as LastDuplicatedDate
           FROM ( SELECT  short_date, type, value,  count(*) as cnt
                    FROM ', _TableName, '
                    WHERE type = "test"
                    GROUP BY  short_date, type, value
                    HAVING COUNT(*) > 1 ) as Duplicates'
                        );
    SELECT _TableName, @command;   -- Debugging (remove if it is clutter)
    PREPARE _sql FROM @command;
    EXECUTE _sql;
    DEALLOCATE PREPARE _sql;
END LOOP;
CLOSE database_cursor;

END $$

DELIMITER ;

CALL so42856538;

3 个解决方案

#1

更多相关文章

随机推荐