求一个query

显示全部楼层 · 2010-3-1 11:04:59

要找出ID对应的最大的DT，规则是：如果所有的IND 都是S，就找出每个ID中最大的DT，如果任意一个ID 有IND 是 N，那么，所有ID 都取 ID 有IND 是 N当中 IND ＝ S 的最大的DT。
每次只可能一个ID 有IND ＝N。而且， N 一定出现在最大的DT 中。
下面的例子中，因为ID ＝１的数据中有IND ＝ N，而这个ID 中IND＝S 最大的DT 是２０，所以所有的DT 都是２０
WITH t
AS (SELECT 1 ID, 10 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 1 ID, 20 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 1 ID, 30 DT, 'N' IND FROM DUAL
UNION ALL
SELECT 2 ID, 10 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 2 ID, 20 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 2 ID, 30 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 3 ID, 30 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 3 ID, 40 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 3 ID, 50 DT, 'S' IND FROM DUAL)
SELECT ID, DT, IND
FROM T;
ID
DT
IND
1
10
S
1
20
S
1
30
N
2
10
S
2
20
S
2
30
S
3
30
S
3
40
S
3
50
S
上面的数据因为有那条 IND＝N 的记录，所以所有的DT都是２０
1

千问 · 2010-3-1 11:04:59

没N,有一个N,有多个N ， 3个例子：
SQL> with t
2 as (select 1 id, 10 dt, 'S' ind from dual
3 union all
4 select 1 id, 20 dt, 'S' ind from dual
5 union all
6 select 1 id, 30 dt, 'S' ind from dual
7 union all
8 select 2 id, 10 dt, 'S' ind from dual
9 union all
10 select 2 id, 20 dt, 'S' ind from dual
11 union all
12 select 2 id, 30 dt, 'S' ind from dual
13 union all
14 select 3 id, 30 dt, 'S' ind from dual
15 union all
16 select 3 id, 40 dt, 'S' ind from dual
17 union all
18 select 3 id, 50 dt, 'S' ind from dual)
19select a.id,b.max_dt max_dt
20from (
21select id
22from t
23 where exists (select 'x'
24
from t
25
where ind = 'N')
26 group by t.id) a,
27(select max(lag_dt) max_dt
28
from (
29
select id, dt, ind,lag(dt) over(partition by id order by dt) lag_dt
30
from t
31
where id in (select id
32
from t
33
where ind = 'N')
34
)
35 ) b
36union all
37select id, max(dt) max_dt
38from t
39 where not exists (select 'x'
40
from t
41
where ind = 'N')
42group by id
43order by id
44/
ID MAX_DT
---------- ----------
1 30
2 30
3 50
SQL>
SQL>
SQL>
SQL>
SQL> with t
2 as (select 1 id, 10 dt, 'S' ind from dual
3 union all
4 select 1 id, 20 dt, 'S' ind from dual
5 union all
6 select 1 id, 30 dt, 'N' ind from dual
7 union all
8 select 2 id, 10 dt, 'S' ind from dual
9 union all
10 select 2 id, 20 dt, 'S' ind from dual
11 union all
12 select 2 id, 30 dt, 'S' ind from dual
13 union all
14 select 3 id, 30 dt, 'S' ind from dual
15 union all
16 select 3 id, 40 dt, 'S' ind from dual
17 union all
18 select 3 id, 50 dt, 'S' ind from dual)
19select a.id,b.max_dt max_dt
20from (
21select id
22from t
23 where exists (select 'x'
24
from t
25
where ind = 'N')
26 group by t.id) a,
27(select max(lag_dt) max_dt
28
from (
29
select id, dt, ind,lag(dt) over(partition by id order by dt) lag_dt
30
from t
31
where id in (select id
32
from t
33
where ind = 'N')
34
)
35 ) b
36union all
37select id, max(dt) max_dt
38from t
39 where not exists (select 'x'
40
from t
41
where ind = 'N')
42group by id
43order by id
44/
ID MAX_DT
---------- ----------
1 20
2 20
3 20
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> with t
2 as (select 1 id, 10 dt, 'S' ind from dual
3 union all
4 select 1 id, 20 dt, 'S' ind from dual
5 union all
6 select 1 id, 30 dt, 'N' ind from dual
7 union all
8 select 2 id, 10 dt, 'S' ind from dual
9 union all
10 select 2 id, 20 dt, 'S' ind from dual
11 union all
12 select 2 id, 30 dt, 'S' ind from dual
13 union all
14 select 3 id, 30 dt, 'S' ind from dual
15 union all
16 select 3 id, 40 dt, 'S' ind from dual
17 union all
18 select 3 id, 50 dt, 'N' ind from dual)
19select a.id,b.max_dt max_dt
20from (
21select id
22from t
23 where exists (select 'x'
24
from t
25
where ind = 'N')
26 group by t.id) a,
27(select max(lag_dt) max_dt
28
from (
29
select id, dt, ind,lag(dt) over(partition by id order by dt) lag_dt
30
from t
31
where id in (select id
32
from t
33
where ind = 'N')
34
)
35 ) b
36union all
37select id, max(dt) max_dt
38from t
39 where not exists (select 'x'
40
from t
41
where ind = 'N')
42group by id
43order by id
44/
ID MAX_DT
---------- ----------
1 40
2 40
3 40
SQL>

千问 · 2010-3-1 11:04:59

WITH t
AS (SELECT 1 ID, 10 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 1 ID, 20 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 1 ID, 30 DT, 'N' IND FROM DUAL
UNION ALL
SELECT 2 ID, 10 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 2 ID, 20 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 2 ID, 30 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 3 ID, 30 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 3 ID, 40 DT, 'S' IND FROM DUAL
UNION ALL
SELECT 3 ID, 50 DT, 'S' IND FROM DUAL)
SELECT ID
,NVL(MAX(CASE WHEN MAX(CASE WHEN IND='N' THEN IND END)='N' THEN MAX(CASE WHEN IND='S' THEN DT END) END) OVER()

,MAX(DT)

) as DT
FROM T
GROUP BY ID;

千问 · 2010-3-1 11:04:59

newkid 发表于 2019-8-9 10:52
WITH t AS (SELECT 1 ID, 10 DT, 'S' IND FROM DUAL UNION ALL SELECT 1 ID, 20 DT, ' ...
知道你出手就不一样。厉害！👍👍👍

千问 · 2010-3-1 11:04:59

solomon_007 发表于 2019-8-9 00:07
没N,有一个N,有多个N ， 3个例子：SQL> with t2 as (select 1 id, 10 dt, 'S' ind from dual3...
谢谢

千问 · 2010-3-1 11:04:59

newkid 发表于 2019-8-9 23:52
WITH t AS (SELECT 1 ID, 10 DT, 'S' IND FROM DUAL UNION ALL SELECT 1 ID, 20 DT, ' ...
真优雅！