如何使用SQL查询创建逗号分隔列表? [英] How do I Create a Comma-Separated List using a SQL Query?
问题描述
我有3个表格:
- 应用程式(编号,名称)
- id,name)
- ApplicationsResources(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?
推荐答案
没有办法以DB不可知的方式做。
所以你需要得到整个数据集如下:
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 分组时
这篇关于如何使用SQL查询创建逗号分隔列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!