問題描述

考慮像這樣的桌子

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

我需要生成一個結(jié)果集，其中包含按視圖計數(shù)排序的家庭 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

但是，當(dāng)然，order by 以這種方式在 union all 上下文中無效.有什么建議?我知道我可以運行一組 4 個查詢，將結(jié)果存儲到臨時表中并選擇該臨時表的內(nèi)容作為最終結(jié)果，但我寧愿盡可能避免使用臨時表.

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.

注意:在實際應(yīng)用中，每個family id的記錄數(shù)是不確定的，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

這篇關(guān)于聯(lián)合和訂購的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！