我的查询是：

SELECT
    t.*
FROM 
    (SELECT
         ig_idx,
         --, count(ig_root) over(partition by ig_root) AS GameCount1
         ig_root,
         ig_game_type,
         ROW_NUMBER() OVER (PARTITION BY ig_root
                            ORDER BY CASE WHEN ig_game_type IN ('12', '1x2') THEN 0 ELSE 1 END, ig_game_type ASC) AS seqnum
     FROM 
         Info_Game) AS t
ORDER BY 
    ig_root, seqnum

结果 ：

sqlfiddle

SQL Server 2014表架构

CREATE TABLE Info_Game
(
    [ig_idx] int, 
    [GameCount1] int, 
    [ig_root] int, 
    [ig_game_type] varchar(5), 
    [old_seqnum] int
);

INSERT INTO Info_Game ([ig_idx], [GameCount1], [ig_root], [ig_game_type], [old_seqnum])
VALUES (1, 3, 11, '12', 1),
       (2, 3, 11, 'ah', 2),
       (3, 3, 11, 'ou', 3),
       (4, 1, 12, '12', 1),
       (5, 4, 13, '1h_ah', 1), 
       (6, 4, 13, '2h_ou', 2),
       (7, 4, 13, 'ah', 3),
       (8, 4, 13, 'ou', 4),
       (9, 4, 13, '1h_ou', 5),
       (10, 5, 14, '1x2', 1),
       (11, 5, 14, '1h_ah', 2),
       (12, 5, 14, 'h', 3),
       (13, 5, 14, 'ou', 4),
       (14, 5, 14, '1h_ou', 5);

仅ig_root当seqnum为1且ig_game_type为‘12’或‘1x2’时。

我想获得相同编号的ig_root

我想要结果

sqlfiddle

SQL Server 2014架构

CREATE TABLE Info_Game
(
    [ig_idx] int, 
    [GameCount1] int, 
    [ig_root] int, 
    [ig_game_type] varchar(5), 
    [old_seqnum] int
);

INSERT INTO Info_Game ([ig_idx], [GameCount1], [ig_root], [ig_game_type], [old_seqnum])
VALUES (1, 3, 11, '12', 1),
       (2, 3, 11, 'ah', 2),
       (3, 3, 11, 'ou', 3),
       (4, 1, 12, '12', 1),
       (10, 5, 14, '1x2', 1),
       (11, 5, 14, '1h_ah', 2),
       (12, 5, 14, 'h', 3),
       (13, 5, 14, 'ou', 4),
       (14, 5, 14, '1h_ou', 5);

相同数量的ig_root，seqnum = 1且类型=‘12’或类型=‘1x2’我想让它获得相同数量的ig_root

ig_idx  ig_root ig_game_type  seqnum
1       11      12            1
2       11      ah            2
3       11      ou            3
4       12      12            1
5       13      1h_ah         1  <--- this 
9       13      1h_ou         2
6       13      2h_ou         3
7       13      ah            4
8       13      ou            5
10      14      1x2           1
11      14      1h_ah         2
14      14      1h_ou         3
12      14      h             4
13      14      ou            5

idx 5：这意味着seqnum为1，但类型不是12或1x2。所以我不想带ig_root 13。

像这样：

ig_idx  ig_root ig_game_type  seqnum
1       11      12            1
2       11      ah            2
3       11      ou            3
4       12      12            1
10      14      1x2           1
11      14      1h_ah         2
14      14      1h_ou         3
12      14      h             4
13      14      ou            5