时间戳

首页 > 解决方案 > MySQL - 加入同一个表两次但获取不同的结果

mysql - MySQL - 加入同一个表两次但获取不同的结果

问题描述

我之前在这里问过一个关于同样情况的问题，但由于我对我的查询非常模糊，所以提供的解决方案不适用于我的具体情况。这是我解决此问题的第二次尝试：

我有一系列以奇怪​​的方式连接的表......这是我试图通过 SELECT 查询实现的最终结果：

|----|---------|----------|----------|---------------|---------------|------------|---------------|
| id | company | city     | province | manager_name  | manager_email | staff_name | staff_email   |
|----|---------|----------|----------|---------------|---------------|------------|---------------|
| 1  | aaa     | toronto  | ON       | John Smith    | john@aaa.com  | Steve Chan | steve@aaa.com |
| 2  | bbb     | sudbury  | ON       | Tom Bell      | tom@bbb.com   |            |               |
| 3  | ccc     | hamilton | ON       | Bill Miller   | bill@ccc.com  | Jill Smith | jill@ccc.com  |
|----|---------|----------|----------|---------------|---------------|------------|---------------|

如果没有看到数据库结构，可能会感到困惑，但这是我的查询：

SELECT
  f.id,
  f.firm_name company,
  f.city,
  f.province,

  -- manager
  CONCAT(b.first_name, ' ', b.last_name) manager_name, 
  b.primary_email manager_email

  -- staff
  -- CONCAT(c.first_name, ' ', s.last_name) staff_name,
  -- c.primary_email staff_email

  -- manager (alt attempt)
  -- CASE
  --   WHEN r.user_role_types_id = 7 THEN max(b.primary_email)
  -- END AS manager_email,

  -- staff (alt attempt)
  -- CASE
  --   WHEN r.user_role_types_id = 21 THEN max(b.primary_email)
  -- END AS staff_email

FROM
  projects p1

-- projects
JOIN
(
  SELECT
    id,
    MAX(fiscal_year) max_year, -- latest of the fiscal years
    firms_id
  FROM
    projects
  GROUP BY
    firms_id
) p2
ON p1.fiscal_year = p2.max_year
AND p1.firms_id = p2.firms_id

-- firms
JOIN (
  SELECT
    id,
    firm_name,
    is_cancelled,
    deleted,
    a.city,
    a.province,
  FROM
    firms

    -- address
    JOIN (
      SELECT
        firms_id,
        city,
        province
      FROM
        addresses
    ) a
    ON a.firms_id = id
) f
ON f.id = p1.firms_id

-- roles
JOIN (
  SELECT
    projects_id,
    users_id,
    user_role_types_id
  FROM
    project_user_roles
) r
ON r.projects_id = p1.id

-- managers
JOIN (
  SELECT
    id,
    first_name,
    last_name,
    primary_email
  FROM
    users
) m
ON m.id = r.users_id
-- AND r.user_role_types_id = 7

-- staff
-- JOIN (
--   SELECT
--     id,
--     first_name,
--     last_name,
--     primary_email
--   FROM
--     users
-- ) s
-- ON s.id = r.users_id
-- AND r.user_role_types_id = 21

WHERE
  p1.deleted = 0 -- project not deleted
  AND f.is_cancelled = 0 -- firm not cancelled
  AND f.deleted = 0 -- firm not deleted
  AND
  (
    r.user_role_types_id = 7 -- managers only
    OR r.user_role_types_id = 21 -- staff only
  )

GROUP BY
  f.id

ORDER BY
  f.firm_name

笔记：

注释掉manager顶部的GROUP BY部分、底部的部分和取消注释CASE语句是我获取这两个字段的最新尝试，但它产生了如下结果：

|----|---------|----------|----------|---------------|---------------|
| id | company | city     | province | manager_email | staff_email   |
|----|---------|----------|----------|---------------|---------------|
| 1  | aaa     | toronto  | ON       | john@aaa.com  |               |
| 1  | aaa     | toronto  | ON       |               | steve@aaa.com |
| 2  | bbb     | sudbury  | ON       | tom@bbb.com   |               |
| 3  | ccc     | hamilton | ON       | bill@ccc.com  |               |
| 3  | ccc     | hamilton | ON       |               | jill@ccc.com  |
|----|---------|----------|----------|---------------|---------------|

虽然这使我接近了我的目标，但我仍然需要执行以下操作：

如果需要，我很乐意进一步澄清这些，但基本上，我只需要能够抓住manager细节staff......有什么想法吗？

也让我知道您对重命名/重新措辞这个问题以提高知名度有任何建议......很难找到要使用的词。

标签： mysqlselectgroup-by

解决方案

我最终在here和here的帮助下弄清楚了！这是我的新查询，以防万一处于这种奇怪情况的其他人发现自己在这里：

SELECT
  f.id,
  f.firm_name company,
  f.city,
  f.province,
  f.region_type region,
  f.industry_type industry,

  -- manager
  MAX(
    CASE
      WHEN r.user_role_types_id = 7
      THEN CONCAT(u.first_name, ' ', u.last_name)
    END
  ) AS manager_name,
  MAX(
    CASE
      WHEN r.user_role_types_id = 7
      THEN u.primary_email
    END
  ) AS manager_email,

  -- staff
  MAX(
    CASE
      WHEN r.user_role_types_id = 21
      THEN CONCAT(u.first_name, ' ', u.last_name)
    END
  ) AS staff_name,
  MAX(
    CASE
      WHEN r.user_role_types_id = 21
      THEN u.primary_email
    END
  ) AS staff_email

FROM
  projects p1

-- projects
JOIN
(
  SELECT
    id,
    MAX(fiscal_year) max_year, -- latest of the fiscal years
    firms_id
  FROM
    projects
  GROUP BY
    firms_id
) p2
ON p1.fiscal_year = p2.max_year
AND p1.firms_id = p2.firms_id

-- firms
JOIN (
  SELECT
    id,
    firm_name,
    is_cancelled,
    deleted,
    a.city,
    a.province
  FROM
    firms

    -- address
    JOIN (
      SELECT
        firms_id,
        city,
        province
      FROM
        addresses
    ) a
    ON a.firms_id = id
) f
ON f.id = p1.firms_id

-- roles
JOIN (
  SELECT
    projects_id,
    users_id,
    user_role_types_id
  FROM
    project_user_roles
) r
ON r.projects_id = p1.id

-- users
JOIN (
  SELECT
    id,
    first_name,
    last_name,
    primary_email
  FROM
    users
) u
ON u.id = r.users_id

WHERE
  p1.deleted = 0 -- project not deleted
  AND f.is_cancelled = 0 -- firm not cancelled
  AND f.deleted = 0 -- firm not deleted
  AND f.industry_type != ''

GROUP BY
  f.id

ORDER BY
  f.firm_name

推荐阅读