仅获取已分配的最新部门的 SQL 语句

Question

在我的数据库中，我有以下行和列：https : //i.imgur.com/ktUZY9d.jpg

我的问题是同一个员工有 3 个不同的部门，但他目前只活跃在 1 个部门。如何更改此 SQL 语句以仅包含他所在的最新部门，从 2018 年开始到 2100 年结束，如ALLOCATION_START 和 ALLOCATION_END？

询问

string agentIdSubQuery = "SELECT DISTINCT " +

                                     "AGENT_ID " +

                                 "FROM " +

                                     "KS_DRIFT.V_AGENT_ALLOCATION " +

                                 "WHERE " +

                                     "LENGTH(AGENT_INITIALS) < 5";

        if(queryParams.SnapshotDate.HasValue)

            agentIdSubQuery += " AND " + OracleConversion.ToOracleDate(queryParams.SnapshotDate) + " BETWEEN ALLOCATION_START AND ALLOCATION_END";

更新：

尝试了很多不同的解决方案，但每次都崩溃，当我运行调试器时，在这个方法中进一步向下这个查询导致我崩溃：

 string sql = "SELECT " +

                         "age1.* " +

                     "FROM " +

                         "KS_DRIFT.V_AGENT_ALLOCATION age1 " +

                     "INNER JOIN " +

                         "(" + agentIdSubQuery + ") age2 ON age1.AGENT_ID = age2.AGENT_ID " +

                     "ORDER BY " +

                         "AGENT_INITIALS";

错误信息：

{"在执行 SQL 查询时发生错误：SELECT age1.* FROM KS_DRIFT.V_AGENT_ALLOCATION age1 INNER JOIN (SELECT max(DISTINCT AGENT_ID FROM KS_DRIFT.V_AGENT_ALLOCATION WHERE LENGTH(AGENT_INITIALS) < 5 AND '2015'ALLOCETATION_START- AND (UPPER(AGENT_INITIALS) = 'JKKA')) age2 ON age1.AGENT_ID = age2.AGENT_ID ORDER BY AGENT_INITIALS。"}

也给了我一个内心的例外：

{"ORA-00904: \"AGE2\".\"AGENT_ID\": ugyldig identifikator"}

Answer 1

按最新开始日期（降序）排序并选择前 1！string agentIdSubQuery =&nbsp; &nbsp; "AGENT_ID " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"FROM " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"KS_DRIFT.V_AGENT_ALLOCATION " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"WHERE " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"LENGTH(AGENT_INITIALS) < 5 " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" AND ROWNUM = 1 " +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;" ORDER BY ALLOCATION_START DESC";编辑，将 Top 1 更改为 Rownum = 1，对于 Oracle 语法

Answer 2

您的错误消息显示了最终生成的 SQL：{"在执行 SQL 查询时发生错误：SELECT age1.* FROM KS_DRIFT.V_AGENT_ALLOCATION age1 INNER JOIN (SELECT max(DISTINCT AGENT_ID FROM KS_DRIFT.V_AGENT_ALLOCATION WHERE LENGTH(AGENT_INITIALS) < 5 AND '2018-ALLOCATION_END1 AND '2018-ALLOCATION_END1 INNER JOIN) AND (UPPER(AGENT_INITIALS) = 'JKKA')) age2 ON age1.AGENT_ID = age2.AGENT_ID ORDER BY AGENT_INITIALS。"}如果将其格式化以使其可读，则会得到：select age1.*from&nbsp; &nbsp;ks_drift.v_agent_allocation age1&nbsp; &nbsp; &nbsp; &nbsp;inner join&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;( select max(distinct agent_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from&nbsp; &nbsp;ks_drift.v_agent_allocation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where&nbsp; length(agent_initials) < 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and&nbsp; &nbsp; '2018-08-15' between allocation_start and allocation_end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;and&nbsp; &nbsp; (upper(agent_initials) = 'JKKA') ) age2&nbsp; &nbsp; &nbsp; &nbsp;on age1.agent_id = age2.agent_idorder by agent_initials应该跳出两个语法问题：后面缺少一个右括号max(distinct agent_id（distinct也是多余的）日期文字缺少它的date关键字——它应该是date '2018-08-15'（或者更好的是，一个绑定变量）。周围的括号(upper(agent_initials) = 'JKKA')是多余的，但也许它们来自您的生成器逻辑，最容易保留它们。不过，我不确定这与您的“最新分配部门”要求有何关系。一些示例数据（不是屏幕截图）会有所帮助。