但结果

但结果

关注
发信
关注(28)粉丝(399)

MySQL-两次连接同一张表，但结果不同

本文介绍了MySQL-两次连接同一张表，但结果不同的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我之前曾问过关于同一情况的问题，但是由于我对查询非常含糊，因此所提供的解决方案不适用于我的具体情况.这是我第二次尝试解决此问题的方法:

I previously asked a question in regards to the same situation here but since I was so vague about my query, the provided solution didn't work for my specific case. Here's my second attempt at resolving this:

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

I have a series of tables that are connected in strange ways... Here's the end result I'm trying to achieve with my SELECT query:

|----|---------|----------|----------|---------------|---------------|------------|---------------|
| id | company | city     | province | manager_name  | manager_email | staff_name | staff_email   |
|----|---------|----------|----------|---------------|---------------|------------|---------------|
| 1  | aaa     | toronto  | ON       | John Smith    | [email protected]  | Steve Chan | [email protected] |
| 2  | bbb     | sudbury  | ON       | Tom Bell      | [email protected]   |            |               |
| 3  | ccc     | hamilton | ON       | Bill Miller   | [email protected]  | Jill Smith | [email protected]  |
|----|---------|----------|----------|---------------|---------------|------------|---------------|

可能看不到数据库结构，这很令人困惑，但这是我的查询:

It may be confusing without seeing the database structure, but here's my query:

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

注意:

  • 我已经将一些以前的/替代尝试留在了那里(注释掉了)
  • 经理和职员都存储在同一表中，具有相同的字段first_name，last_name和primary_email ...将经理与职员分开的唯一内容是他们的r.user_role_types_id，已指定在角色表中
    • 经理= 7，员工= 21
    • I've left some of my previous / alternate attempts in there (commented out)
    • Both managers and staff are stored in the same table with the same fields first_name, last_name and primary_email... The only thing that separates a manager from a staff is their r.user_role_types_id, which is specified in the roles table
      • manager = 7, staff = 21

      在顶部注释掉manager部分，在底部注释GROUP BY部分，并取消注释CASE语句，这是我最近一次尝试获得这两个字段的尝试，但是它产生了如下所示的结果:

      Commenting out the manager section at the top, the GROUP BY section at the bottom, and un-commenting the CASE statements was my latest attempt at getting both of these fields, but it produced results like the following:

      |----|---------|----------|----------|---------------|---------------|
| id | company | city     | province | manager_email | staff_email   |
|----|---------|----------|----------|---------------|---------------|
| 1  | aaa     | toronto  | ON       | [email protected]  |               |
| 1  | aaa     | toronto  | ON       |               | [email protected] |
| 2  | bbb     | sudbury  | ON       | [email protected]   |               |
| 3  | ccc     | hamilton | ON       | [email protected]  |               |
| 3  | ccc     | hamilton | ON       |               | [email protected]  |
|----|---------|----------|----------|---------------|---------------|

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

      While this brought me close to my goal, I still needed to do the following:

      • 将重复的行分组为1，同时保留两封电子邮件
        • 这就是让我像以前一样问我以前的问题的原因
        • group duplicate lines into 1, retaining both emails
          • this specifically is what brought me to ask my previous question like I did

          如果需要的话，我很乐意进一步澄清，但是基本上，我只需要能够同时掌握manager和staff的详细信息……有什么想法吗?

          I'll be happy to clarify any of this further if needed, but basically, I just need to be able to grab both the manager and staff details... Any ideas?

          也请让我知道您对重命名/重新措词有任何建议，以提高可见度...很难找到要使用的词.

          Also let me know you have any suggestions on renaming / re-wording this question for better visibility... It was hard to find the words to use.

          推荐答案

          我最终在此处 a>和此处！这是我的新查询，以防万一其他人在这种奇怪的情况下发现自己在这里:

          I ended up figuring it out with help from both here and here! Here's my new query, just in case somebody else in this strange situation finds themselves 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

          这篇关于MySQL-两次连接同一张表，但结果不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-13 18:05
Powered by LMLPHP ©2024 但结果 0.034191