我有一個回傳表的查詢，并且基于值（如果存在）我想設定 row_number。我有一些解決方案，但它看起來很長，我認為處理它可能更容易，代碼更少（最佳選擇）。以下示例具有預期結果：
如果查詢回傳帶有 NULL 的客戶端：
----------------------
Process | Client|
A | NULL |
A | B |
A | B |
A | B |
A | C |
A | C |
A | C |
輸出應該是：
----------------------
Process | Client| RowNumber
A | NULL | 1
A | B | 2
A | B | 3
A | B | 4
A | C | 2
A | C | 3
A | C | 4
如果查詢回傳沒有 NULL：
----------------------
Process | Client|
A | B |
A | B |
A | B |
A | C |
A | C |
A | C |
輸出應該是：
----------------------
Process | Client| RowNumber
A | B | 1
A | B | 2
A | B | 3
A | C | 1
A | C | 2
A | C | 3
uj5u.com熱心網友回復：
我不確定是否NULL應始終被視為'B'，但您需要處理：
select t.* ,
row_number() over (partition by process, coalesce(client, 'B') order by (select null))
from t
where client is not null;
哦，我明白了，您沒有設定NULLto'B'而是將NULLs 的數量添加到其他值。這也很簡單：
select t.* ,
(row_number() over (partition by process, client order by (select null))
(case when client is null then 0
else sum(case when client is null then 1 else 0 end) over ()
end)
)
from t
where client is not null;
uj5u.com熱心網友回復：
小提琴手
DROP TABLE if exists mytable;
CREATE TABLE mytable(Process char(1), Client char(1));
INSERT INTO mytable values
('A',null),
('A','B'),
('A','B'),
('A','B'),
('A','C'),
('A','C'),
('A','C');
-- with a NULL value
select
Process,
Client,
ROW_NUMBER() OVER (partition by process,Client order by (select null)) CASE WHEN Client is null THEN 0 else 1 end R
from mytable;
-- without a NULL value
select
Process,
Client,
ROW_NUMBER() OVER (partition by process,Client order by (select null)) R
from mytable
where not client is null;
uj5u.com熱心網友回復：
…
declare @t table(process varchar(10), client varchar(10));
insert into @t(process, client)
values
('A', null),
('A', 'B'),('A', 'B'),('A', 'B'),
('A', 'C'),('A', 'C'),
('A', ''), ('A', ''), ('A', ' '), ('A', ' '),
('A', 'ZXY'), ('A', 'ZXY'),
('X', 'B'),('X', 'B'),('X', 'B'),
('X', 'C'),('X', 'C');
select *,
row_number() over(partition by process,client order by client)
--if there is a null client per process then add 1 to every non null client
case when client is not null and min(case when client is null then 0 else 1 end) over(partition by process) = 0 then 1 else 0 end
-- case when client is not null and min(isnull(ASCII(client '.'), 0)) over(partition by process) = 0 then 1 else 0 end
as rownumber
from
(
select *
from @t
--where client is not null
) as t;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/419942.html
標籤：