SQL查询中的复杂计算
我昨天用相同的代码发布了一个类似的问题，我已经重写了，它应该是一个新规范的完美，但由于某种原因它不起作用。
我正在使用Oracle 10g Express。
以下代码正在计算收入最高的10％的律师。
CREATE VIEW rich_solicitors AS
select notes.time_spent*rate.rate_amnt+coalesce(special_rate.s_rate_amnt,0)
AS solicitor_made, notes.solicitor_id
FROM notes, rate, solicitor_rate, solicitor, case, contract, special_rate
WHERE notes.solicitor_id = solicitor.solicitor_id
AND solicitor.solicitor_id = solicitor_rate.solicitor_id
AND solicitor_rate.rate_id = rate.rate_id
AND notes.case_id = case.case_id
AND case.contract_id = contract.contract_id
AND contract.contract_id = special_rate.contract_id (+)
ORDER BY -solicitor_made;
SELECT * FROM rich_solicitors
WHERE ROWNUM <= (SELECT COUNT(*)/10 FROM rich_solicitors);
AND contract.start_date >= 01-01-10
AND contract.expiry_date <= 01-01-11
CREATE VIEW rich_solicitors1 AS
SELECT notes.time_spent*rate.rate_amnt+coalesce(special_rate.s_rate_amnt,0) AS solicitor_made, notes.solicitor_id, TO_CHAR(contract.start_date, 'DD-MM-YY'), TO_CHAR(contract.expiry_date, 'DD-MM-YY')
FROM notes, rate, solicitor_rate, solicitor, case, contract, special_rate
WHERE notes.solicitor_id = solicitor.solicitor_id
AND solicitor.solicitor_id = solicitor_rate.solicitor_id
AND solicitor_rate.rate_id = rate.rate_id
AND notes.case_id = case.case_id
AND case.contract_id = contract.contract_id
AND contract.contract_id = special_rate.contract_id (+)
AND contract.start_date >= 01-01-10
AND contract.expiry_date <= 01-01-11
ORDER BY -solicitor_made;
没有找到相关结果
已邀请:
2 个回复
臀博
SELECT notes.time_spent*rate.rate_amnt+NVL(special_rate.s_rate_amnt,0) AS solicitor_made, notes.solicitor_id, TO_CHAR(contract.start_date, 'DD-MM-YY'), TO_CHAR(contract.expiry_date, 'DD-MM-YY') FROM notes, rate, solicitor_rate, solicitor, case, contract, special_rate WHERE notes.solicitor_id = solicitor.solicitor_id AND solicitor.solicitor_id = solicitor_rate.solicitor_id AND solicitor_rate.rate_id = rate.rate_id AND notes.case_id = case.case_id AND case.contract_id = contract.contract_id AND contract.contract_id = special_rate.contract_id (+) AND contract.start_date >= to_date('01-01-10','MM-DD-YY') AND contract.expiry_date <= to_date('01-01-11' ,'MM-DD-YY') ORDER BY solicitor_made诞胃
SELECT * FROM ( SELECT solicitor_made, solicitor_id ,NTILE(10) OVER (ORDER BY solicitor_made DESC) decile FROM rich_solicitors ) WHERE decile = 1;