SQL Server SQL按租户方式从每个表中获取行计数

tkclm6bt 于 2022-12-26 发布在其他

关注(0)|答案(2)|浏览(137)

我正在处理一个多租户应用程序。大多数表都包含一个tenant_id列。我需要特定tenant_id在所有表中的行数。
示例，表格：-

create table Employee(id integer, name varchar(100), tenantid integer);
insert into Employee(id, name, tenantid) values(1, "Stephan", 64);
insert into Employee(id, name, tenantid) values(2, "Alan", 64);
insert into Employee(id, name, tenantid) values(3, "Charles", 46);

create table Student(id integer, name varchar(100), standard integer, tenantid integer);
insert into Student(id, name, standard, tenantid) values(1, "James", 2, 64);
insert into Student(id, name, standard, tenantid) values(2, "Rony", 4, 64);
insert into Student(id, name, standard, tenantid) values(3, "Steve",6, 64);

create table Teacher(id integer, name varchar(100), subject varchar(100), tenantid integer);
insert into Teacher(id, name, subject, tenantid) values(1, "Alvin", "Science", 46);
insert into Teacher(id, name, subject, tenantid) values(2, "Neil", "Geography", 64);
insert into Teacher(id, name, subject, tenantid) values(3, "Amy", "Mathematics", 46);`

获取tenantid = 64的每个表中的行数的示例结果
| 表格名称|计数|
| - ------|- ------|
| 雇员|第二章|
| 学生|三个|
| 教师|1个|
如何遍历所有表并查询where tenantid = <tenantid>

sql-server

来源：https://stackoverflow.com/questions/74886040/sql-get-row-count-from-every-table-tenantwise

2条答案

按热度按时间

brc7rcf01#

使用动态查询。为每个表动态形成查询，使用string_agg()将其连接起来，然后使用sp_executesql执行查询

declare @tables varchar(max),
        @tenantid int,
        @sql nvarchar(max)

select @tables = 'Employee,Student,Teacher',
       @tenantid = 64;

select @sql = string_agg('select [TableName] = ''' + value + ''', [Count] = count(*) from ' + quotename(value) + ' where tenantid = @tenantid', ' union all' + char(13) )
from   string_split(@tables, ',')

print @sql

exec sp_executesql @sql, N'@tenantid int', @tenantid

db<>fiddle demo

赞(0）回复(0）举报 2022-12-26

polkgigr2#

使用UNION ALL：

SELECT 'Employee' AS "TableName", COUNT(*) AS "Count" FROM Employee WHERE tenantid = 64
UNION ALL
SELECT 'Student' AS "TableName", COUNT(*) AS "Count" FROM Student WHERE tenantid = 64
UNION ALL
SELECT 'Teacher' AS "TableName", COUNT(*) AS "Count" FROM Teacher WHERE tenantid = 64

查看示例（我不确定效率）：

CREATE VIEW some_counters_view AS
SELECT 'Employee' AS "TableName", tenantid FROM Employee
UNION ALL
SELECT 'Student' AS "TableName", tenantid FROM Student
UNION ALL
SELECT 'Teacher' AS "TableName", tenantid FROM Teacher
;

SELECT "TableName", COUNT(*) AS "Count"
FROM some_counters_view 
WHERE tenantid = 64
GROUP BY "TableName"

赞(0）回复(0）举报 2022-12-26

我来回答

SQL Server SQL按租户方式从每个表中获取行计数

2条答案

相关问题

热门标签

最新问答