如何使用 SQL 查询创建逗号分隔的列表?

2021-12-01 00:00:00 sql tsql sql-server string-aggregation

我有 3 个表:

  • 应用程序(ID、名称)
  • 资源(id、名称)
  • 应用程序资源(id、app_id、resource_id)

我想在 GUI 上显示所有资源名称的表格.在每一行的一个单元格中，我想列出该资源的所有应用程序(逗号分隔).

I want to show on a GUI a table of all resource names. In one cell in each row I would like to list out all of the applications (comma separated) of that resource.

所以问题是，在 SQL 中执行此操作的最佳方法是什么，因为我需要获取所有资源，并且还需要获取每个资源的所有应用程序?

So the question is, what is the best way to do this in SQL as I need to get all resources and I also need to get all applications for each resource?

我是否首先从资源中运行 select *，然后遍历每个资源并对每个资源执行单独的查询以获取该资源的应用程序列表?

Do I run a select * from resources first and then loop through each resource and do a separate query per resource to get the list of applications for that resource?

有没有办法在一个查询中做到这一点?

Is there a way I can do this in one query?

推荐答案

没有办法以与数据库无关的方式来做到这一点.所以你需要像这样获取整个数据集:

There is no way to do it in a DB-agnostic way. So you need to get the whole data-set like this:

select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id

然后在按 ResName 分组的同时以编程方式连接 AppName.

And then concat the AppName programmatically while grouping by ResName.

相关文章