表结构
a b c
1 2 3
1 2 4
1 2 8
2 3 6
2 3 7
4 5 1
6 7 2
7 8 1
7 8 2
select a , b, c, row_number() over(order by a , b) colc from A在公司的结果是:
a b c colc
1 2 3 1
1 2 4 2
1 2 8 3
2 3 6 1
2 3 7 2
4 5 1 1
6 7 2 1
7 8 1 1
7 8 2 2
在家的结果是:a b c colc
1 2 3 1
1 2 4 2
1 2 8 3
2 3 6 4
2 3 7 5
4 5 1 6
6 7 2 7
7 8 1 8
7 8 2 9为什么啊???
a b c
1 2 3
1 2 4
1 2 8
2 3 6
2 3 7
4 5 1
6 7 2
7 8 1
7 8 2
select a , b, c, row_number() over(order by a , b) colc from A在公司的结果是:
a b c colc
1 2 3 1
1 2 4 2
1 2 8 3
2 3 6 1
2 3 7 2
4 5 1 1
6 7 2 1
7 8 1 1
7 8 2 2
在家的结果是:a b c colc
1 2 3 1
1 2 4 2
1 2 8 3
2 3 6 4
2 3 7 5
4 5 1 6
6 7 2 7
7 8 1 8
7 8 2 9为什么啊???
这样写吧
from
(
select TJISSEKI.NYUKEN_YMD,TJISSEKI.LOT_NO,TJISSEKI.GOUHI_KBN,
TJISSEKI.HANTEI_YMD,TJISSEKI.NONYU_SU,TJISSEKI.SAMPLE_YMD,TJISSEKITOKUSAI.TOKUSAI_NO
from
(
select * from
(
select *
from T_JISSEKI
where HINMEI='*L51399-OJ' and MAKER_CD='HAC3' and KYOTENKANRI_CD='A202' and
(GOUHI_KBN=1 or GOUHI_KBN=2) and DATA_STATUS=3
order by NYUKEN_YMD desc,SORTORDER_NYUKEN desc
)T_JISSEKI where rownum < 51
)TJISSEKI
left join (select NYUKEN_ID , NYUKEN_SEQ , max(substr(sys_connect_by_path( TOKUSAI_NO, ','), 2)) TOKUSAI_NO
from
(
select NYUKEN_ID , NYUKEN_SEQ, TOKUSAI_NO, colc, lead(colc) over(partition by NYUKEN_ID , NYUKEN_SEQ order by colc) cold
from ( select NYUKEN_ID , NYUKEN_SEQ, TOKUSAI_NO , row_number() over(order by NYUKEN_ID , NYUKEN_SEQ) colc
from T_JISSEKI_TOKUSAI )
)
start with cold is null
connect by prior colc=cold
group by NYUKEN_ID , NYUKEN_SEQ
) TJISSEKITOKUSAIon TJISSEKI.NYUKEN_ID=TJISSEKITOKUSAI.NYUKEN_ID
and TJISSEKI. NYUKEN_SEQ=TJISSEKITOKUSAI.NYUKEN_SEQ) T_JISSEKI_LF_T_JISSEKI_TOKUSAI
left join
(
select T_TSUCHISYO_LOT.NYUKEN_ID,T_TSUCHISYO_LOT.NYUKEN_SEQ,
T_TSUCHISYO_LOT.TSUCHISYO_NO,T_TSUCHISYO.TSUCHISYO_NO,
T_TSUCHISYO.Priority
from T_TSUCHISYO_LOT,T_TSUCHISYO
where T_TSUCHISYO_LOT.TSUCHISYO_NO=T_TSUCHISYO.TSUCHISYO_NO) T_TSUCHISYO_LOT-T_TSUCHISYO on T_TSUCHISYO_LOT-T_TSUCHISYO.NYUKEN_ID=T_JISSEKI_LF_T_JISSEKI_TOKUSAI.NYUKEN_ID
and T_TSUCHISYO_LOT-T_TSUCHISYO.NYUKEN_SEQ=JISSEKI_LF_T_JISSEKI_TOKUSAI.NYUKEN_SEQ红色部分都是测试正确的,在进行左连接的时候,报 ora-00918 列未定义 错误????
是什么原因,没搞清楚?????????????
from
(
select TJISSEKI.NYUKEN_YMD,TJISSEKI.LOT_NO,TJISSEKI.GOUHI_KBN,
TJISSEKI.HANTEI_YMD,TJISSEKI.NONYU_SU,TJISSEKI.SAMPLE_YMD,TJISSEKITOKUSAI.TOKUSAI_NO
from
(
select * from
(
select *
from T_JISSEKI
where HINMEI='*L51399-OJ' and MAKER_CD='HAC3' and KYOTENKANRI_CD='A202' and
(GOUHI_KBN=1 or GOUHI_KBN=2) and DATA_STATUS=3
order by NYUKEN_YMD desc,SORTORDER_NYUKEN desc
)T_JISSEKI where rownum < 51
)TJISSEKI
left join (select NYUKEN_ID , NYUKEN_SEQ , max(substr(sys_connect_by_path( TOKUSAI_NO, ','), 2)) TOKUSAI_NO
from
(
select NYUKEN_ID , NYUKEN_SEQ, TOKUSAI_NO, colc, lead(colc) over(partition by NYUKEN_ID , NYUKEN_SEQ order by colc) cold
from ( select NYUKEN_ID , NYUKEN_SEQ, TOKUSAI_NO , row_number() over(order by NYUKEN_ID , NYUKEN_SEQ) colc
from T_JISSEKI_TOKUSAI )
)
start with cold is null
connect by prior colc=cold
group by NYUKEN_ID , NYUKEN_SEQ
) TJISSEKITOKUSAIon TJISSEKI.NYUKEN_ID=TJISSEKITOKUSAI.NYUKEN_ID
and TJISSEKI. NYUKEN_SEQ=TJISSEKITOKUSAI.NYUKEN_SEQ) T_JISSEKI_LF_T_JISSEKI_TOKUSAI
left join
(
select T_TSUCHISYO_LOT.NYUKEN_ID,T_TSUCHISYO_LOT.NYUKEN_SEQ,
T_TSUCHISYO_LOT.TSUCHISYO_NO,T_TSUCHISYO.TSUCHISYO_NO,
T_TSUCHISYO.Priority
from T_TSUCHISYO_LOT,T_TSUCHISYO
where T_TSUCHISYO_LOT.TSUCHISYO_NO=T_TSUCHISYO.TSUCHISYO_NO) T_TSUCHISYO_LOT-T_TSUCHISYO on T_TSUCHISYO_LOT-T_TSUCHISYO.NYUKEN_ID=T_JISSEKI_LF_T_JISSEKI_TOKUSAI.NYUKEN_ID
and T_TSUCHISYO_LOT-T_TSUCHISYO.NYUKEN_SEQ=JISSEKI_LF_T_JISSEKI_TOKUSAI.NYUKEN_SEQ红色部分都是测试正确的,在进行左连接的时候,报 ora-00918 列未定义 错误????
是什么原因,没搞清楚?????????????