MySQL查询多个表是否是辅助表的多个项目? [英] MySQL query for multiple tables being secondary tables multiple items?

问题描述

我有一个查询，目前我从这样的2个表中获取信息:

I have a query where I currently get information from 2 tables like this:

SELECT g.name 
FROM site_access b 
    JOIN groups g 
        ON b.group_id = g.id 
WHERE b.site_id = 1 
ORDER BY g.status ASC

现在我想用该查询创建另一个表，但是这个表将返回多于1行的行吗?

Now I wanted to have another table with this query but this one table would return more then 1 row is that possible at all ?

我所能做的就是从该表中拉出1行，我想要的字段是一个字符串字段，并且可以在此查询中将所有匹配项都拉到一起，并且也可以使用分隔符将结果联接在一起.

All I could make was it pull 1 row from that table, the field I want is a string field and it is ok to join the result with a separator too as long as all the matchs can be pulled together in this query.

如果您需要有关表的更多信息，或者有任何其他需要我说的话，我认为不需要，因为这主要是如何从联接/选择查询中拉取多行的示例.

上述查询结果的更新:

Admin
Member
Banned

现在在我的第3个表中，每个访问都有允许使用的命令，因此该第3个表将列出每个人可以访问的命令，例如:

Now with my 3rd table each access have commands they are allowed to use so this 3rd table would list what commands each one has access to, example:

Admin - add, del, announce
Member - find
Banned - none

UPDATE2:

• site_access

• site_access

site_id
group_id

组

groups

id
name
status

groups_commands

groups_commands

group_id
command_id

命令

commands

id
name

推荐答案

SELECT g.name, GROUP_CONCAT(c.command) AS commands
FROM site_access b 
    JOIN groups g 
        ON b.group_id = g.id 
    JOIN groups_commands gc
        ON g.id = gc.group_id
    JOIN commands c
        ON gc.command_id = c.id
WHERE b.site_id = 1 
GROUP BY g.name
ORDER BY g.status ASC

这篇关于MySQL查询多个表是否是辅助表的多个项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！