我需要在那里得到结果表字段
– 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
但我得到了这个错误
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
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()