我需要获取带有字段的结果表
-table_name，min_date，max_date
这是我的查询，应该对所有表执行
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
然后我发现了如何获取所有表名
我是这样做的
SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`
WHERE `TABLES`.`TABLE_SCHEMA` = 'test'
AND `TABLES`.`TABLE_NAME` LIKE 'test%'
但是我不知道如何对所有表执行它，并将结果填写到新表中。
我试图以这种方式做到这一点
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
但是我得到了这个错误
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()
最佳答案
在开始处添加DELIMITER $$；在DELIMITER ;之后添加END。
摆脱声明Command的麻烦。而是使用@command，无需声明。
在SELECT @command;之后添加SELECT @command := ...;，以便我们可以进行一些调试。CLOSE和DEALLOCATE语句需要;才能终止它们。
测试FETCH的行是否用完。
您确实需要查看许多存储过程的示例，尤其是带有游标的示例。
更新
gh，我什至没有发现语法错误的一半。这可能有用(我不知道是因为我没有您的特定表或列。):
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;