b******y
发帖数: 139 | 1 I have a recordset ordered by CusipCol, IDCol like this:
CusipCol IDCol ValueCol
1C 1 A
2C 2 B
3C 1 A
3C 1 Null
4C 1 A
4C 2 Null
5C 1 A
5C 2 B
6C 1 Null
6C 2 B
7C 1 Null
7C 2 Null
I need write a oracle query to get the fol | c***c
发帖数: 6234 | 2 我觉得纯query是没法做到的。因为纯query没法用if判断。
而且没法知道是第一和第二。
用stored procedure试试把，也得建立temp table或者返回3个arrays，就是CusipCol
，IDCol， ValueCol各在一个array里面
【在 b******y 的大作中提到】
: I have a recordset ordered by CusipCol, IDCol like this:
: CusipCol IDCol ValueCol
: 1C 1 A
: 2C 2 B
: 3C 1 A
: 3C 1 Null
: 4C 1 A
: 4C 2 Null
: 5C 1 A
: 5C 2 B
| c**t
发帖数: 2744 | 3 不要轻易说没办法。我觉给你一个纯query吧：
with TEMP as (
select CusipCol, IDCol, ValueCol,
row_number() over ( partition by CusipCol order by CusipCol) as RN,
count(CusipCol) over (partition by CusipCol order by CusipCol) as CNT
from test)
select CUSIPCOL, IDCOL, VALUECOL from TEMP
where CNT=1
UNION ALL
select CUSIPCOL, IDCOL, VALUECOL from TEMP WHERE CNT>1 and RN=1 and VAlueCol
is NOT NULL
UNION ALL
select A.CUSIPCOL,
decode(a.VALUECOL, null, decode(B.ValueCol, null, A.IDCOL, B.IDCol),
a.IDCOL) as IDCOL,
【在 c***c 的大作中提到】
: 我觉得纯query是没法做到的。因为纯query没法用if判断。
: 而且没法知道是第一和第二。
: 用stored procedure试试把，也得建立temp table或者返回3个arrays，就是CusipCol
: ，IDCol， ValueCol各在一个array里面
| c**t
发帖数: 2744 | 4 You may use LAG to simplify the query
VAlueCol
【在 c**t 的大作中提到】
: 不要轻易说没办法。我觉给你一个纯query吧：
: with TEMP as (
: select CusipCol, IDCol, ValueCol,
: row_number() over ( partition by CusipCol order by CusipCol) as RN,
: count(CusipCol) over (partition by CusipCol order by CusipCol) as CNT
: from test)
: select CUSIPCOL, IDCOL, VALUECOL from TEMP
: where CNT=1
: UNION ALL
: select CUSIPCOL, IDCOL, VALUECOL from TEMP WHERE CNT>1 and RN=1 and VAlueCol
| s******s
发帖数: 508 | 5 select
CusipCol
, case when ValueCol is null and nextValue is not null then nextID else
IDCol end IDCol
, case when ValueCol is null and nextValue is not null then nextValue else
ValueCol end ValueCol
from
(
select CusipCol, IDCol, ValueCol
,lead(ValueCol) over (partition by CusipCol order by IDCol) NextValue
,lead(ValueCol) over (partition by CusipCol order by IDCol) NextID
,row_number() over (partition by CusipCol order by IDCol) rownum
from mytable
) x
where rownum = | c**t
发帖数: 2744 | 6 a typo: the 2nd lead should be lead(IDCol)
NextValue
【在 s******s 的大作中提到】
: select
: CusipCol
: , case when ValueCol is null and nextValue is not null then nextID else
: IDCol end IDCol
: , case when ValueCol is null and nextValue is not null then nextValue else
: ValueCol end ValueCol
: from
: (
: select CusipCol, IDCol, ValueCol
: ,lead(ValueCol) over (partition by CusipCol order by IDCol) NextValue
| b******y
发帖数: 139 | 7 Yes, this works well.
Thumb up!
NextValue
【在 s******s 的大作中提到】
: select
: CusipCol
: , case when ValueCol is null and nextValue is not null then nextID else
: IDCol end IDCol
: , case when ValueCol is null and nextValue is not null then nextValue else
: ValueCol end ValueCol
: from
: (
: select CusipCol, IDCol, ValueCol
: ,lead(ValueCol) over (partition by CusipCol order by IDCol) NextValue
| b******y
发帖数: 139 | 8 You guys are really good.
Thanks!
VAlueCol
【在 c**t 的大作中提到】
: 不要轻易说没办法。我觉给你一个纯query吧：
: with TEMP as (
: select CusipCol, IDCol, ValueCol,
: row_number() over ( partition by CusipCol order by CusipCol) as RN,
: count(CusipCol) over (partition by CusipCol order by CusipCol) as CNT
: from test)
: select CUSIPCOL, IDCOL, VALUECOL from TEMP
: where CNT=1
: UNION ALL
: select CUSIPCOL, IDCOL, VALUECOL from TEMP WHERE CNT>1 and RN=1 and VAlueCol
| n*****2
发帖数: 35 | 9 SELECT A.CUSIPCOL, B.IDCOL, B.VALUECOL
FROM ( SELECT CUSIPCOL,
MIN (DECODE (VALUECOL, NULL, 'ZZZZZZ', VALUECOL)) AS
ND
FROM YOUR_TABLE_NAME
GROUP BY CUSIPCOL) A,
YOUR_TABLE_NAME B
WHERE A.ND = DECODE (B.VALUECOL, NULL, 'ZZZZZZ', VALUECOL)
AND A.CUSIPCOL = B.CUSIPCOL
ORDER BY A.CUSIPCOL, B.IDCOL
【在 b******y 的大作中提到】
: I have a recordset ordered by CusipCol, IDCol like this:
: CusipCol IDCol ValueCol
: 1C 1 A
: 2C 2 B
: 3C 1 A
: 3C 1 Null
: 4C 1 A
: 4C 2 Null
: 5C 1 A
: 5C 2 B
|
|
