如何动态地将所有客户id传递给子查询的WHERE IN子句

Question

我试图通过传递所有customer_id到子查询的WHERE IN子句来获取所有列出的客户的第2 MAX END_DATE。现在我的静态customer_id在WHERE in子句中给了我想要的输出，但其余的customer_id显示为NULL。

如何动态地传递所有customer_id而不是在子查询的WHERE in中的静态值。非常感谢您的帮助。

SELECT d.customer_id
, ( SELECT DISTINCT g.end_date
FROM contract g
WHERE g.end_date = (SELECT MAX(g.end_date) FROM contract g WHERE g.customer_id IN ('64','65','69') 
AND g.customer_id = d.customer_id 
AND g.end_date<(SELECT MAX(g.end_date) FROM contract g WHERE g.customer_id IN ('64','65','69') 
AND g.customer_id = d.customer_id)
)) END_DATE
FROM customer_vw d
GROUP BY d.customer_id;

Answer 1

也许你把它弄得太复杂了;在这种情况下，分析函数可能会有所帮助。下面是一个示例—我想从每个部门的表格中选择第二高的招聘人员:

SQL> break on deptno
SQL> select deptno, hiredate from emp order by deptno, hiredate desc;
DEPTNO HIREDATE
---------- ----------
10 23.01.1982
17.11.1981    --> this, for department 10
09.06.1981
20 12.01.1983
09.12.1982    -->
03.12.1981
02.04.1981
17.12.1980
30 03.12.1981
28.09.1981    -->
08.09.1981
01.05.1981
22.02.1981
20.02.1981
14 rows selected.

:

SQL> with temp as
2    (select deptno, hiredate,
3       rank() over (partition by deptno order by hiredate desc) rnk
4     from emp
5    )
6  select deptno, hiredate
7  from temp
8  where rnk = 2;
DEPTNO HIREDATE
---------- ----------
10 17.11.1981
20 09.12.1982
30 28.09.1981
SQL>

---

作为"动态传递的值列表"，在Oracle中(当您使用Oracle SQL Developer时，我(可能错误地)假设您实际使用该数据库)，您将拆分该值列表成行(这就是行#5 - 8中的子查询)并在IN子句中使用它:

SQL> with temp as
2    (select deptno, hiredate,
3       rank() over (partition by deptno order by hiredate desc) rnk
4     from emp
5     where deptno in (select regexp_substr('&&par_deptno', '[^,]+', 1, level)
6                      from dual
7                      connect by level <= regexp_count('&&par_deptno', ',') + 1
8                     )
9    )
10  select deptno, hiredate
11  from temp
12  where rnk = 2;
Enter value for par_deptno: 10,30
DEPTNO HIREDATE
---------- ----------
10 17.11.1981
30 28.09.1981
SQL>

在SQL Developer中，您将'&&par_deptno'替换为:par_deptno(即更改绑定变量的替换)。

Answer 2

您可以使用LIKE与逗号分隔的数字字符串和DENSE_RANK分析函数进行比较，以找到第二高的值:

SELECT d.customer_id,
( SELECT end_date
FROM   (
SELECT end_date,
DENSE_RANK() OVER (ORDER BY end_date DESC) AS rnk
FROM   contract g
WHERE  ',' || :your_list_of_customers || ',' LIKE '%,' || customer_id || ',%'
AND    g.customer_id = d.customer_id
)
WHERE rnk = 2
AND   ROWNUM = 1
) AS END_DATE
FROM   customer_vw d
GROUP BY
d.customer_id;

注意:不要使用RANK，因为如果有两个(或更多)行并列第一，那么RANK将返回第1和第3的排名1,1和3，并且不会有第二个位置。在前面的例子中，DENSE_RANK通过返回1,1,2解决了这个问题。