获得的连续数为每个分区在查询
问题描述:
我有此表(样品数据):获得的连续数为每个分区在查询
CREATE TABLE A
([Idx] int, [Title] varchar(50), [Sort] int)
;
INSERT INTO A
([Idx], [Title], [Sort])
VALUES
(6, 'b', 1),
(6, 'b', 2),
(6, 'b', 3),
(4, 'a', 4),
(4, 'a', 5),
(4, 'a', 6),
(7, 'c', 7),
(7, 'c', 8),
(7, 'c', 9)
;
我的查询:
SELECT *
FROM A
ORDER BY Sort
结果:
| Idx | Title | Sort |
|-----|-------|------|
| 6 | b | 1 |
| 6 | b | 2 |
| 6 | b | 3 |
| 4 | a | 4 |
| 4 | a | 5 |
| 4 | a | 6 |
| 7 | c | 7 |
| 7 | c | 8 |
| 7 | c | 9 |
我想要为查询中的每个分区获得从1开始的连续数字。 (分区是通过Idx
)
预期结果:
| Idx | Title | Sort | N |
|-----|-------|------|---|
| 6 | b | 1 | 1 |
| 6 | b | 2 | 1 |
| 6 | b | 3 | 1 |
| 4 | a | 4 | 2 |
| 4 | a | 5 | 2 |
| 4 | a | 6 | 2 |
| 7 | c | 7 | 3 |
| 7 | c | 8 | 3 |
| 7 | c | 9 | 3 |
优选使用? OVER (PARTITION BY Idx ORDER BY Sort)
如果可能的话。
我还必须保持Sort
的顺序!
答
我想,如果你不想使用DENSE_RANK(),你可以这样做以下:
select A.*, B.GRP from A
INNER JOIN (
select row_number() over(order by srt) GRP,idx
FROM (select idx,MIN(sort) srt from A group by idx) t1) B
on A.Idx = B.idx
order by sort
答
你可以使用DENSE_RANK()
功能
SELECT *, DENSE_RANK() OVER (ORDER BY Title) N FROM <table_name>
结果:
Idx Title Sort N
6 a 1 1
6 a 2 1
6 a 3 1
4 b 4 2
4 b 5 2
4 b 6 2
7 c 7 3
7 c 8 3
7 c 9 3
答
;with cte
as
(
select *, row_number()over (order by idx) as rwnum,DENSE_RANK() over (Order by title) as rank
from #a
)
,cte3
as
(
select c.Idx as idx1,c.Title as title1,c.sort as sort1,c.rwnum,c.rank ,a.Idx ,a.Sort,a.Title
from
cte as c
join #A as a on c.rwnum=a.sort
)
select idx,Title,Sort,rank from cte3
我不能使用DENSE_RANK()b/c它给出了错误的结果。 – zig