英文:
when try to make database separated comma i get error invalid in the select list because it is not contained in either an aggregate?
问题
我在SQL Server 2012上工作，遇到了错误，但不知道如何解决错误。
Msg 8120, Level 16, State 1, Line 43
选择列表中的列'#servers.ServerId'无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。
我需要做的是为每个应用程序和服务器显示逗号分隔的数据库，因为每个应用程序有一个服务器，而每个服务器有多个数据库。
我尝试的是：
SELECT
p.ApplicationId,
S.ServerName,
[数据库]= STUFF((SELECT DISTINCT ',' + pt.DatabaseName
FROM #Database pt
WHERE S.ServerId = pt.ServerId
FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, '')
FROM
#application p
INNER JOIN
#servers S ON S.ServerId = P.ServerId
GROUP BY
p.ApplicationId, S.ServerName
ORDER BY
p.ApplicationId, S.ServerName
代码表的详细信息：
CREATE TABLE #application
(
ApplicationId int,
ServerId int
)
INSERT INTO #application (ApplicationId, ServerId)
VALUES (1, 1), (2, 2)
CREATE TABLE #servers
(
ServerId int,
ServerName nvarchar(50)
)
INSERT INTO #servers (ServerId, ServerName)
VALUES (1, 'LinuxServer'),
(2, 'WindowsServer')
CREATE TABLE #Database
(
DatabaseId int,
ServerId int,
DatabaseName nvarchar(50)
)
INSERT INTO #Database (DatabaseId, ServerId, DatabaseName)
VALUES (1, 1, 'DB1'), (2, 1, 'DB2'),
(3, 2, 'DB3'), (4, 2, 'DB4')
预期结果：
英文:
I work on SQL Server 2012 I face error and I don't know how to solve error
> Msg 8120, Level 16, State 1, Line 43
> Column '#servers.ServerId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What I need to do is display database separated comma for every application and server, because every application have one server and every server have multi database.
What I tried is :
SELECT
p.ApplicationId,
S.ServerName,
[DataBase]= STUFF((SELECT DISTINCT ',' + pt.DatabaseName
FROM #Database pt
WHERE S.ServerId = pt.ServerId
FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 2, N'')
FROM
#application p
INNER JOIN
#servers S ON S.ServerId = P.ServerId
GROUP BY
p.ApplicationId, S.ServerName
ORDER BY
p.ApplicationId, S.ServerName
Code table details
CREATE TABLE #application
(
ApplicationId int,
ServerId int
)
INSERT INTO #application (ApplicationId, ServerId)
VALUES (1, 1), (2, 2)
CREATE TABLE #servers
(
ServerId int,
ServerName nvarchar(50),
)
INSERT INTO #servers (ServerId, ServerName)
VALUES (1, 'LinuxServer'),
(2, 'WindowsServer')
CREATE TABLE #Database
(
DatabaseId int,
ServerId int,
DatabaseName nvarchar(50),
)
INSERT INTO #Database (DatabaseId, ServerId, DatabaseName)
VALUES (1, 1, 'DB1'), (2, 1, 'DB2'),
(3, 2, 'DB3'), (4, 2, 'DB4')
Expected result
答案1
得分: 2
不需要使用 group by，因为 for xml path('') 会为每个应用程序和服务器生成一个聚合字符串。
以下是将生成您期望结果的代码：
select
ApplicationId,
ServerName,
stuff(Databases, 1, 2, N'')
from
(
select
a.ApplicationId,
s.ServerName,
(
select ', ' + d.DatabaseName
from #Database d
where d.ServerId = s.ServerId
order by d.DatabaseName
for xml path('')
) Databases
from #application a
inner join #servers s on s.ServerId = a.ServerId
) q
order by ApplicationId, ServerName
建议1：在实施 for xml path('') 后使用 stuff。
建议2：确保在 for xml path('') 查询中使用 order by，以便按字母顺序列出数据库。
注意：在同一服务器中不可能有两个相同的数据库名称，因此不需要使用 distinct。
英文:
There is no need to use group by because for xml path('') would generate an aggregated string for each application and server.
Here is the code that will generate your expected result:
select
ApplicationId,
ServerName,
stuff (Databases, 1, 2, N'')
from
(
select
a.ApplicationId,
s.ServerName,
(
select ', ' + d.DatabaseName
from #Database d
where d.ServerId = s.ServerId
order by d.DatabaseName
for xml path('')
) Databases
from #application a
inner join #servers s on s.ServerId = a.ServerId
) q
order by ApplicationId, ServerName
suggestion1: Use stuff after implementing for xml path('').
suggestion2: Make sure to use order by in your for xml path('') query, so databases listed alphabetically.
note: There is not possible two identical database name in same server, so distinct is not necessary.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论