使用Oracle SQL进行复杂计算(Complicated Calculation Using Oracle SQL)

 我为虚构的律师创建了一个数据库，我最后一次要完成的查询让我疯狂。 我需要计算一名律师在公司的职业生涯中所取得的总人数，我有time_spent和rate来增加乘数和特殊费率。 （特殊费率是公司合同的一次性费用，所以很少有这种费用）。 我能想到的最好的代码如下。 它做我想要的，但只显示工作在一个案件与特殊的速度适用于它的律师。 
 
 我基本上希望它能够在表中显示查询结果，即使特殊比率为NULL。 
 
 我已经订购了表格以显示最高金额，因此我可以使用ROWNUM仅显示前10％的收款人。 
 
CREATE VIEW rich_solicitors AS 
  SELECT notes.time_spent * rate.rate_amnt + special_rate.s_rate_amnt AS solicitor_made,          
        notes.case_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)

I have created a database for an imaginary solicitors, my last query to complete is driving me insane. I need to work out the total a solicitor has made in their career with the company, I have time_spent and rate to multiply and special rate to add. (special rate is a one off charge for corporate contracts so not many cases have them). the best I could come up with is the code below. It does what I want but only displays the solicitors working on a case with a special rate applied to it.
 
I essentially want it to display the result of the query in a table even if the special rate is NULL.
 
I have ordered the table to show the highest amount first so i can use ROWNUM to only show the top 10% earners.
 
CREATE VIEW rich_solicitors AS 
  SELECT notes.time_spent * rate.rate_amnt + special_rate.s_rate_amnt AS solicitor_made,          
        notes.case_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;
 
Query:
 
SELECT * 
  FROM rich_solicitors
 WHERE ROWNUM <= (SELECT COUNT(*)/10 
                    FROM rich_solicitors)

原文：https://stackoverflow.com/questions/5172230