代码之家 › 专栏 › 技术社区 › Ryan Gadsdon

join后的Rownum顺序不正确-SQL Server

rownum join sql-server sql

Ryan Gadsdon · 技术社区 · 6 年前

桌子:

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 |

2 回复 | 直到 6 年前

marc_s 6 年前

任何关系数据库都是 -你不会得到任何保证的订单除非你明确要求-通过 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

Gordon Linoff 6 年前

您正在使用 order by (select null) . 这意味着 不定序 . 并且顺序可以从一个查询调用更改为另一个查询调用。

您不应该依赖于默认顺序,即使是通过外部命令 order by 明确地 在 订货人