考虑一张像

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

我需要生成一个结果集,其中包含按 View 计数排序的家庭 ID(例如 1、2、3 和 4)子集的前两(2)行。
我想做类似的事情
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

但是,当然, 的 命令在 联合所有 上下文中以这种方式无效。有什么建议么?我知道我可以运行一组 4 个查询,将结果存储到临时表中并选择该临时表的内容作为最终结果,但我宁愿尽可能避免使用临时表。

注意:在实际应用中,每个家庭 id 的记录数是不确定的,view_counts 也不是固定的,如上例所示。

最佳答案

你可以试试这样的

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

关于sql - 联合和订购,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/1882807/

10-12 13:28