问按给定顺序更改序列

EN

-- create sample table
declare @tbl table
(
    Id              int,
    Category        varchar(10),
    content_type    varchar(10),  
    Content         varchar(10), 
    IsCategorywise  int
)

-- insert sample data
insert into @tbl 
values
    (1,   'ABC',         'Image',          'content1',  1),
    (2,   'ABC',         'Image',          'content2',  1),
    (3,   'EFG',         'Video',          'content3',  0),
    (4,   'EFG',         'Image',          'content4',  0),
    (5,   'EFG',         'Image',          'content5',  0),
    (6,   'XYZ',         'Image',          'content6',  1),
    (7,   'XYZ',         'Image',          'content7',  1)

-- the query
; with 
-- use numbers / tally table if you have one. 
numbers as
(
    select  n = 1
    union all
    select  n = n + 1
    from    numbers
    where   n < 10
),
cte as
(
    select  *,      
            -- Group wise IsCategorywise value
            GrpCW   = min(IsCategorywise) over (partition by Category),
            -- generate row_number for GrpCW = 1 for each category order by Id
            rn  = case  when    min(IsCategorywise) over (partition by Category) = 1
                        then    row_number() over (partition by Category order by Id)
                        end
    from    @tbl
),
cte2 as
(
    select  *, 
            -- m is for repeating the GrpCW = 0 for each grouping
            m = case when GrpCW = 1 then 1 else max(rn) over () end
    from    cte
)
-- basically you want to order by "rn" but for cases where IsCategorywise = 0,
-- you want to repeat it. That is where the inner join to "numbers" comes in
select  Id, Category, content_type, Content, IsCategorywise
from    cte2 c
        inner join numbers n    on  n.n <= c.m
order by coalesce(rn, n), Category, Id

/*
Id          Category   content_type Content    IsCategorywise
----------- ---------- ------------ ---------- --------------
1           ABC        Image        content1   1
3           EFG        Video        content3   0
4           EFG        Image        content4   0
5           EFG        Image        content5   0
6           XYZ        Image        content6   1
2           ABC        Image        content2   1
3           EFG        Video        content3   0
4           EFG        Image        content4   0
5           EFG        Image        content5   0
7           XYZ        Image        content7   1
*/

SELECT
  Id
  ,Category
  ,content_type
  ,Content
  ,IsCategorywise
FROM (

    SELECT
      Id
      ,Category
      ,content_type
      ,Content
      ,IsCategorywise
      ,ROW_NUMBER() OVER ( ORDER BY Category,Id) as orderby
    FROM myTable
    WHERE IsCategorywise=1
    UNION ALL
    SELECT
      Id
      ,Category
      ,content_type
      ,Content
      ,IsCategorywise
      ,1.5
    FROM myTable
    WHERE IsCategorywise=0
    UNION ALL
    SELECT
      Id
      ,Category
      ,content_type
      ,Content
      ,IsCategorywise
      ,3.5
    FROM myTable
    WHERE IsCategorywise=0
) X
ORDER BY orderby