SQL在动态值上排名

发布于 2025-02-13 06:55:48 字数 839 浏览 0 评论 0原文

问题语句

创建表代码

CREATE TABLE dbo.RankingTest

(
    DeptNo varchar(500),
    DeptName varchar (1000)
)

插入值代码

insert into dbo.RankingTest

SELECT 1, 'Sales'
union all
SELECT 2, 'Sales Internal'
union all
SELECT 3, 'Sales External'
union all
SELECT 4, 'Sales External IND'
union all
SELECT 5, 'Purchase'
union all
SELECT 6, 'Purchase Internal'
union all
SELECT 7, 'Purchase External'
union all
SELECT 8, 'Purchase External APC'
union all
SELECT 9, 'HR'
union all
SELECT 10, 'HR Internal'
union all
SELECT 11, 'HR External'
union all
SELECT 12, 'HR External ASA'

表值与新列：

dense_rank带有分区，但无法获得所需的结果集。结果集不仅要等到4，这在实际数据方案中是动态的。此处提供的数据仅用于解释问题声明。

任何帮助都将受到赞赏

原文

Problem statement

Create Table Code

CREATE TABLE dbo.RankingTest

(
    DeptNo varchar(500),
    DeptName varchar (1000)
)

Insert Values Code

insert into dbo.RankingTest

SELECT 1, 'Sales'
union all
SELECT 2, 'Sales Internal'
union all
SELECT 3, 'Sales External'
union all
SELECT 4, 'Sales External IND'
union all
SELECT 5, 'Purchase'
union all
SELECT 6, 'Purchase Internal'
union all
SELECT 7, 'Purchase External'
union all
SELECT 8, 'Purchase External APC'
union all
SELECT 9, 'HR'
union all
SELECT 10, 'HR Internal'
union all
SELECT 11, 'HR External'
union all
SELECT 12, 'HR External ASA'

Table values with new column :

Tried with ROW_NUMBER, RANK, DENSE_RANK with partition by but not getting the desired result-set.
The result set is not only till 4, this is dynamic in real data scenario.
The data provided here is only for explaining the problem statement.

Any help is appreciated

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一杆小烟枪 2025-02-20 06:55:48

可以肯定的是，您希望编号在DEPTNAME列中使用“第一个”单词重新启动。理想情况下，这应该是表中的另一列，但是您可以为此利用一些字符串操纵。不知道为什么您的DEPTNO是示例数据中的VARCHAR。但是以下适用于您的示例数据。

select *
    , RankValues = ROW_NUMBER()over(partition by left(rt.DeptName, CHARINDEX(' ', rt.DeptName + ' ') - 1) order by rt.DeptName)
from RankingTest rt
order by try_convert(int, DeptNo)

Pretty sure that you are wanting the numbering to restart with the "first" word in the DeptName column. This should ideally be another column in your table instead but you could leverage some string manipulation for this. Not sure why your DeptNo is a varchar in your sample data. But the following works for your sample data.

select *
    , RankValues = ROW_NUMBER()over(partition by left(rt.DeptName, CHARINDEX(' ', rt.DeptName + ' ') - 1) order by rt.DeptName)
from RankingTest rt
order by try_convert(int, DeptNo)

回复收藏 0 原文

~没有更多了~