主页 / dba / 问题 / 42841
Accepted
Taryn
Asked: 2013-05-23 13:05:28 +0800 CST

如何让 row_number 具有 dense_rank 的行为

  • 772

我有一个存储过程将用于翻阅数据。该过程的要求之一是要有一个参数，用于对两列数据进行排序，在应用row_number().

一些示例数据：

CREATE TABLE grp 
(
  [grp_id] uniqueidentifier primary key, 
  [grp_nm] varchar(5) not null, 
  [grp_owner] varchar(200) not null
);

INSERT INTO grp ([grp_id], [grp_nm], [grp_owner])
VALUES
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 'test1', 'me'),
    ('1F52A713-EFAC-E211-9C26-78E7D18E1E84', 'test2', 'me'),
    ('D123B48A-63AB-E211-9C26-78E7D18E1E84', 'test3', 'me'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 'test4', 'me'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 'test5', 'me'),
    ('D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84', 'test6', 'me'),
    ('9A07EA21-1AAD-E211-9C26-78E7D18E1E84', 'test7', 'me');


CREATE TABLE mbr
(
  [grp_id] uniqueidentifier, 
  [mbr_id] int not null primary key, 
  [acct_id] varchar(7) not null, 
  [cst] varchar(4) null
);

INSERT INTO mbr ([grp_id], [mbr_id], [acct_id], [cst])
VALUES
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 10, '1', 'AA'),
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 11, '2', 'BB'),
    ('1F52A713-EFAC-E211-9C26-78E7D18E1E84', 12, '1234578', 'blah'),
    ('D123B48A-63AB-E211-9C26-78E7D18E1E84', 13, '78', 'test'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 14, 'x', 'mbr1'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 15, 'a', 'mbr2'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 16, 'b', 'mbr1'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 17, 'c', 'mbr2'),
    ('D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84', 18, 'a', 'mbr1'),
    ('9A07EA21-1AAD-E211-9C26-78E7D18E1E84', 19, 'a', 'mbr1');

该程序和排序目前正在按书面进行。

但是我们应用row_number(). row_number()应该几乎模拟发生的排名，但dense_rank由于使用参数排序，它不能按预期工作。

例如，如果我运行以下查询：

declare @sort_desc bit = 0

select g.grp_id, g.grp_nm,
    m.mbr_id, m.acct_id, m.cst,
    row_number() over(order by case when @sort_desc = 0 then g.grp_nm end
                              , case when @sort_desc = 0 then m.acct_id end
                              , case when @sort_desc = 1 then g.grp_nm end desc
                              , case when @sort_desc = 1 then m.acct_id end desc) rn,
    dense_rank()  over(order by case when @sort_desc = 0 then g.grp_nm end
                              , case when @sort_desc = 0 then m.acct_id end
                              , case when @sort_desc = 1 then g.grp_nm end desc
                              , case when @sort_desc = 1 then m.acct_id end desc) dr
from grp g
inner join mbr m
  on g.grp_id = m.grp_id;

请参阅带演示的 SQL Fiddle（还提供了一个精简的存储过程）

我得到以下结果：

|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | DR |
-------------------------------------------------------------------------------------
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1 |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  2 |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  3 |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  4 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  5 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  6 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  7 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  8 |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  9 |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 | 10 |

但想要的结果是：

|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | expR|
-------------------------------------------------------------------------------------
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1  |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  1  |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  2  |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  3  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  4  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  4  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  5  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  5  |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  6  |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 |  7  |

正如您所看到的，expR列值正在递增，grp_id但行仍处于正确的排序顺序中。我不知道如何得到这个结果，任何建议都会很好。

sql-server window-functions

1 个回答

  1. Best Answer

    这是我在不挤压我的大脑造成灾难性后果的情况下所能得到的最简洁的内容。SQL小提琴。

    DECLARE @sort_desc BIT = 0;

;WITH x AS
(
  SELECT g.grp_id, g.grp_nm, m.mbr_id, m.acct_id, m.cst,
    o1 = ROW_NUMBER() OVER (ORDER BY 
         CASE @sort_desc WHEN 0 THEN g.grp_nm END,
         CASE @sort_desc WHEN 0 THEN m.acct_id END,
         g.grp_nm DESC, m.acct_id DESC)
  FROM dbo.grp AS g
  INNER JOIN dbo.mbr AS m
  ON g.grp_id = m.grp_id
), 
y AS
(
  SELECT *, o2 = ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY o1)
  FROM x
)
SELECT *, expected = DENSE_RANK() OVER (ORDER BY o1-o2)
FROM y
ORDER BY o1;

    您当然希望推断出对*您需要继续并最终输出的实际列的所有引用。我很懒，但照我说的去做，而不是像我做的那样。:-)

    • 7

相关问题