try
Select T.* From A T
Inner Join B
On A.fb2=B.fb2
Where (Select Count(*) From A Where fb2=T.fb2 And fb1<T.fb1)<B.fb4
Select T.* From A T
Inner Join B
On A.fb2=B.fb2
Where (Select Count(*) From A Where fb2=T.fb2 And fb1<T.fb1)<B.fb4
office custom net uf uf swatch
A1 123456 10 06-0001 06-0001 10
A2 123457 11 06-0001 06-0002 8
A3 123458 12 06-0001 06-0003 7
A4 123459 13 06-0004 06-0004 1
A5 123460 14 06-0004 … …
A6 123461 15 06-0004 (有2百行)
A7 123462 16 06-0002
A8 123463 17 06-0002
A9 123464 18 06-0009
A10 123465 19 06-0010
… … … …
(有10万行)
希望得到的是tblA表中06-0001的top N为10,06-0002 的Top N为8,也就是说这外Top N受tblB表的影响!
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
*/