时间戳

首页 > 解决方案 > 包含多个连接的复杂查询未执行右外连接

mysql - 包含多个连接的复杂查询未执行右外连接

问题描述

我正在编写一个包含多个连接的查询，我希望每个用户都针对所有类别类型显示条目。当我执行下面的查询时，仅返回 1 条记录，因为该员工u.employee_id = "0079-P"仅从事 1 个项目，但我想获取所有category_types用户工作时间的数据，对于他未从事的类别，其工作时间显示为空。 

Select u.employee_id As Employee_ID, u.user_name As UserName, COALESCE(primaryDept.ctd_name, primaryProj.ctd_name) As PrimaryDeptOrProj, region.region_name As Region, categoryType.ctd_id, categoryType.ctd_name, SUM(tsdd.workhours) 
    From users u 
    LEFT JOIN category_type_details primaryDept ON u.user_primary_department = primaryDept.ctd_id
    LEFT JOIN category_type_details primaryProj ON u.user_primary_project = primaryProj.ctd_id
    LEFT JOIN regions region ON u.region_id = region.region_id
    LEFT JOIN timesheets ts ON u.user_id = ts.timesheet_user
    INNER JOIN timesheet_mr tsmr ON ts.timesheet_caller = tsmr.tsmr_id
    INNER JOIN timesheet_details tsd ON ts.timesheet_id = tsd.tsd_timesheet_id
    INNER JOIN timesheet_day_details tsdd ON tsd.tsd_id = tsdd.tsd_id
    RIGHT OUTER JOIN category_type_details categoryType ON tsd.tsd_category_type_id = categoryType.ctd_id
    WHERE tsmr.tsmr_id = 14 and u.employee_id = "0079-P"
    GROUP BY u.user_id, tsd.tsd_category_type_id;

我尝试了这个带有变体的查询，它在任何情况下都返回 1 条记录。

标签： mysqlsqlsql-serverdatabase

解决方案

您可以将查询更改为：

SELECT u.employee_id AS Employee_ID, u.user_name AS UserName, 
    COALESCE(pd.ctd_name, pp.ctd_name) AS PrimaryDeptOrProj, 
    r.region_name AS Region, ct.ctd_id, ct.ctd_name, SUM(tsdd.workhours) 
FROM users u 
LEFT JOIN category_type_details pd ON u.user_primary_department = pd.ctd_id
LEFT JOIN category_type_details pp ON u.user_primary_project = pp.ctd_id
LEFT JOIN regions r ON u.region_id = r.region_id
LEFT JOIN timesheets ts ON u.user_id = ts.timesheet_user
INNER JOIN timesheet_mr tsmr ON ts.timesheet_caller = tsmr.tsmr_id AND tsmr.tsmr_id = 14
INNER JOIN timesheet_details tsd ON ts.timesheet_id = tsd.tsd_timesheet_id
INNER JOIN timesheet_day_details tsdd ON tsd.tsd_id = tsdd.tsd_id
RIGHT OUTER JOIN category_type_details ct ON tsd.tsd_category_type_id = ct.ctd_id AND u.employee_id = "0079-P"
GROUP BY ct.ctd_id, u.user_id, u.employee_id, u.user_name, 
    COALESCE(pd.ctd_name, pp.ctd_name), r.region_name, ct.ctd_name
ORDER BY ct.ctd_id, u.user_id, u.employee_id, u.user_name, 
    COALESCE(pd.ctd_name, pp.ctd_name), r.region_name, ct.ctd_name;

您只有 1 行，因为WHERE子句中的条件过滤NULL了其他类别的所有 user_id 行。

对于，您可以省略子句MySQL中的其他列：GROUP BY

SELECT u.employee_id AS Employee_ID, u.user_name AS UserName, 
    COALESCE(pd.ctd_name, pp.ctd_name) AS PrimaryDeptOrProj, 
    r.region_name AS Region, ct.ctd_id, ct.ctd_name, SUM(tsdd.workhours) 
FROM users u 
LEFT JOIN category_type_details pd ON u.user_primary_department = pd.ctd_id
LEFT JOIN category_type_details pp ON u.user_primary_project = pp.ctd_id
LEFT JOIN regions r ON u.region_id = r.region_id
LEFT JOIN timesheets ts ON u.user_id = ts.timesheet_user
INNER JOIN timesheet_mr tsmr ON ts.timesheet_caller = tsmr.tsmr_id AND tsmr.tsmr_id = 14
INNER JOIN timesheet_details tsd ON ts.timesheet_id = tsd.tsd_timesheet_id
INNER JOIN timesheet_day_details tsdd ON tsd.tsd_id = tsdd.tsd_id
RIGHT OUTER JOIN category_type_details ct ON tsd.tsd_category_type_id = ct.ctd_id AND u.employee_id = "0079-P"
GROUP BY ct.ctd_id, u.user_id
ORDER BY ct.ctd_id, u.user_id;

推荐阅读