在 SQL Server 2008 中是否有更好的方法来执行此连接？
我有以下执行子查询连接的 SQL 语句。如果您注意到，它使用 FOR XML .. ，这 .. 好吧 .. 让我有点发抖 :: 我觉得它有一些代码味道。
SQL Server 2008 有更好的方法来做到这一点吗？
SELECT a.CityId AS LocationId, a.City,
STUFF(
(SELECT ', ' + x.County
FROM [dbo].[CountiesView] x
INNER JOIN [dbo].[CityCounties] y ON x.CountyId = y.CountyId
WHERE y.CityId = a.CityId
FOR XML PATH (''))
, 1, 2, '')
FROM [dbo].[CitiesView] a
where a.StateCode = 'NY'
这段代码的作用是列出纽约州的所有城市（及其县）。因为一个城市可以存在于 1 个或多个县中，所以我希望连接县名称...否则我将得到每个县 1 个城市行（我不想要）。
这可以重构吗？
我尝试使用 COALESCE 但没有成功。
I've got the following SQL statement that does a subquery concatenation. If you notice, it uses FOR XML .. which .. well .. gives me the shivers a bit :: I feel like it's some code-smell.
Is there a better way to do this with SQL Server 2008 ?
SELECT a.CityId AS LocationId, a.City,
STUFF(
(SELECT ', ' + x.County
FROM [dbo].[CountiesView] x
INNER JOIN [dbo].[CityCounties] y ON x.CountyId = y.CountyId
WHERE y.CityId = a.CityId
FOR XML PATH (''))
, 1, 2, '')
FROM [dbo].[CitiesView] a
where a.StateCode = 'NY'
What this code is doing is it listing all the cities (and their counties) for the state of New York. Because a city can exist in 1 or more counties, I wish to CONCATENATE the County names ... otherwise i'll get 1 city row per county (which I don't want).
Can this be refactored, better?
I tried to use COALESCE but had no luck.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
“更好”是情人眼里出西施。我在 SQL Server 2005+ 中没有看到更好的方法来做到这一点，但我并没有努力寻找。
最好的方法可能是编写自己的聚合函数< /a>.我想我宁愿保留这个查询。
"Better" is in the eye of the beholder. I haven't seen a better way to do this in SQL Server 2005+, but I haven't been looking hard.
The very best way is probably to program your own aggregate function. I think I'd rather maintain this query.
由于您不关心
ORDER，因此您可以使用 CLR 自定义聚合来执行此操作。它的性能可能比 XML 方法更好，并且如果任何县包含&符号（或<和> 符号），您当前的方法将无法很好地处理。但我想这不太可能！）As you don't care about
ORDERyou can use a CLR custom aggregate to do this. It may well perform better than the XML method and your current approach won't deal well if any Counties contain the&symbol (or<and>but I guess that is unlikely!)