英文:
count multiple tables in sql
问题
我有一个数据库，其中每天都有一张表，使其成为一个包含许多月甚至年份的巨大数据库。现在，我想统计每个月所有表中的记录数。我的意思是，我想要每个月所有表中记录的计数，但是分开来。
我已经使用了这个查询：
select
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200 )+...
这个查询是有用的，但它不会为我提供每个月的结果。它会给我查询中存在的所有表的总计数。感谢大家。
英文:
I have a database that has a table for each day making it a huge database consisting of so many months and even years. Now, I wanna count the records of all the tables in each month. I mean, I want the count of all the records in all tables but monthly apart.
I already used this query:
select
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200 )+...
witch is useful but it won't give me the result for each month. it gives me the total count of all the tables existing in the query.
Thx everyone.
答案1
得分: 0
以下是翻译后的代码部分：
你只需将相关的年/月/日数值硬编码到对每个表的查询中，将所有单独表的查询联接起来，然后查询该结果以获得您需要的最终结果。
此SQL将为您演示如何执行此操作；显然，您可能需要根据您的确切要求进行调整
WITH ALL_DATA AS
(SELECT '1400' AS TABLE_YEAR, '06' AS TABLE_MONTH, '01' AS TABLE_DAY, count(*) AS TABLE_COUNT FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] WHERE mti=200
UNION
SELECT '1400', '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] WHERE mti=200
UNION
SELECT '1400', '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] WHERE mti=200
)
SELECT TABLE_YEAR, TABLE_MONTH, SUM(TABLE_COUNT)
FROM ALL_DATA
GROUP BY TABLE_YEAR, TABLE_MONTH;
请注意，我已将代码中的HTML实体（例如'）还原为普通的单引号。
英文:
You should just be able to hard-code the relevant year/month/day values into your query against each table, UNION all individual table queries together and then query that result to give what ever final result you need.
This SQL will give you an example of how to do this; obviously you may need to adjust it to meet your exact requirements
WITH ALL_DATA AS
(SELECT '1400' TABLE_YEAR, '06' TABLE_MONTH, '01' AS TABLE_DAY, count(*) AS TABLE_COUNT FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200
UNION
SELECT '1400' , '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200
UNION
SELECT '1400' , '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200
)
SELECT TABLE_YEAR, TABLE_MONTH, SUM(TABLE_COUNT)
FROM ALL_DATA
GROUP BY TABLE_YEAR, TABLE_MONTH
;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论