免责声明：标题可能具有误导性。
介绍
我已经获得了使我们所有的 SQL Server 实例保持最新状态的出色项目，这意味着我必须确保将适当的 Service Pack 应用到每个实例。
我们有一个数据库工具（缩写为 DBT）应用程序，它将我们的 MySQL、PostgreSQL、Microsoft SQL Server 和 Oracle RDBMS 数据库的所有信息存储在一个位置。
这个 DBT 应用程序将给定的数据库链接到应用程序，数据库链接到实例，实例链接到服务器，当然还有数据库链接到负责人。
数据库将并且可以有很多附加信息（DB 版本、状态、项目经理、数据库经理，...），为了简化解释，我已将这些信息排除在描述之外。
为了让项目继续进行，我想输出一个包含数据库总和并按所有其他相关信息分组的唯一 SQL Server 列表。其想法是对拥有最多数据库和最高复杂性（用户、应用程序、实例）的 SQL Server 进行概览。
长话短说
这是已经汇总的数据示例以及我期望实现的目标
示例结果集
SRV_NAME INST_NAME DB_NAME USER_NAME APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01 ANOTHER HIS_DB HIM TELLTAIL
SQLSRV_01 ANOTHER RZO_P4 YOU PSB IZQ
SQLSRV_01 GENERAL MY_DB2 ME HAL_2000
SQLSRV_01 GENERAL MY_DB3 ME HAL_2000
SQLSRV_01 GENERAL MY_DB4 ME HAL_2000
SQLSRV_01 GENERAL RZO_6_4 ME RZO_6.4
SQLSRV_01 GENERAL RZO_6_4_1 ME RZO_6.4
SQLSRV_01 GENERAL RZO_6_4_2 YOU RZO_6.4
SQLSRV_01 GENERAL YOUR_DB2 YOU HAL_2000
SQLSRV_01 SECURE DB1 ME HAL_2000
SQLSRV_01 SECURE PURCHGRAV HER PURCHGRAV
SQLSRV_01 SECURE TELLTAIL HER TELLTAIL
进一步分组/排序后的预期结果
SRV_NAME GRP_CNT_INST_NAME SUM_DB_NAME GRP_CNT_USER_NAME GRP_CNT_APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01 3 12 4 5
预期结果说明
示例中的 SQL Server SQLSRV_01具有三 (3) 个唯一实例、总共十二 (12) 个数据库、四 (4) 名负责人和链接到数据库的五 (5) 个应用程序。这是上面示例数据的摘要。
将其应用于整个 DBT 数据库将使我对最复杂的系统有一个概览。
参考资料已查阅
长版
遵循查询中涉及的每个表的数据和定义。最后，我已经完成了这些步骤。
表 [DBT].[服务器]
数据
ID | SRV_NAME | ...
----+-----------+----
1 | SQLSRV_01 |
2 | SQLSRV_11 |
3 | SQLSRV_21 |
定义
CREATE TABLE [DBT].[Server](
[ID] [int] NOT NULL,
[SRV_NAME] [nchar](20) NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Server] TO SCHEMA OWNER
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Server_ALL] ON [DBT].[Server]
(
[ID] ASC,
[SRV_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
表 [DBT].[实例]
数据
ID | INST_NAME | SRV_ID | ...
----+-------------+--------+----
1 | GENERAL | 1 |
2 | SECURE | 1 |
3 | ANOTHER | 1 |
4 | GENERAL | 2 |
5 | MSSQLSRV | 3 |
6 | MSSQLSRV | 2 |
7 | PRODUCTION | 2 |
8 | TESTING | 3 |
... | | |
定义
CREATE TABLE [DBT].[Instance](
[ID] [int] NOT NULL,
[INST_NAME] [nchar](20) NOT NULL,
[SRV_ID] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Instance] TO SCHEMA OWNER
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Instance_ALL] ON [DBT].[Instance]
(
[ID] ASC,
[INST_NAME] ASC,
[SRV_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
表[DBT].[应用]
数据
ID | APPL_NAME | ...
----+------------+-----
1 | HAL_2000 |
2 | RZO_6.4 |
3 | PSB IZQ |
4 | TELLTAIL |
5 | PURCHGRAV |
... | |
定义
CREATE TABLE [DBT].[Application](
[ID] [int] NOT NULL,
[APPL_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Application] TO SCHEMA OWNER
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Application_ALL] ON [DBT].[Application]
(
[ID] ASC,
[APPL_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
表 [DBT].[用户]
数据
ID | USER_NAME | ...
----+------------+-----
1 | ME |
2 | YOU |
3 | HIM |
4 | HER |
5 | THE OTHERS |
6 | ALIENS |
... | |
定义
CREATE TABLE [DBT].[User](
[ID] [int] NOT NULL,
[USER_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[User] TO SCHEMA OWNER
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_User_ALL] ON [DBT].[User]
(
[ID] ASC,
[USER_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
表 [DBT].[数据库]
数据
ID | DB_NAME | INST_ID | APPL_ID | USER_ID | ...
----+------------+---------+---------+---------+-----
1 | MY_DB2 | 1 | 1 | 1 |
2 | YOUR_DB2 | 1 | 1 | 2 |
3 | RZO_6_4 | 1 | 2 | 1 |
4 | DB1 | 2 | 1 | 1 |
5 | TELLTAIL | 2 | 4 | 4 |
6 | PURCHGRAV | 2 | 5 | 4 |
7 | HIS_DB | 3 | 4 | 3 |
8 | RZO_P4 | 3 | 3 | 2 |
9 | PURCH | 4 | 5 | 2 |
10 | YOUR_DB | 5 | 4 | 2 |
11 | HER_DB | 6 | 4 | 4 |
12 | TEST_PURCH | 6 | 5 | 5 |
13 | PROD_PURCH | 7 | 5 | 5 |
14 | TELLTAIL | 7 | 4 | 4 |
15 | IZQ_TEST | 8 | 3 | 3 |
16 | IZQ_PROD | 7 | 2 | 2 |
17 | HAL_CA1 | 5 | 1 | 3 |
18 | MY_DB3 | 1 | 1 | 1 |
19 | MY_DB4 | 1 | 1 | 1 |
20 | RZO_6_4_1 | 1 | 2 | 1 |
21 | RZO_6_4_2 | 1 | 2 | 2 |
22 | HAL_CA1_1 | 5 | 1 | 3 |
23 | HAL_CA1_2 | 5 | 1 | 6 |
... |
定义
CREATE TABLE [DBT].[Database](
[ID] [int] NOT NULL,
[DB_NAME] [nchar](20) NOT NULL,
[INST_ID] [int] NOT NULL,
[APPL_ID] [int] NOT NULL,
[USER_ID] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Database] TO SCHEMA OWNER
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Database_ID_DB_NAME_INST_ID] ON [DBT].[Database]
(
[ID] ASC,
[DB_NAME] ASC,
[INST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
目前为止就这样了。
选择所有信息
第一个陈述是我选择基本信息的起点。
SELECT s.[SRV_NAME], i.[INST_NAME], d.[DB_NAME], u.[USER_NAME], a.[APPL_NAME]
FROM [DBT].[Server] AS s
JOIN [DBT].[Instance] AS i
ON s.ID = i.SRV_ID
JOIN [DBT].[Database] AS d
ON i.[ID] = d.[INST_ID]
JOIN [DBT].[Application] AS a
ON d.[APPL_ID] = a.[ID]
JOIN [DBT].[User] AS u
ON u.ID = d.[USER_ID]
ORDER BY 1, 2, 3, 4, 5
这导致返回以下记录，并且是简介中示例数据的长版本：
SRV_NAME INST_NAME DB_NAME USER_NAME APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01 ANOTHER HIS_DB HIM TELLTAIL
SQLSRV_01 ANOTHER RZO_P4 YOU PSB IZQ
SQLSRV_01 GENERAL MY_DB2 ME HAL_2000
SQLSRV_01 GENERAL MY_DB3 ME HAL_2000
SQLSRV_01 GENERAL MY_DB4 ME HAL_2000
SQLSRV_01 GENERAL RZO_6_4 ME RZO_6.4
SQLSRV_01 GENERAL RZO_6_4_1 ME RZO_6.4
SQLSRV_01 GENERAL RZO_6_4_2 YOU RZO_6.4
SQLSRV_01 GENERAL YOUR_DB2 YOU HAL_2000
SQLSRV_01 SECURE DB1 ME HAL_2000
SQLSRV_01 SECURE PURCHGRAV HER PURCHGRAV
SQLSRV_01 SECURE TELLTAIL HER TELLTAIL
SQLSRV_11 GENERAL PURCH YOU PURCHGRAV
SQLSRV_11 MSSQLSRV HER_DB HER TELLTAIL
SQLSRV_11 MSSQLSRV TEST_PURCH THE OTHERS PURCHGRAV
SQLSRV_11 PRODUCTION IZQ_PROD YOU RZO_6.4
SQLSRV_11 PRODUCTION PROD_PURCH THE OTHERS PURCHGRAV
SQLSRV_11 PRODUCTION TELLTAIL HER TELLTAIL
SQLSRV_21 MSSQLSRV HAL_CA1 HIM HAL_2000
SQLSRV_21 MSSQLSRV HAL_CA1_1 HIM HAL_2000
SQLSRV_21 MSSQLSRV HAL_CA1_2 ALIENS HAL_2000
SQLSRV_21 MSSQLSRV YOUR_DB YOU TELLTAIL
SQLSRV_21 TESTING IZQ_TEST HIM PSB IZQ
按计数汇总（DB_NAME）
所以我认为按 , 分组SRV_NAME，INST_NAME然后USER_NAME添加APPL_NAME到COUNT(DB_NAME)select 语句是个好主意。
陈述
SELECT s.[SRV_NAME], i.[INST_NAME], count(d.[DB_NAME]) AS SUMDB, u.[USER_NAME], a.[APPL_NAME]
FROM [DBT].[Server] AS s
JOIN [DBT].[Instance] AS i
ON s.ID = i.SRV_ID
JOIN [DBT].[Database] AS d
ON i.[ID] = d.[INST_ID]
JOIN [DBT].[Application] AS a
ON d.[APPL_ID] = a.[ID]
JOIN [DBT].[User] AS u
ON u.ID = d.[USER_ID]
GROUP BY s.[SRV_NAME], i.[INST_NAME], u.[USER_NAME], a.[APPL_NAME]
ORDER BY 1, 2, 3, 4, 5
结果
SRV_NAME INST_NAME SUMDB USER_NAME APPL_NAME
-------------------- -------------------- ----------- -------------------- --------------------
SQLSRV_01 ANOTHER 1 HIM TELLTAIL
SQLSRV_01 ANOTHER 1 YOU PSB IZQ
SQLSRV_01 GENERAL 1 YOU HAL_2000
SQLSRV_01 GENERAL 1 YOU RZO_6.4
SQLSRV_01 GENERAL 2 ME RZO_6.4
SQLSRV_01 GENERAL 3 ME HAL_2000
SQLSRV_01 SECURE 1 HER PURCHGRAV
SQLSRV_01 SECURE 1 HER TELLTAIL
SQLSRV_01 SECURE 1 ME HAL_2000
SQLSRV_11 GENERAL 1 YOU PURCHGRAV
SQLSRV_11 MSSQLSRV 1 HER TELLTAIL
SQLSRV_11 MSSQLSRV 1 THE OTHERS PURCHGRAV
SQLSRV_11 PRODUCTION 1 HER TELLTAIL
SQLSRV_11 PRODUCTION 1 THE OTHERS PURCHGRAV
SQLSRV_11 PRODUCTION 1 YOU RZO_6.4
SQLSRV_21 MSSQLSRV 1 ALIENS HAL_2000
SQLSRV_21 MSSQLSRV 1 YOU TELLTAIL
SQLSRV_21 MSSQLSRV 2 HIM HAL_2000
SQLSRV_21 TESTING 1 HIM PSB IZQ
正如您从结果中看到的那样，还有进一步总结（分组？）的潜力，例如，通过,INST_NAME来概述最复杂的系统。USER_NAMEAPPL_NAME
对 INST_NAME、USER_NAME 和 APPL_NAME 进行分组
所以基本上我想根据介绍中解释的服务器对每个唯一（子）项目进行总结：
SRV_NAME GRP_CNT_INST_NAME SUM_DB_NAME GRP_CNT_USER_NAME GRP_CNT_APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01 3 12 4 5
嗯。查看在线书籍我可以选择OVER 子句 (Transact-SQL)并在相关列上进行分区。但是我可能会误解描述。
陈述
SELECT s.[SRV_NAME],
COUNT(i.[INST_NAME]) OVER (PARTITION by i.[INST_NAME]) as GRP_CNT_INST_NAME,
COUNT(d.[DB_NAME]) AS SUMDB,
COUNT(u.[USER_NAME]) OVER (PARTITION by u.[USER_NAME]) as GRP_CNT_USER_NAME,
COUNT(a.[APPL_NAME]) OVER (PARTITION by a.[APPL_NAME]) as GRP_CNT_APPL_NAME
FROM [DBT].[Server] AS s
JOIN [DBT].[Instance] AS i
ON s.ID = i.SRV_ID
JOIN [DBT].[Database] AS d
ON i.[ID] = d.[INST_ID]
JOIN [DBT].[Application] AS a
ON d.[APPL_ID] = a.[ID]
JOIN [DBT].[User] AS u
ON u.ID = d.[USER_ID]
GROUP BY s.[SRV_NAME]--, i.[INST_NAME], u.[USER_NAME], a.[APPL_NAME]
ORDER BY 1, 2, 3, 4, 5
结果
SRV_NAME GRP_CNT_INST_NAME SUMDB GRP_CNT_USER_NAME GRP_CNT_APPL_NAME
-------------------- ----------------- ----------- ----------------- -----------------
SQLSRV_01 2 1 3 5
SQLSRV_01 2 1 6 2
SQLSRV_01 3 1 3 5
SQLSRV_01 3 1 4 4
SQLSRV_01 3 1 4 5
SQLSRV_01 5 1 6 3
SQLSRV_01 5 1 6 5
SQLSRV_01 5 2 3 3
SQLSRV_01 5 3 3 5
...
...
That doesn't look like what I was expecting to achieve. But then again, I might need a totally different approach.
Question
I'm still trying to find the right way to summarise each sub-item so as to have an overview of the most complex systems. What is a possible solution to my problem?
It appears you want
COUNT(DISTINCT), which gives you the count of unique values in a column – seems to be exactly what you want.Based on your joins, it appears that DB rows are going to be unique, so you probably do not need
COUNT(DISTINCT)in that specific instance. If the output should reflect the number of actual databases, counting distinct names can give you a skewed result, since different instances might have databases with identical names andCOUNT(DISTINCT)would see that as a single item.On the other hand, there would probably be no issue if you counted IDs rather than names: