我的SQL查询
SELECT emp_location, emp_system_name, emp_compName, tran_type_code, emp_morn_checkin, emp_ot_checkin, emp_ot_checkout, over_time, ontime, updated_by
FROM timesheet_tran
WHERE emp_id = 'TMSTEST'
AND tran_as_of_date = '04/02/14'
传回资料为
我想找到一种合并和显示数据的方法
喜欢
emp_location -- emp_system_name -- morn_check_OTM -- mor_check_DLY -- overtime_OTM --overtime_OT
AMK ::1 8.45 8.38 1:15 6:30
我的SQL知识,不能做到这一点:(
您要查找的是将数据透视表行转换为列,不幸的是,MySQL没有本机数据透视表运算符,但是您可以使用该CASE
表达式来实现:
SELECT
emp_location,
emp_system_name,
MAX(CASE WHEN tran_type_code = 'OTM' THEN emp_morn_checkin END) AS morn_check_OTM,
MAX(CASE WHEN tran_type_code = 'DLY' THEN emp_morn_checkin END) AS morn_check_DLY,
MAX(CASE WHEN tran_type_code = 'OT' THEN emp_morn_checkin END) AS morn_check_OT,
MAX(CASE WHEN tran_type_code = 'OTM' THEN over_time END) AS over_time_OTM,
MAX(CASE WHEN tran_type_code = 'DLY' THEN over_time END) AS over_time_DLY,
MAX(CASE WHEN tran_type_code = 'OT' THEN over_time END) AS over_time_OT
FROM timesheet_tran
WHERE emp_id = 'TMSTEST'
AND tran_as_of_date = '04/02/14'
GROUP BY emp_location, emp_system_name;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句