考虑像这样的桌子
tbl_ranks
--------------------------------
family_id | item_id | view_count
--------------------------------
1 10 101
1 11 112
1 13 109
2 21 101
2 22 112
2 23 109
3 30 101
3 31 112
3 33 109
4 40 101
4 51 112
4 63 109
5 80 101
5 81 112
5 88 109
我需要生成一个结果集,其中包含按视图计数排序的家庭 ID(例如 1、2、3 和 4)子集的前两 (2) 行.我想做类似的事情
I need to generate a result set with the top two(2) rows for a subset of family ids (say, 1,2,3 and 4) ordered by view count. I'd like to do something like
select top 2 * from tbl_ranks where family_id = 1 order by view_count
union all
select top 2 * from tbl_ranks where family_id = 2 order by view_count
union all
select top 2 * from tbl_ranks where family_id = 3 order by view_count
union all
select top 2 * from tbl_ranks where family_id = 4 order by view_count
但是,当然,order by 以这种方式在 union all 上下文中无效.有什么建议?我知道我可以运行一组 4 个查询,将结果存储到临时表中并选择该临时表的内容作为最终结果,但我宁愿尽可能避免使用临时表.
but, of course, order by isn't valid in a union all context in this manner. Any suggestions? I know I could run a set of 4 queries, store the results into a temp table and select the contents of that temp as the final result, but I'd rather avoid using a temp table if possible.
注意:在实际应用中,每个family id的记录数是不确定的,view_counts也不是固定的,如上例所示.
Note: in the real app, the number of records per family id is indeterminate, and the view_counts are also not fixed as they appear in the above example.
你可以试试这样的
DECLARE @tbl_ranks TABLE(
family_id INT,
item_id INT,
view_count INT
)
INSERT INTO @tbl_ranks SELECT 1,10,101
INSERT INTO @tbl_ranks SELECT 1,11,112
INSERT INTO @tbl_ranks SELECT 1,13,109
INSERT INTO @tbl_ranks SELECT 2,21,101
INSERT INTO @tbl_ranks SELECT 2,22,112
INSERT INTO @tbl_ranks SELECT 2,23,109
INSERT INTO @tbl_ranks SELECT 3,30,101
INSERT INTO @tbl_ranks SELECT 3,31,112
INSERT INTO @tbl_ranks SELECT 3,33,109
INSERT INTO @tbl_ranks SELECT 4,40,101
INSERT INTO @tbl_ranks SELECT 4,51,112
INSERT INTO @tbl_ranks SELECT 4,63,109
INSERT INTO @tbl_ranks SELECT 5,80,101
INSERT INTO @tbl_ranks SELECT 5,81,112
INSERT INTO @tbl_ranks SELECT 5,88,109
SELECT *
FROm (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY view_count DESC) MyOrder
FROM @tbl_ranks
) MyOrders
WHERE MyOrder <= 2
这篇关于联合和订购的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!