Mysql Rollup repeatingid字段

Question

我使用 group by 和 rollup 得到以下查询结果:

Divison Department      Section     Employee Name   Employee ID Hours

Assets  Asset Strategy  Not Defined Monty Mouse     480193      64.00
Assets  Asset Strategy  Not Defined Frank Flint     480165      67.50
Assets  Asset Strategy  Not Defined                 480165      131.50
Assets  Asset Strategy                              480165      131.50
Assets  Event Centre    Not Defined Sally Spoons    800192      72.00
Assets  Event Centre    Not Defined Randolph Smith  800199      37.50
Assets  Event Centre    Not Defined Petra Peters    800195      64.00
Assets  Event Centre    Not Defined                 800195      173.50
Assets  Event Centre                                800195      173.50

我想做的是阻止员工 ID 在汇总行中复制:

Divison Department      Section     Employee Name   Employee ID Hours

Assets  Asset Strategy  Not Defined Monty Mouse     480193      64.00
Assets  Asset Strategy  Not Defined Frank Flint     480165      67.50
Assets  Asset Strategy  Not Defined                             131.50
Assets  Asset Strategy                                          131.50
Assets  Event Centre    Not Defined Sally Spoons    800192      72.00
Assets  Event Centre    Not Defined Randolph Smith  800199      37.50
Assets  Event Centre    Not Defined Petra Peters    800195      64.00
Assets  Event Centre    Not Defined                             173.50
Assets  Event Centre                                            173.50

我读过其他关于使用 union 来尝试匹配来自非汇总查询的员工的帖子，但这对我没有用。

我也读过有关使用子选择(包装)获取员工 ID 的信息，但这只会导致相同的结果。

我的汇总报表按部门、部门、部门和员工姓名分组。如果我尝试将员工 ID 添加到此子句中，我会得到每次员工变更的汇总。我还更改了名称和 if 字段的顺序，并尝试按 id 而不是名称进行分组，但这只是以与上面的 id 相同的方式复制了名称。

我是在追逐不可能实现的梦想吗？这样显示数据实际上是不可能的吗？任何建议将不胜感激。

对于那些想要完整查询代码的人，这里是:

select distinct
  hr_func_desc('CD_DEPT_', p.department) as 'Department',
  hr_func_desc('CD_DIVN_', p.division) as 'Division',
  hr_func_desc('CD_SECT_', p.section) as 'Section',
  pe.payroll_name as 'Employee Name',
  pe.employee_id as 'Employee ID',
  max(e.termination_date) as 'Termination Date',
  sum(ph.ordinary_hours) as 'Ordinary Hours',
  sum(ph.overtime_1_hours) as 'Overtime 1 Hours',
  sum(ph.overtime_2_hours) as 'Overtime 2 Hours',
  sum(ph.overtime_1_hours) + sum(ph.overtime_2_hours) as 'Total Overtime Hours',
  sum(ph.ordinary_hours) + sum(ph.overtime_1_hours) + sum(ph.overtime_2_hours) as 'Total Hours Worked',
  sum(al.units) as 'Number of Standby Worked',
  sum(ph.statutory_hours) as 'Statutory Holidays',
  sum(ph.annual_leave_hours) as 'Annual Leave',
  sum(ph.long_service_leave_hours) as 'Long Service Leave',
  sum(ph.special_leave_hours) as 'Special Leave',
  sum(ph.time_in_alt_hours) as 'Alt Lieu',
  sum(ph.parental_leave_hours) as 'Parental Leave',
  sum(ph.sick_hours) as 'Sick',
  sum(ph.domestic_leave_hours) as 'Domestic',
  sum(ph.bereavement_hours) as 'Bereavement',
  sum(ph.acc_week_1_hours) as 'ACC Week 1',
  sum(ph.acc_hours) as 'ACC Unpaid',
  sum(ph.lwop_hours) as 'Leave Without Pay',
  sum(ph.sick_hours) + sum(ph.domestic_leave_hours) + sum(ph.bereavement_hours) + sum(ph.annual_leave_hours) + sum(ph.statutory_hours) +
sum(ph.special_leave_hours) + sum(ph.long_service_leave_hours) + sum(ph.time_in_lieu_hours) + sum(ph.time_in_alt_hours) + sum(ph.lwop_hours) +
sum(ph.standby_leave_hours) + sum(ph.parental_leave_hours) + sum(ph.acc_week_1_hours) + sum(ph.acc_hours) as 'Total Hours Absent',
  sum(ph.ordinary_hours) + sum(ph.overtime_1_hours) + sum(ph.overtime_2_hours) + sum(ph.statutory_hours) + sum(ph.annual_leave_hours) + 
sum(ph.long_service_leave_hours) + sum(ph.special_leave_hours) + sum(ph.time_in_alt_hours) + sum(ph.sick_hours) + sum(ph.domestic_leave_hours)  +
sum(ph.bereavement_hours) + sum(ph.acc_week_1_hours) as 'Total Hours Paid',
  hr_func_normal_hours(pe.employee_id) as 'Normal Hours Worked per Week',
  sum(cast(pt.pay_weeks as unsigned integer)) as 'Pay Weeks for Period Chosen',
  round((sum(ph.ordinary_hours) + sum(ph.overtime_1_hours) + sum(ph.overtime_2_hours) + sum(ph.sick_hours) + sum(ph.domestic_leave_hours) +
   sum(ph.bereavement_hours) + sum(ph.statutory_hours) + sum(ph.special_leave_hours) + sum(ph.time_in_lieu_hours) + sum(ph.time_in_alt_hours) +
   sum(ph.lwop_hours) + sum(ph.acc_week_1_hours) + sum(ph.acc_hours) + sum(ph.standby_leave_hours) + sum(ph.parental_leave_hours))
/sum(cast(pt.pay_weeks as unsigned integer)), 2) as 'Calculated FTE Hours',
  truncate(((sum(ph.ordinary_hours) + sum(ph.overtime_1_hours) + sum(ph.overtime_2_hours) + sum(ph.sick_hours) + sum(ph.domestic_leave_hours) +
   sum(ph.bereavement_hours) + sum(ph.statutory_hours) + sum(ph.special_leave_hours) + sum(ph.time_in_lieu_hours) + sum(ph.time_in_alt_hours) +
   sum(ph.lwop_hours) + sum(ph.acc_week_1_hours) + sum(ph.acc_hours) + sum(ph.standby_leave_hours) + sum(ph.parental_leave_hours))
/sum(cast(pt.pay_weeks as unsigned integer)))/hr_func_normal_hours(pe.employee_id), 3) as 'Calculated FTE'
from swpayroll.py_employees pe
  left outer join swhr_rails.hr_employees e on e.id = pe.employee_id
  left outer join swhr_rails.hr_employee_positions ep on pe.employee_id = ep.employee_id
  left outer join swhr_rails.hr_positions p on ep.position_id = p.id
  left outer join swpayroll.py_rep_hist_hours ph on pe.employee_id = ph.employee_id
  left outer join swpayroll.py_hist_dedns_allowances al
    on al.employee_id = pe.employee_id
       and al.pay_date = ph.pay_date
       and al.da_id in (33, 66, 67)
  left outer join swpayroll.py_hist_totals pt on pt.employee_id = pe.employee_id and pt.pay_date = ph.pay_date
where ep.position_id = (select min(x.position_id) from swhr_rails.hr_employee_positions as x 
                         where x.employee_id = pe.employee_id and x.position_end is null)
      and ph.pay_date between '2012-10-21' and '2012-11-04'
group by
  hr_func_desc('CD_DEPT_', p.department),
  hr_func_desc('CD_DIVN_', p.division),
  hr_func_desc('CD_SECT_', p.section),
  pe.payroll_name with rollup;

更小的代码片段:

select distinct
  hr_func_desc('CD_DEPT_', p.department) as 'Department',
  hr_func_desc('CD_DIVN_', p.division) as 'Division',
  hr_func_desc('CD_SECT_', p.section) as 'Section',
  pe.payroll_name as 'Employee Name',
  pe.employee_id as 'Employee ID',
  sum(ph.ordinary_hours) as 'Ordinary Hours'
from swpayroll.py_employees pe
  left outer join swhr_rails.hr_employees e on e.id = pe.employee_id
  left outer join swhr_rails.hr_employee_positions ep on pe.employee_id = ep.employee_id
  left outer join swhr_rails.hr_positions p on ep.position_id = p.id
  left outer join swpayroll.py_rep_hist_hours ph on pe.employee_id = ph.employee_id
  left outer join swpayroll.py_hist_dedns_allowances al
    on al.employee_id = pe.employee_id
       and al.pay_date = ph.pay_date
       and al.da_id in (33, 66, 67)
  left outer join swpayroll.py_hist_totals pt on pt.employee_id = pe.employee_id and pt.pay_date = ph.pay_date
where ep.position_id = (select min(x.position_id) from swhr_rails.hr_employee_positions as x 
                         where x.employee_id = pe.employee_id and x.position_end is null)
      and ph.pay_date between '2012-10-21' and '2012-11-04'
group by
  hr_func_desc('CD_DEPT_', p.department),
  hr_func_desc('CD_DIVN_', p.division),
  hr_func_desc('CD_SECT_', p.section),
  pe.payroll_name with rollup;

Answer 1

据我所知，在 mysql.com 论坛 ( http://forums.mysql.com/read.php?10,577674,577674#msg-577674 ) 中发布了相同的问题，没有办法在查询时消除这种重复。

这需要通过辅助程序语言以编程方式完成。