sql - 连接后 Rownum 顺序不正确 - SQL Server

Question

http://sqlfiddle.com/#!18/97fbe/1 - fiddle

我尝试尽可能多地演示我的现实生活场景

表格:

CREATE TABLE [OrderTable] 
(
    [id] int,
    [OrderGroupID] int,
    [Total] int,
    [fkPerson] int,
    [fkitem] int

    PRIMARY KEY (id)
) 

INSERT INTO [OrderTable] (id, OrderGroupID, Total ,[fkPerson], [fkItem]) 
VALUES
  ('1', '1', '20', '1', '1'),
  ('2', '1', '45', '2', '2'),
  ('3', '2', '32', '1', '1'),
  ('4', '2', '30', '2', '2'),
  ('5', '2', '32', '1', '1'),
  ('6', '2', '32', '3', '1'),
  ('7', '2', '32', '4', '1'),
  ('8', '2', '32', '4', '1'),
  ('9', '2', '32', '5', '1');

CREATE TABLE [Person] 
(
    [id] int,
    [Name] varchar(32)

    PRIMARY KEY (id)
) 

INSERT INTO [Person] (id, Name) 
VALUES
  ('1', 'Fred'),
  ('2', 'Sam'),
  ('3', 'Ryan'),
  ('4', 'Tim'),
  ('5', 'Gary');

CREATE TABLE [Item] 
(
    [id] int,
    [ItemNo] varchar(32),
    [Price] int

    PRIMARY KEY (id)
) 

INSERT INTO [Item] (id, ItemNo, Price) 
VALUES
  ('1', '453', '23'),
  ('2', '657', '34');

查询:

WITH TABLE1 AS 
(
    SELECT 
        -- P.ID AS [PersonID], 
        -- P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson,
        [fkItem]
    FROM 
        OrderTable OT
    -- INNER JOIN Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        -- P.ID, P.Name,
        i.id, ot.fkperson, [fkItem]
)
SELECT 
    t1.[fkperson],
    P.[Name],
    t1.[itemid],
    t1.[total],
    t1.[rownum]
    -- Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson
INNER JOIN 
    Item I ON I.[id] = T1.[fkItem]

结果:

| fkperson | Name | itemid | total | rownum |
+----------+------+--------+-------+--------+
|        1 | Fred |      1 |    84 |      1 |
|        3 | Ryan |      1 |    32 |      2 |
|        4 |  Tim |      1 |    64 |      3 |
|        5 | Gary |      1 |    32 |      4 |
|        2 |  Sam |      2 |    75 |      5 |

这就是我想要的结果。然而，我现实生活中的例子是以奇怪的顺序给我行号的。我知道这是一个连接问题，因为当我评论这些连接时:

INNER JOIN 
    Person P ON P.ID = T1.fkperson
INNER JOIN 
    Item I ON I.[id] = T1.[fkItem]

效果很好。

| fkperson | Name | itemid | total | rownum |
|----------|------|--------|-------|--------|
|        1 | Fred |      1 |    84 |      4 |
|        3 | Ryan |      1 |    32 |      3 |
|        4 |  Tim |      1 |    64 |      5 |
|        5 | Gary |      1 |    32 |      1 |
|        2 |  Sam |      2 |    75 |      2 |

有没有人对连接如何导致这些奇怪的行号排序有任何建议？或者为我指出正确的方向。谢谢

Answer 1

任何关系数据库本质上都是无序的 - 并且您不会获得任何有保证的顺序除非您明确要求 - 通过ORDER BY外部查询的子句。

您需要添加ORDER BY明确地 - 像这样:

WITH TABLE1 AS 
(
    .....
)
SELECT 
    (list of columns ....)
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson
INNER JOIN 
    Item I ON I.[id] = T1.[fkItem]
ORDER BY 
    T1.rownum