当前位置：文江博客话题详情

按统一顺序选择行

发布于 2024-11-30 13:40:59 字数 198 浏览 1 评论 0原文

我有一个表 customer，其中有 100 行。表中为活动/非活动客户设置状态列。

我想要做的是按特定顺序从表中选择所有客户即

前 7 个活跃客户，然后 3 个不活跃客户，再次 7 个活跃客户和 3 个不活跃客户，依此类推

我如何在查询中实现此目的。

我正在使用 sql server 2008 。请帮我....

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

恏ㄋ傷疤忘ㄋ疼 2024-12-07 13:40:59

根据您到目前为止列出的最低要求，类似这样的事情应该可以做到：

;with NumberedRows as (
    select
        *, --TODO - pick columns
        ROW_NUMBER() OVER(PARTITION BY status ORDER BY PKID) - 1 as rn
    from
        table
), AlteredOrdering as (
    select
        *, --TODO - pick columns
        CASE
            WHEN status = 'active' THEN (10 * (rn/7)) + (rn % 7)
            WHEN status = 'inactive' THEN (10 * (rn/3)) + 7 + (rn % 3)
        END as FinalOrder
    from NumberedRows
)
select * from AlteredOrdering ORDER BY FinalOrder

显然，根据需要更改表和列名称。 PKID 被假定为表中的其他列，可以根据该列对各个行进行排序。

AlteredOrdering CTE 中的神奇数字希望是显而易见的 - 7 和 3 来自问题，代表每个“组”中应出现的每个项目的数量。 10 是每个“组”的总大小。因此，如果 9 个处于活动状态，4 个处于非活动状态，则 CASE 表达式将如下所示：

WHEN status = 'active' THEN (13 * (rn/9)) + (rn % 9)
WHEN status = 'inactive' THEN (13 * (rn/4)) + 9 + (rn % 4)

Something like this should do it, based on the minimal requirements you've listed so far:

;with NumberedRows as (
    select
        *, --TODO - pick columns
        ROW_NUMBER() OVER(PARTITION BY status ORDER BY PKID) - 1 as rn
    from
        table
), AlteredOrdering as (
    select
        *, --TODO - pick columns
        CASE
            WHEN status = 'active' THEN (10 * (rn/7)) + (rn % 7)
            WHEN status = 'inactive' THEN (10 * (rn/3)) + 7 + (rn % 3)
        END as FinalOrder
    from NumberedRows
)
select * from AlteredOrdering ORDER BY FinalOrder

Obviously, altering the table and column names as appropriate. PKID is assumed to be some other column in the table against which the individual rows can be ordered.

The magic numbers in the AlteredOrdering CTE are hopefully obvious - 7 and 3 come from the question, and represent how many of each item should appear in each "group". The 10 is the total size of each "group". So if it was 9 active, 4 inactive, the CASE expression would look like:

WHEN status = 'active' THEN (13 * (rn/9)) + (rn % 9)
WHEN status = 'inactive' THEN (13 * (rn/4)) + 9 + (rn % 4)

回复收藏 0 原文

情域 2024-12-07 13:40:59

据我所知你不能。从 SQL 检索数据后，可能需要对其进行处理

回复收藏 0 原文

被翻牌 2024-12-07 13:40:59

如果你只有 100 行，那么也许限制 x,y 与 union 组合可以帮助

select * from table where status=active limit 0,7
union
select * from table where status=inactive limit 7,3
union
select * from table where status=active limit 10,7
union
select * from table where status=inactive limit 17,3
...

if you have only 100 rows then perhaps limit x,y combine with union can help

select * from table where status=active limit 0,7
union
select * from table where status=inactive limit 7,3
union
select * from table where status=active limit 10,7
union
select * from table where status=inactive limit 17,3
...

回复收藏 0 原文

~没有更多了~