当前位置： 文江博客话题详情

SQL - 选择分组列上的最小值和最大值

发布于 2025-01-13 06:44:23 字数 1614 浏览 0 评论 0原文

我想使用 SQL 查询选择每个部门的员工开始和结束日期。考虑到员工可能会回到以前的部门。

当对（不相关的）列（例如新地址、职务）进行更改时，系统会创建新的 FromDate 和 ToDate，每个员工都有几行。

我所拥有的：

EmpIDDeptCodeFromDateToDate
201402016-08-01 00:00:00.0002017-08-31 00:00:00.000
201402017-08-01 00:00:00.0002018-09-30 00:00:00.000
201802018-10-01 00:00:00.0002019-05-31 00:00:00.000
201802019-06-01 00:00:00.0002020-06-30 00:00:00.000
201802020-07-01 00:00:00.0002022-02-28 00:00:00.000
201402022-03-01 00:00:00.0009999-12-31 00:00:00.000
302102022-01-01 00:00:00.0009999-12-31 00:00:00.000

我想要实现的是

EmpIDDeptCodeFromDateToDate
201402016-08-01 00:00:00.0002018-09-30 00:00:00.000
201802018-10-01 00:00:00.0002022-02-28 00:00:00.000
201402022-03-01 00:00:00.0009999-12-31 00:00:00.000
302102022-01-01 00:00:00.0009999-12-31 00:00:00.000
原文

I want to select the employees' start and end dates for each department using a SQL query. Taking into mind that an employee might return to a previous department.

As the system creates a new FromDate and ToDate when changes are created to (irrelevant) columns such as a new address, title each employee has several rows.

What I have:

EmpIDDeptCodeFromDateToDate
201402016-08-01 00:00:00.0002017-08-31 00:00:00.000
201402017-08-01 00:00:00.0002018-09-30 00:00:00.000
201802018-10-01 00:00:00.0002019-05-31 00:00:00.000
201802019-06-01 00:00:00.0002020-06-30 00:00:00.000
201802020-07-01 00:00:00.0002022-02-28 00:00:00.000
201402022-03-01 00:00:00.0009999-12-31 00:00:00.000
302102022-01-01 00:00:00.0009999-12-31 00:00:00.000

What I want to achieve is

EmpIDDeptCodeFromDateToDate
201402016-08-01 00:00:00.0002018-09-30 00:00:00.000
201802018-10-01 00:00:00.0002022-02-28 00:00:00.000
201402022-03-01 00:00:00.0009999-12-31 00:00:00.000
302102022-01-01 00:00:00.0009999-12-31 00:00:00.000

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

扫码二维码加入Web技术交流群

发布评论

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

评论（1）

ゝ偶尔ゞ 2025-01-20 06:44:23

为此，我们需要创建一些可以为我们的组使用的东西（答案末尾的 DBFiddle）

看看这个示例，

select t.*,
       row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) as seqnum_na,
       row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate) as seqnum_n
from   codes t

结果是

EmpIDDeptCodeFromDateToDateseqnum_naseqnum_n
201402016-08-01 00:00： 00.0002017-08-31 00:00:00.00011
201402017-08-01 00:00:00.0002018-09-30 00:00:00.00022
201802018-10-01 00:00:00.0002019-05-31 00:00:00.00013
201802019-06-01 00:00:00.0002020-06-30 00:00:00.00024
201802020-07-01 00:00:00.0002022-02-28 00:00:00.00035
201402022-03-01 00:00:00.0009999-12-31 00:00:00.00036
302102022-01-01 00:00:00.0009999-12-31 00:00:00.00011

现在让我们看看最后两列，
假设您将它们彼此相减，您将得到

EmpIDDeptCodeseqnum_naseqnum_n按值计算的分组
20140110
20140220
20180132
20180242
20180352
20140363
第302章10110

现在这个“计算的分组依据值”，我们可以用于我们的实际分组依据，

select i.EmpID, i.DeptCode, min(i.FromDate) as FromDate, max(i.ToDate) as ToDate
from (select t.*,
             row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) as seqnum_na,
             row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate) as seqnum_n
      from   codes t
     ) i
group by (seqnum_n - seqnum_na), EmpID, DeptCode
order by i.EmpID, min(i.FromDate), i.DeptCode

这将为您提供这个

EmpIDDeptCodeFromDateToDate
201402016-08-01 00:00:00.0002018-09-30 00:00 :00.000
201802018-10-01 00:00:00.0002022-02-28 00:00:00.000
201402022-03-01 00:00:00.0009999-12-31 00:00:00.000
302102022-01-01 00:00:00.0009999-12-31 00:00:00.000

您可以在此找到完整的代码和结果 DBFiddle

To do this, we need to create something that we can user for our group by (DBFiddle at the end of the answer)

Look at this sample

select t.*,
       row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) as seqnum_na,
       row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate) as seqnum_n
from   codes t

the result is

EmpIDDeptCodeFromDateToDateseqnum_naseqnum_n
201402016-08-01 00:00:00.0002017-08-31 00:00:00.00011
201402017-08-01 00:00:00.0002018-09-30 00:00:00.00022
201802018-10-01 00:00:00.0002019-05-31 00:00:00.00013
201802019-06-01 00:00:00.0002020-06-30 00:00:00.00024
201802020-07-01 00:00:00.0002022-02-28 00:00:00.00035
201402022-03-01 00:00:00.0009999-12-31 00:00:00.00036
302102022-01-01 00:00:00.0009999-12-31 00:00:00.00011

Now let's look at the last 2 columns,
suppose you would subtract them from each other, you would get

EmpIDDeptCodeseqnum_naseqnum_ncalculated group by value
20140110
20140220
20180132
20180242
20180352
20140363
30210110

Now this "calculated group by value", we can use for our actual group by

select i.EmpID, i.DeptCode, min(i.FromDate) as FromDate, max(i.ToDate) as ToDate
from (select t.*,
             row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) as seqnum_na,
             row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate) as seqnum_n
      from   codes t
     ) i
group by (seqnum_n - seqnum_na), EmpID, DeptCode
order by i.EmpID, min(i.FromDate), i.DeptCode

Which will get you this

EmpIDDeptCodeFromDateToDate
201402016-08-01 00:00:00.0002018-09-30 00:00:00.000
201802018-10-01 00:00:00.0002022-02-28 00:00:00.000
201402022-03-01 00:00:00.0009999-12-31 00:00:00.000
302102022-01-01 00:00:00.0009999-12-31 00:00:00.000

The full code and results you can find in this DBFiddle

回复 原文
~没有更多了~

关于作者

花开柳相依

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

雪花的坚持

文章 0 评论 0

温柔一刀

文章 0 评论 0

扛起拖把扫天下

文章 0 评论 0

北方的韩爷

文章 0 评论 0

绝對不後悔。

文章 0 评论 0

青衫负雪

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文