MySQL-连接2个表和组值 [英] MySQL - Join 2 Tables and Group Values

问题描述

我在这里得到了一些很好的帮助，例如一些棘手的东西，所以我想请你们看看，看看是否有可能.

I've gotten some good help on here with some tricky stuff like this, so thought I'd have you guys take a look and see if this is even possible.

我在MySQL ..公司和资源中有两个表...

I have two tables in MySQL .. company and resources ...

公司可以拥有多种资源..所以...

companies can have multiple resources .. so ...

company
--------
companyId (primary/auto inc)
companyName

resources
---------
resourceId (primary/auto inc)
companyId
resourceName

我想查询两个表并将其分组，以便结果看起来像这样……

I'd like to query the two tables and group it so the results look like this ...

示例...

Company Name
 ResourceName1
 ResourceName2
 ResourceName3
Company Name
 Resource Name4
 Resource Name5
Company Name3
 Resource Name6

等.

希望是有意义的.任何帮助都非常感激.

Hope that makes sense.. any help is much appreciated.

更新

确定以更好地说明这一点...

Ok to help clarify this a little better ...

我需要提取company表中的每个公司，然后使用该companyId，使用它来提取资源表中具有相同companyId的每个资源...

I need to pull up every company in the company table, and then using that companyId, use that to pull every resource in the resource table with that same companyId ...

所以列表看起来像这样……

So the list would look like this ...

Bob's Plumbing (aka Company name)
  Bob the Plumber (has CompanyId of Bob's Plumbing)
  Bob's Assistant (has CompanyId of Bob's Plumbing)
Joe's Roofing (aka Company name)
  Joe the Roofer (has CompanyId of Joe's Roofing)

等.等等...

希望可以消除一些困惑.

Hope that clears up some of the confusion.

推荐答案

我可以建议此查询-

已编辑

SELECT
  c.companyName,
  GROUP_CONCAT(CONCAT(r.resourceName, ' ', r.resourceFirstname, ' ', r.resourceLastname) SEPARATOR '\r\n')
FROM
  company c
JOIN
  resources r
    ON c.companyId = r.companyId
GROUP BY
  c.companyId;

请注意，公司名称及其资源将在一行中.

Note, that company name and its resources will be in one row.

这篇关于MySQL-连接2个表和组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！