mysql - 如何将交叉连接与左外连接一起使用？

Question

我必须显示申请特定月份费用的员工名单，但我正在获取所有员工名单。

SELECT employee_id, expense_month, GROUP_CONCAT(CONCAT_WS('=', exp_type_text, monthly_exployee_expense))
FROM
(
SELECT tbl_employee.employee_id, expense_months.expense_month, tbl_expense_type.exp_type_id, tbl_expense_type.exp_type_text, SUM(expense_cost) AS monthly_exployee_expense
FROM tbl_employee
CROSS JOIN tbl_expense_type
CROSS JOIN
(
    SELECT DISTINCT DATE_FORMAT(expense_date, '%Y%m') AS expense_month
    FROM exp_tbl
) expense_months
LEFT OUTER JOIN exp_tbl
ON tbl_employee.employee_id = exp_tbl.employee_id
AND tbl_expense_type.exp_type_id = some_table.expense_type
AND expense_months.expense_month = DATE_FORMAT(exp_tbl.expense_date, '%Y%m')
GROUP BY tbl_employee.employee_id, expense_months.expense_month, tbl_expense_type.exp_type_id, tbl_expense_type.exp_type_text
) Sub1
GROUP BY employee_id, expense_month

这是我的查询..如何仅获取已报销的员工列表。

示例输出为:

3   Ramesh Kumar M  201402  Phone Expense=0=16,Consumable Purchase=0=11,Auto=0...
3   Ramesh Kumar M  201403  Consumable Purchase=0=11,Auto=0=6,2 wheeler=0=1,Lo...
3   Ramesh Kumar M  201404  Logistics/Transportation=0=18,Labour=0=13,Fuel=0=8...
3   Ramesh Kumar M  201405  Bus Travel=0=3,Office Vehicle=0=20,Others=0=15,Sta...
4   testexplevel1   201402  Others=0=15,Stay=0=10,Train Travel=0=5,Office Main...
4   testexplevel1   201403  Office Maintenance=0=17,Billable Purchase=0=12,Cal...
4   testexplevel1   201404  Call Taxi=0=7,4 wheeler=0=2,Guest House=0=19,Trans...

在该列表中不需要所有员工列表。仅显示 ramesh 数据。因为该员工只提出了 claim 。

Answer 1

有道理。您可以将所有月份和所有费用类型的所有员工交叉连接到一个大的笛卡尔积中。然后您离开加入实际费用，因此如果没有费用，则不会排除任何内容。

因此，快速解决办法是内部连接费用。这样做只会为您提供存在费用的员工月数。

但是这样做还可以让您完全删除交叉联接并将其全部更改为内部联接:

select
  emp.employee_id,
  DATE_FORMAT(ex.expense_date, '%Y%m'),
  GROUP_CONCAT(CONCAT_WS('=', et.exp_type_text, ex.monthly_exployee_expense))
from
  tbl_employee emp
  inner join exp_tbl ex on ex.employee_id = emp.employee_id
  inner join tbl_expense_type et on et.exp_type_id = ex.expense_type
group by
  emp.employee_id,
  DATE_FORMAT(ex.expense_date, '%Y%m')

现在，这个查询是不是更清晰了？ :)