Как разработать таблицу поиска SQL, где порядок имеет значение

Следующий запрос может вернуть ожидаемый результат:

WITH CTE AS (
    SELECT table1id, table2id, ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id) AS CustomOrder
    FROM JunctionTable 
    WHERE table2id IN (1, 2, 3)
) 
SELECT table1id 
FROM CTE
WHERE table2id = CustomOrder
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

Рабочая демонстрация с данными примера:

DECLARE @JunctionTable TABLE (id INT, table1id INT, table2id INT);

INSERT INTO @JunctionTable (id, table1id, table2id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 2),
(5, 2, 1),
(6, 2, 3);

WITH CTE AS (
    SELECT table1id, table2id, ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id) AS CustomOrder
    FROM @JunctionTable 
    WHERE table2id IN (1, 2, 3)
) 
SELECT table1id 
FROM CTE
WHERE table2id = CustomOrder
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

Если порядок имеет значение, то его нужно явно отслеживать, сделав его атрибутом данных. В вашей текущей модели порядок сохраняется неявно, и это не работает, потому что вы никогда не должны делать никаких предположений о порядке, в котором SQL хранит и возвращает данные.

Учитывая базовую таблицу в вашем вопросе, вы можете добавить varchar, упорядочивающий «правильный» порядок table2id, что-то вроде

Id  table1id  table2id  table2order
1       1         1        1,2,3
2       1         2        1,2,3
3       1         3        1,2,3
4       2         2        2,1,3
5       2         1        2,1,3
6       2         3        2,1,3

Это, конечно, не нормализовано, поэтому в качестве альтернативы вы можете иметь вторую таблицу:

table1id  table2order
    1        1,2,3
    2        2,1,3

Использование того или иного из них должно способствовать достижению ваших целей.

Чтобы решить эту проблему, вам нужно будет создать таблицу поиска, которая определяет желаемый порядок целевых table2id, которые вы хотите использовать, поскольку INclause не устанавливает порядок при сопоставлении двух наборов.

Если у вас есть SQL Server 2012 или более поздней версии, вы можете использовать оконную функцию LAG, чтобы помочь решить проблему следующим образом:

DECLARE @table2IdInOrder TABLE
(
    OrderID  INT IDENTITY(1,1)
    ,table2id INT
);

INSERT INTO @table2IdInOrder
VALUES(2), (1), (3);


WITH CTE_JT AS (
    SELECT   JT.id
            ,JT.table1id
            ,JT.table2id
            ,JT.table2id - LAG(JT.table2id, 1, 0) OVER(PARTITION BY JT.table1id ORDER BY JT.id) AS DeltaID
            ,COUNT(JT.table2id) OVER(PARTITION BY JT.table1id) AS RecordCount
    FROM @junctionTable JT
    WHERE   JT.table2id IN (SELECT table2id FROM @table2IdInOrder)
)
, CTE_OT
AS
(
    SELECT   OT.OrderID
            ,OT.table2id
            ,OT.table2id - LAG(OT.table2id, 1, 0) OVER(ORDER BY OT.OrderID) AS DeltaID
            ,COUNT(OT.table2id) OVER(PARTITION BY (SELECT 1 AS ID)) AS RecordCount
    FROM    @table2IdInOrder OT
)
SELECT      DISTINCT JT.table1id
FROM        CTE_JT JT
INNER JOIN  CTE_OT OT ON OT.table2id = JT.table2id AND OT.DeltaID = JT.DeltaID AND OT.RecordCount = JT.RecordCount;

Этот запрос проверяет, совпадает ли порядок Id и Table2Id.

WITH CTE AS (
    SELECT table1id, table2id
    ,orderIsOK = ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id) 
               - ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY table2id)   
    FROM JunctionTable 
    WHERE table2id IN (1, 2, 3)
) 
SELECT table1id 
FROM CTE
WHERE orderIsOK = 0
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3

В качестве альтернативы вы можете явно указать желаемый порядок. В приведенном ниже примере требуемый порядок указан как (2,1,3)

WITH desiredOrder(rowNumber, value) as (
    select * 
    from (
        values (1,2)
        ,(2,1)
        ,(3,3)
        ) t (rowNumber, value)
),  
CTE AS (
    SELECT table1id, table2id
    ,orderIsOK = ROW_NUMBER() OVER(PARTITION BY table1id ORDER BY id)
            - do.rowNumber
    FROM @JunctionTable t
    JOIN desiredOrder do ON t.table2id = do.value
) 
SELECT table1id 
FROM CTE
WHERE orderIsOK=0
GROUP BY table1id
HAVING COUNT(DISTINCT table2id) = 3