27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM
(
SELECT c.voucher_no ,
b.title ,
b.wage_set_type ,
b.data_month ,
d.dept_id ,
a.emp_id ,
d.note_info ,
e.wage_subject_name , --如果列名在wage_set_val表中有存,直接把这个字段换成列名.
a.val
FROM wage_set_val a
INNER JOIN wage_set b ON a.voucher_id = b.voucher_id
INNER JOIN ebs_v c ON b.voucher_id = c.voucher_id
INNER JOIN wage_set_emp d ON c.voucher_id = d.voucher_id AND a.emp_id = d.emp_id
INNER JOIN wage_subject E ON e.wage_subject_id=a.wage_subject_id
WHERE b.data_month BETWEEN '201001' AND '201308'
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id IN ( SELECT val
FROM mup_user_bo
WHERE user_id = 'admin'
AND bo_id = 'emp_company' ) )
) a
PIVOT
(
MAX(val) FOR wage_subject_name IN([基本工资],[固定加班工资],[岗位工资]) --这儿自己补全
) b
SELECT *
FROM
(
SELECT c.voucher_no ,
b.title ,
b.wage_set_type ,
b.data_month ,
d.dept_id ,
a.emp_id ,
d.note_info ,
e.wage_subject_name , --如果列名在wage_set_val表中有存,直接把这个字段换成列名.
a.val
FROM wage_set_val a ,
wage_set b ,
ebs_v c ,
wage_set_emp d,
wage_subject E --写在这儿
WHERE a.voucher_id = b.voucher_id
AND b.voucher_id = c.voucher_id
AND c.voucher_id = d.voucher_id
AND a.emp_id = d.emp_id
AND e.wage_subject_id=a.wage_subject_id
AND b.data_month BETWEEN '201001' AND '201308'
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id IN ( SELECT val
FROM mup_user_bo
WHERE user_id = 'admin'
AND bo_id = 'emp_company' ) )
) a
PIVOT
(
MAX(val) FOR wage_subject_name IN([基本工资],[固定加班工资],[岗位工资]) --这儿自己补全
) b
SELECT *
FROM
(
SELECT c.voucher_no ,
b.title ,
b.wage_set_type ,
b.data_month ,
d.dept_id ,
a.emp_id ,
d.note_info ,
a.wage_subject_id , --如果列名在wage_set_val表中有存,直接把这个字段换成列名.
a.val
FROM wage_set_val a ,
wage_set b ,
ebs_v c ,
wage_set_emp d --如果这些表中都没有,就再JOIN一下有列名的表.把a.wage_subject_id替换掉
WHERE a.voucher_id = b.voucher_id
AND b.voucher_id = c.voucher_id
AND c.voucher_id = d.voucher_id
AND a.emp_id = d.emp_id
AND b.data_month BETWEEN '201001' AND '201308'
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id IN ( SELECT val
FROM mup_user_bo
WHERE user_id = 'admin'
AND bo_id = 'emp_company' ) )
) a
PIVOT
(
MAX(val) FOR wage_subject_id IN([01],[02],[03],[04],[05],[06],[07],[10],[11],[13]) --注意:上面替换后,这儿就应该变成名称了:[基本工资],[固定加班工资],[岗位工资]....
) b
SELECT *
FROM
(
SELECT c.voucher_no ,
b.title ,
b.wage_set_type ,
b.data_month ,
d.dept_id ,
a.emp_id ,
d.note_info ,
a.wage_subject_id ,
a.val
FROM wage_set_val a ,
wage_set b ,
ebs_v c ,
wage_set_emp d
WHERE a.voucher_id = b.voucher_id
AND b.voucher_id = c.voucher_id
AND c.voucher_id = d.voucher_id
AND a.emp_id = d.emp_id
AND b.data_month BETWEEN '201001' AND '201308'
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id IN ( SELECT val
FROM mup_user_bo
WHERE user_id = 'admin'
AND bo_id = 'emp_company' ) )
) a
PIVOT
(
MAX(val) FOR wage_subject_id IN([01],[02],[03],[04],[05],[06],[07],[10],[11],[13])
) b