sql - 获取有条件的数据

Question

declare @t table (
    Id      int ,
    Section int,
    Moment  date
);

insert into @t values
(   1   ,   1   , '2014-01-01'),
(   2   ,   1   , '2014-01-02'),
(   3   ,   1   , '2014-01-03'),
(   4   ,   1   , '2014-01-04'),
(   5   ,   1   , '2014-01-05'),

(   6   ,   2   , '2014-02-06'),
(   7   ,   2   , '2014-02-07'),
(   8   ,   2   , '2014-02-08'),
(   9   ,   2   , '2014-02-09'),
(   10  ,   2   , '2014-02-10'),

(   11  ,   3   , '2014-03-11'),
(   12  ,   3   , '2014-03-12'),
(   13  ,   3   , '2014-03-13'),
(   14  ,   3   , '2014-03-14'),
(   15  ,   3   , '2014-03-15');

这样获取数据

select * from @t

Id  Section Moment
1   1   2014-01-01
2   1   2014-01-02
3   1   2014-01-03
4   1   2014-01-04
5   1   2014-01-05
6   2   2014-02-06
7   2   2014-02-07
8   2   2014-02-08
9   2   2014-02-09
10  2   2014-02-10
11  3   2014-03-11
12  3   2014-03-12
13  3   2014-03-13
14  3   2014-03-14
15  3   2014-03-15

但我想要这样的数据。按 3 分组并明智地划分

如果 Ant 部分有 5 行，将创建 2 个组。

Id  Section Moment  Group by 3
1   1   1/1/2014    1
2   1   1/2/2014    1
3   1   1/3/2014    1
4   1   1/4/2014    2
5   1   1/5/2014    2
6   2   2/6/2014    3
7   2   2/7/2014    3
8   2   2/8/2014    3
9   2   2/9/2014    4
10  2   2/10/2014   4
11  3   3/11/2014   5
12  3   3/12/2014   5
13  3   3/13/2014   5
14  3   3/14/2014   6
15  3   3/15/2014   6

Answer 1

您可以使用窗口函数和算术。以下列举了每个部分:

select (row_number() over (partition by section order by moment) + 2) / 3, t.*
from @t;

然后应用 dense_rank() 得到你想要的:

select dense_rank() over (order by section, tempcol) as group3,
       t.*
from (select (row_number() over (partition by section order by moment) + 2) / 3 as tempcol, t.*
      from t
     ) t
order by id

Here是一个数据库<> fiddle 。