代码之家 › 专栏 › 技术社区 › Bren G.

MySQL合并两个查询

mysql sql

Bren G. · 技术社区 · 17 年前

我有以下表格设置:

分支表:

...
manager_id
sales_manager_id
admin_manager_id
...

上述列包含users表的外键。

为了使事情复杂化,我还有一个branches\u额外的表:

额外分支机构:

id
branch_id
user_id
position_id

其中包含用户的外键,分支机构的外键,这些外键将显示在网站上的分支机构联系人页面上。问题是,用户有时会显示多次,因为他们可以有多个职位/角色,我目前有两个查询用于返回“分支”表中指定的经理,然后返回“分支机构”表中的额外员工:

SELECT CONCAT( xoops_users_extra.first_name, ' ', xoops_users_extra.last_name ) AS full_name, , xoops_users_extra.profile_image_thumb, xoops_users.email,
            CASE xoops_users_extra.main_userid
                WHEN branches.manager_id THEN 'Manager'
                WHEN branches.sales_manager_id  THEN 'Sales Manager'
                WHEN branches.admin_manager_id  THEN 'Admin Manager'
                WHEN branches.ops_manager_id  THEN 'Ops Manager'
                WHEN branches.export_manager_id  THEN 'Export Manager'
                WHEN branches.import_manager_id  THEN 'Import Manager'
            END AS position,
            CASE xoops_users_extra.main_userid
                WHEN branches.manager_id THEN 1
                WHEN branches.sales_manager_id  THEN 2
                WHEN branches.admin_manager_id  THEN 3
                WHEN branches.ops_manager_id  THEN 4
                WHEN branches.export_manager_id  THEN 5
                WHEN branches.import_manager_id  THEN 6
            END AS sort_order
        FROM (branches, xoops_users_extra, xoops_users)
        WHERE ((branches.manager_id = xoops_users_extra.main_userid)
            OR (branches.sales_manager_id = xoops_users_extra.main_userid)
            OR (branches.admin_manager_id = xoops_users_extra.main_userid)
            OR (branches.ops_manager_id = xoops_users_extra.main_userid)
            OR (branches.export_manager_id = xoops_users_extra.main_userid)
            OR (branches.import_manager_id = xoops_users_extra.main_userid))
            AND xoops_users.uid = xoops_users_extra.main_userid 
            AND branches.branch_id = %d ORDER BY sort_order ASC

对于额外的员工:

 SELECT CONCAT( xoops_users_extra.first_name, ' ', xoops_users_extra.last_name ) AS full_name, 
        positions.description AS position, xoops_users.email, xoops_users_extra.profile_image_thumb AS thumbnail
    FROM (xoops_users, xoops_users_extra, branches_extra, branches, positions)
    WHERE branches_extra.uid = xoops_users_extra.main_userid
        AND positions.id = branches_extra.position
        AND branches.branch_id = branches_extra.branch_id
        AND xoops_users.uid = xoops_users_extra.main_userid
        AND branches_extra.display =1
        AND branches.branch_id = %d

1 回复 | 直到 17 年前

Lance Rushing 17 年前

GROUP_CONCAT ()

SELECT userid, GROUP_CONCAT(position SEPARATOR ', ') 
FROM (
   ... position joining.
) 
GROUP BY userid;