【SELECT 分类TOP N 的问题】

_____________________________________________________________________________________
声明:此文摘自网络,仅供学习研究之用.                                                                                                       

--建立測試環境  
  Create   Table   tblA  
  (office Varchar(5),  
    custom Varchar(6),  
    net Int,  
    uf Varchar(10))  
   
  Create   Table   tblB  
  (uf Varchar(10),  
    swatch Int)  
  --插入數據  
  Insert   tblA   Select   'A1', '123456', 10, '06-0001'  
  Union   All   Select   'A2', '123457', 11, '06-0001'  
  Union   All   Select   'A3', '123458', 12, '06-0001'  
  Union   All   Select   'A4', '123459', 13, '06-0004'  
  Union   All   Select   'A5', '123460', 14, '06-0004'  
  Union   All   Select   'A6', '123461', 15, '06-0004'  
  Union   All   Select   'A7', '123462', 16, '06-0002'  
  Union   All   Select   'A8', '123463', 17, '06-0002'  
  Union   All   Select   'A9', '123464', 18, '06-0009'  
  Union   All   Select   'A10','123465', 19, '06-0010'  
   
  Insert   tblB   Select   '06-0001', 3  
  Union   All   Select   '06-0002', 2  
  Union   All   Select   '06-0003', 2  
  Union   All   Select   '06-0004', 1  
  GO  
  --測試  
  Select   A.*   From   tblA   A      
  Inner   Join   tblB   B  
  On   A.uf=B.uf  
  Where   (Select   Count(*)   From   tblA   Where   uf=A.uf   And   net<A.net)<B.swatch  
  GO  
  --刪除測試環境  
  Drop   Table   tblA,tblB  
  --結果  
  /*  
  office custom net uf  
  A1 123456 10 06-0001  
  A2 123457 11 06-0001  
  A3 123458 12 06-0001  
  A4 123459 13 06-0004  
  A7 123462 16 06-0002  
  A8 123463 17 06-0002  
  */
---------------------------
  Select   A.*   From   #t3   A      
  Inner   Join   (select distinct prj_id from #t3) as B 
  On   A.prj_id=B.prj_id  
  Where   (Select   Count(*)   From   #t3   Where   prj_id=A.prj_id   And   amount>A.amount)<10