如何从两张表中选择金额前4位的记录

Question

我有两个表，其中一个表包含以下文档详细信息：

表1：包含客户和单据总额。

DocEntry  CustID  CustName  City       DocAmount
1         GF002   Raffy N   London     120.00 
2         GF025   Jhon      Liverpool   50.00
3         GF120   Keng      London     125.25
4         GF055   Tung L.   London      30.00 
5         GF020   Lee H.    Manchester  60.00 

表2：包含每个文档的项目和项目价格。

DocEntry  LineNum  ItemID  ItemName    ItemPrice  Qty  LineAmount
1         0        I0001   Mouse         6.00      5    30.00  
1         1        I0002   Key Broad     6.00      5    30.00 
1         2        I0200   Monitor      60.00      1    60.00 
2         0        I0501   Ext.HDD1     50.00      1    50.00 
3         0        I0665   Printer     125.00      1   125.00
4         0        I0002   Key Broad     6.00      4    24.00
4         1        I0001   Mouse         6.00      1     6.00 
5         0        I0050   ODD          12.00      1    12.00 
5         1        I0001   Mouse         6.00      8    48.00

我想从表1中选择DocAmount最高的前3个文档，在选择的前3个中必须显示表2 中的行详细信息

结果应该是：

Row  DocEntry  CustID  CustName DocAmount  ItemID  ItemName    ItemPrice  Qty  LineAmount
1    3         GF120   Keng     125.25     I0665   Printer     125.00      1   125.00
2    1         GF002   Raffy N  120.00     I0001   Mouse         6.00      5    30.00
3    1         GF002   Raffy N  120.00     I0002   Key Broad     6.00      5    30.00 
4    1         GF002   Raffy N  120.00     I0200   Monitor      60.00      1    60.00
5    5         GF020   Lee H.    60.00     I0050   ODD          12.00      1    12.00
5    5         GF020   Lee H.    60.00     I0001   Mouse         6.00      8    48.00

Answer 1

select Table2.DocEntry, CustID, CustName, DocAmount, ItemID, ItemName, 
ItemPrice, Qty, LineAmount
from (select top 3 * from Table1 order by DocAmount desc) TopDocs 
    join Table2 on TopDocs.DocEntry=Table2.DocEntry
order by DocAmount desc

SQL Fiddle在这里。