SQL select dynamic number of records
Using SQL Server 2005, I am trying to select a certain number of records (dynamic) from one ta开发者_如何学Goble, based on another table to get what that number of records needs to be.
Table 1 has a Category ID and the number of records I want returned for that category.
Category ID TOP_Limit
----------------------
Cat 1 1
Cat 2 2
Cat 3 10
Table 2 has a Product ID, the Category ID, and a Quantity:
Product ID Category ID Quantity
---------------------------------
Part 1 Cat 1 10
Part 2 Cat 1 20
Part 3 Cat 2 100
Part 4 Cat 2 100
Part 5 Cat 2 50
Part 6 Cat 3 5
How can I write a query that will get me the correct "top" product records from Table 2 (Part 2, Part 3 & 4, Part 6)?
Try something like this:
;with cte as (
select ProductID, CategoryID, Quantity,
[row] = row_number() over(partition by CategoryID order by Quantity desc)
from Table2
)
select t2.Product, t2.CategoryID, t2.Quantity
from cte t2
join Table1 t1 on t2.CategoryID=t1.CategoryID
where t2.row <= t1.TOP_Limit
I think this will do it.
declare @Table1 table (
Cat int,
TOP_Limit int
)
declare @Table2 table (
Part int,
Cat int,
Quantity int
)
insert into @Table1
(Cat, TOP_Limit)
select 1,1 union all
select 2,2 union all
select 3,10
insert into @Table2
(Part, Cat, Quantity)
select 2,1,20 union all
select 3,2,100 union all
select 4,2,100 union all
select 5,2,50 union all
select 6,3,5
;with cteRowNums as (
select t2.Part, t2.Cat, t2.Quantity,
ROW_NUMBER() over(partition by t2.Cat order by t2.Quantity desc, t2.Part) as rownum
from @Table2 t2
inner join @Table1 t1
on t2.Cat = t1.Cat
)
select c.Part, c.Cat, c.Quantity
from cteRowNums c
inner join @Table1 t1
on c.Cat = t1.Cat
and c.rownum <= t1.TOP_Limit
精彩评论