select charge_code.charge_code_value,
end_code.end_code_value,
from
(select b.enti_id, b.hand_id,b.begin_date,value as charge_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = 714076 --特殊字段1
and b.BEGIN_DATE >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.END_DATE <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) charge_code,
(select b.enti_id, b.hand_id,b.begin_date, value as end_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = 714077 --特殊字段2
and b.BEGIN_DATE >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.END_DATE <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) end_code,
where charge_code.enti_id = end_code.enti_id
and charge_code.begin_date = end_code.begin_date问题:目前在特殊字段1中有数据,但是特殊字段2中没有数据,最后查询出来的结果没有数据。更改:只要特殊字段1或特殊字段2中其一有数据,就返回里面的数据,没有数据的字段可以不显示数据。自己改了改,使用union,但是最后查询出来的数据,成了一列了。求大牛帮忙,感激!
end_code.end_code_value,
from
(select b.enti_id, b.hand_id,b.begin_date,value as charge_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = 714076 --特殊字段1
and b.BEGIN_DATE >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.END_DATE <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) charge_code,
(select b.enti_id, b.hand_id,b.begin_date, value as end_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = 714077 --特殊字段2
and b.BEGIN_DATE >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.END_DATE <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) end_code,
where charge_code.enti_id = end_code.enti_id
and charge_code.begin_date = end_code.begin_date问题:目前在特殊字段1中有数据,但是特殊字段2中没有数据,最后查询出来的结果没有数据。更改:只要特殊字段1或特殊字段2中其一有数据,就返回里面的数据,没有数据的字段可以不显示数据。自己改了改,使用union,但是最后查询出来的数据,成了一列了。求大牛帮忙,感激!
select charge_code.charge_code_value,
end_code.end_code_value
from
(select b.enti_id, b.hand_id,b.begin_date,value as charge_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = '714078' --特殊字段1
and b.begin_date >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.end_date <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) charge_code,
(select b.enti_id, b.hand_id,b.begin_date, value as end_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = '714076' --特殊字段2
and b.begin_date >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.end_date <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) end_code
where (charge_code.enti_id = end_code.enti_id(+)
and charge_code.begin_date = end_code.begin_date(+))
union
select charge_code.charge_code_value,
end_code.end_code_value
from
(select b.enti_id, b.hand_id,b.begin_date,value as charge_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = '714078' --特殊字段1
and b.begin_date >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.end_date <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) charge_code,
(select b.enti_id, b.hand_id,b.begin_date, value as end_code_value
from table1 b, table2 h
where b.hand_id = h.id
and b.kpi_id = '714076' --特殊字段2
and b.begin_date >= to_date('2011-08-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and b.end_date <= to_date('2011-08-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
order by b.enti_id, b.hand_id) end_code
where (charge_code.enti_id(+) = end_code.enti_id
and charge_code.begin_date(+) = end_code.begin_date)
第一个是charge_code.enti_id = end_code.enti_id(+)
第二个反过来charge_code.enti_id(+) = end_code.enti_id