SELECT item_code, item_note, oper_code, falg
from (select item.item_code,
ITEM_NOTE,
DECODE(op.oper_code, 'WULI', op.oper_code, NULL) OPER_CODE,
DECODE(op.oper_code, 'WULI', 'T', 'F') falg
from hr_salary_item item,
hr_account_oper op,
hr_account acc,
hr_salary_muster mu
where item.item_code = mu.item_code
and mu.item_kind = acc.account_code
and op.account_code = acc.account_code)
group by item_code, item_note, oper_code, falg
order by item_code;
from (select item.item_code,
ITEM_NOTE,
DECODE(op.oper_code, 'WULI', op.oper_code, NULL) OPER_CODE,
DECODE(op.oper_code, 'WULI', 'T', 'F') falg
from hr_salary_item item,
hr_account_oper op,
hr_account acc,
hr_salary_muster mu
where item.item_code = mu.item_code
and mu.item_kind = acc.account_code
and op.account_code = acc.account_code)
group by item_code, item_note, oper_code, falg
order by item_code;
from (select item.item_code,
ITEM_NOTE,
DECODE(op.oper_code, 'WULI', op.oper_code, NULL) OPER_CODE,
DECODE(op.oper_code, 'WULI', 'T', 'F') falg
from hr_salary_item item,
hr_account_oper op,
hr_account acc,
hr_salary_muster mu
where item.item_code = mu.item_code
and mu.item_kind = acc.account_code
and op.account_code = acc.account_code)
group by item_code, item_note, oper_code, falg
order by item_code;
不行啊。。查询出来的结果没有达到和我发表的那个SQL的要求。 查询出来有重复的。
FROM(
select item.item_code,item.item_note,op.oper_code,'T' falg
from hr_salary_item item,hr_account_oper op,hr_account acc,hr_salary_muster mu
where item.item_code=mu.item_code
and mu.item_kind = acc.account_code
and op.account_code = acc.account_code
and op.oper_code = 'WULI'
union
SELECT ITEM_CODE,ITEM_NOTE,'' oper_code,'F' falg
FROM HR_SALARY_ITEM mu
where not exists
(select item.item_code
from hr_salary_item item,hr_account_oper op,hr_account acc
where item.item_code = mu.item_code
and mu.item_kind = acc.account_code
and op.account_code = acc.account_code
and op.oper_code = 'WULI')
)
group by item_code,item_note,oper_code,falg order by item_code
from (select item.item_code,
ITEM_NOTE,
DECODE(op.oper_code, 'WULI', op.oper_code, NULL) OPER_CODE,
DECODE(op.oper_code, 'WULI', 'T', 'F') falg
from hr_salary_item item,
hr_account_oper op,
hr_account acc,
hr_salary_muster mu
where item.item_code = mu.item_code
and mu.item_kind = acc.account_code
and op.account_code = acc.account_code)
group by item_code, item_note, oper_code, falg
order by item_code;