嗨，我一直在把这个查询从mysql连接到oracle，因为oracle在子查询顺序中产生了问题。查询是：
SELECT bt_charges.bt_setup_id, bt_setups.name, IFNULL(bt_charges.charges_for,'OPD') as charges_for_vals, bt_charges.nc_applicable,bt_charges.unit_value,bt_charges.taxtype_id, bt_charges.id, bt_charges.amount, bt_charges.effective_date
FROM bt_setups JOIN bt_charges ON ( bt_charges.bt_setup_id = bt_setups.id AND
bt_charges.id = (SELECT id
FROM bt_charges ilaba
WHERE IFNULL(ilaba.charges_for,'OPD') = IFNULL(bt_charges.charges_for,'OPD')
AND ilaba.bt_setup_id= bt_setups.id AND ilaba.effective_date <= '2014-11-10'
AND ilaba.insprovider_id IS NULL AND ilaba.deleted=0
ORDER BY ilaba.effective_date DESC, ilaba.date_entered DESC
LIMIT 1))
WHERE bt_setups.status='Active' AND bt_setups.deleted=0
AND bt_charges.insprovider_id IS NULL
ORDER BY bt_setups.name, charges_for ASC
这里，bt_setups ( name, description )是提供的服务bt_charges (effective_date date, date_entered datetime, charger_for char, bt_setup_id foreign key(bt_setups), insprovider_id foreign key(insproviders) )包含从生效日期起适用的服务费用，包括供应商
SELECT bc.bt_setup_id, bs.name,
NVL(bc.charges_for,'OPD') as charges_for_vals,
bc.nc_applicable, bc.unit_value, bc.taxtype_id,
bc.id, bc.amount, bc.effective_date
FROM bt_setups bs JOIN bt_charges bc ON ( bc.bt_setup_id = bs.id AND
bc.id = (SELECT id FROM
(SELECT ilaba.id, ilaba.bt_setup_id
FROM bt_charges ilaba
WHERE NVL(ilaba.charges_for,'OPD') = NVL(bc.charges_for,'OPD')
AND ilaba.effective_date <= TO_DATE('2014-11-10', 'YYYY-MM-DD')
AND ilaba.insprovider_id IS NULL AND ilaba.deleted=0
ORDER BY ilaba.effective_date DESC, ilaba.date_entered DESC)
WHERE bt_setup_id = bs.id AND ROWNUM = 1
))
WHERE bs.status='Active' AND bs.deleted=0
AND bc.insprovider_id IS NULL
ORDER BY bs.name, charges_for ASC;
IFNULL->NVL
"2014-11-10"->日期（"2014-11-110"，"YYYY-MM-DD"）-我认为ilaba.effective_date具有日期类型
LIMIT 1->order by在子查询中+rownum=1在父查询中