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;
解决方案 »
- Oracle导出多个表时如何判断列名是否存在
- oracle按时间段找出某个字段中所有0开始0结束的数,有几组
- 如何根据明细表的状态变化,实时修改主表的状态
- SQL语句返回插入ID,有插入,难道没法做到?!!
- oracle用Navicat 连接报12541无监听程序错误
- 存储过程中创建临时表,为何没有权限?
- 程序调用sqlplus时参数怎样写?
- 如何在可变数组上建立索引?
- ODBC无法连接oracle,提示ORA-01017: invalid username/password; logon denied
- 请问如何从一个函数中返回一个记录集
- 关于PostgreSQL,达梦,Oracle数据库的对比
- 请教oracle中一个sql语句的写法
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;