在数据库中对所有表进行分组查询
在数据库中对所有表进行分组查询
我有一个包含150多个表的数据库(我们称之为DB),例如:table1,table2等等。
问题
我想循环遍历所有的表,并按组获取行数,如下所示:
当前方法
目前我正在考虑将所有表附加在一起,或者手动操作!
表结构
name code A code1 A code2 A code6 A code98 B code1
期望输出
table_name name code count table1 A code1 100 table1 B code2 941 table2 A code1 98
每个表的代码
SELECT name, code, count(*) AS count FROM table1 GROUP BY name, code
问题的原因:用户想要对数据库中的所有表进行分组查询,但是想要得到一个单一的结果集,而不是分别得到每个表的结果集。
解决方法:
1. 使用临时表和sys.sp_MSforeachtable存储过程来实现。首先创建一个临时表#Temp,包含tableName、name、code和Count列。然后使用sys.sp_MSforeachtable存储过程遍历数据库中的每个表,将每个表的结果插入到临时表中,最后查询临时表的内容并删除临时表。
2. 使用动态SQL来实现。首先创建一个临时表#Temp,包含tableName、name、code和Count列。然后使用游标遍历数据库中的每个表,将每个表的结果拼接成动态SQL语句,然后使用sp_executesql函数执行动态SQL语句,并将结果插入到临时表中,最后查询临时表的内容并删除临时表。
如果只想对特定的表进行查询,可以在设置游标时添加过滤条件,只选择需要查询的表。例如:SELECT name FROM sys.tables WHERE type ='U' AND name IN ('table1', 'table46', 'table 23')。
如果想要在sys.sp_MSforeachtable存储过程中只查询特定的表,也可以在执行语句中添加过滤条件。例如:EXEC sys.sp_MSforeachtable =' insert into #Temp select ''?'' AS tableName,Name,Code,count(*) from ? group by Name, Code' , @whereand = ' And Object_id In (Select Object_id From sys.tables Where name in (''temp1'', ''temp3'', ''table 23''))'。