在保持ID顺序的同时对子类别进行排序
在保持ID顺序的同时对子类别进行排序
提问于 2019-12-25 16:30:06
我有下表：
+----+------+------+
| id | cat1 | cat2 |
+----+------+------+
| 1 | A | foo |
| 2 | A | foo |
| 3 | A | bar |
| 4 | B | sci |
| 5 | B | ble |
| 6 | B | ble |
+----+------+------+我想对子类别(cat2)进行排序。
预期结果：
+----+------+------+------+
| id | cat1 | cat2 | res |
+----+------+------+------+
| 1 | A | foo | 1 |
| 2 | A | foo | 1 |
| 3 | A | bar | 2 |
| 4 | B | sci | 1 |
| 5 | B | ble | 2 |
| 6 | B | ble | 2 |
+----+------+------+------+我使用DENSE_RANK和分区BY来获得以下结果：
+----+------+------+------+
| id | cat1 | cat2 | res |
+----+------+------+------+
| 1 | A | foo | 2 |
| 2 | A | foo | 2 |
| 3 | A | bar | 1 |
| 4 | B | sci | 2 |
| 5 | B | ble | 1 |
| 6 | B | ble | 1 |
+----+------+------+------+声明：
SELECT DENSE_RANK() OVER (PARTITION BY cat1 ORDER BY cat2 asc) as res, t.*
FROM mytable t
ORDER BY id;正如你所看到的，我唯一缺少的是结果的顺序。目前，排名是基于cat2的字母顺序。但是，我希望保留id的顺序(参见期望的结果)。简单地按我的DENSE_RANK更改订单是行不通的。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-12-25 16:54:16
WITH cte AS ( SELECT MIN(id) id, cat1, cat2
FROM test
GROUP BY cat1, cat2 )
SELECT t1.id,
t1.cat1,
t1.cat2,
DENSE_RANK() OVER (PARTITION BY t1.cat1 ORDER BY t2.id asc) as res
FROM test t1
JOIN cte t2 USING (cat1, cat2)
ORDER BY t1.id;当然，CTE可以转换为子查询。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59480152
复制相关文章
相似问题
腾讯云开发者
