oracle 中查询一个有数据的视图，加上rownum后没有数据显示出来

SELECT T1.*, T.DWMC DWJC
                  FROM (SELECT SORTN, D.DWBM, D.DWJC DWMC
                          FROM MW_APP.DM_DWBM_SORT K,
                               (SELECT DWJC, DWBM, OBJ_ID
                                  FROM MW_APP.DM_DWBM WD
                                 WHERE WD.JB <> 2
                                   AND WD.ZXRQ IS NULL
                                   AND WD.CXSJ IS NULL
                                   AND WD.XTFL LIKE '%2%'
                                   AND LENGTH(WD.DWBM) = 6
                                   AND WD.DWBM NOT IN ('012020',
                                                       '013030',
                                                       '014040',
                                                       '016060',
                                                       '010106',
                                                       '010104',
                                                       '010105',
                                                       '717151')
                                   AND WD.DWMC NOT LIKE '%示范区%'
                                   AND WD.DWMC NOT LIKE '%超高压') D
                         WHERE K.DW_OBJID = D.OBJ_ID) T,
                       (SELECT DWBM,
                               COL1,
                               COL2 DQTZ,
                               '2016-06-02至2016-06-02' TJQZ,
                               HXJSS HXJRJSS,
                               ZXJSS ZXJRJSS,
                               JRZS,
                               DECODE(HXJSL,
                                      '0',
                                      '0.00%',
                                      '100',
                                      '100.00%',
                                      TRIM(TO_CHAR(HXJSL, '99999999990.99')) || '%') HXJSL,
                               DECODE(ZXJSL,
                                      '0',
                                      '0.00%',
                                      '100',
                                      '100.00%',
                                      TRIM(TO_CHAR(ZXJSL, '99999999990.99')) || '%') ZXJSL
                          FROM MW_APP.ZXJC_ZMJC_JSX_TEMP
                         WHERE TYPE = 'GDDY'
                           AND COL1 = '汇总值'
                           AND COL2 = '汇总值'
                           AND PARENT_OBJ_ID =
                               (SELECT OBJ_ID
                                  FROM MW_APP.ZXJC_ZMJC_SS_TJ_TASK
                                 WHERE TASK_ID = 116)) T1
                 WHERE T.DWBM = T1.DWBM
                 ORDER BY T.SORTN


这个SQL查询是有结果的，但是外面包一层SELECT t0.*, ROWNUM AS count_id FROM (。。。。） t0  就查询不出结果了

11 个解决方案

#1

是没有数据了呢，还是查不出结果 ?

#2

查不出结果。

#3

按说是有查询结果数据的，但查不出，SQL也不报错

#4

不应该呀，你好好检查一下，看看哪里有没有写错，理论上是可以的~

#5

引用 4 楼 ghx287524027 的回复:

不应该呀，你好好检查一下，看看哪里有没有写错，理论上是可以的~

按说就是不应该，但就是这样

#6

引用 5 楼 luyifa2010 的回复:

Quote: 引用 4 楼 ghx287524027 的回复:

不应该呀，你好好检查一下，看看哪里有没有写错，理论上是可以的~

按说就是不应该，但就是这样

逐步测试一下，先只加一个外层select看看有没有数据，然后再加rownum

#7

引用 6 楼 ghx287524027 的回复:

Quote: 引用 5 楼 luyifa2010 的回复:

Quote: 引用 4 楼 ghx287524027 的回复:

不应该呀，你好好检查一下，看看哪里有没有写错，理论上是可以的~

按说就是不应该，但就是这样

逐步测试一下，先只加一个外层select看看有没有数据，然后再加rownum

加select 是没有问题的，就是加rownum后，就查询不出结果了

#8

SELECT t0.*, ROWNUM AS count_id FROM
( SELECT T1.*, T.DWMC DWJC
                  FROM (SELECT SORTN, D.DWBM, D.DWJC DWMC
                          FROM MW_APP.DM_DWBM_SORT K,
                               (SELECT DWJC, DWBM, OBJ_ID
                                  FROM MW_APP.DM_DWBM WD
                                 WHERE WD.JB <> 2
                                   AND WD.ZXRQ IS NULL
                                   AND WD.CXSJ IS NULL
                                   AND WD.XTFL LIKE '%2%'
                                   AND LENGTH(WD.DWBM) = 6
                                   AND WD.DWBM NOT IN ('012020',
                                                       '013030',
                                                       '014040',
                                                       '016060',
                                                       '010106',
                                                       '010104',
                                                       '010105',
                                                       '717151')
                                   AND WD.DWMC NOT LIKE '%示范区%'
                                   AND WD.DWMC NOT LIKE '%超高压') D
                         WHERE K.DW_OBJID = D.OBJ_ID) T,
                       (SELECT DWBM,
                               COL1,
                               COL2 DQTZ,
                               '2016-06-02至2016-06-02' TJQZ,
                               HXJSS HXJRJSS,
                               ZXJSS ZXJRJSS,
                               JRZS,
                               DECODE(HXJSL,
                                      '0',
                                      '0.00%',
                                      '100',
                                      '100.00%',
                                      TRIM(TO_CHAR(HXJSL, '99999999990.99')) || '%') HXJSL,
                               DECODE(ZXJSL,
                                      '0',
                                      '0.00%',
                                      '100',
                                      '100.00%',
                                      TRIM(TO_CHAR(ZXJSL, '99999999990.99')) || '%') ZXJSL
                          FROM MW_APP.ZXJC_ZMJC_JSX_TEMP
                         WHERE TYPE = 'GDDY'
                           AND COL1 = '汇总值'
                           AND COL2 = '汇总值'
                           AND PARENT_OBJ_ID =
                               (SELECT OBJ_ID
                                  FROM MW_APP.ZXJC_ZMJC_SS_TJ_TASK
                                 WHERE TASK_ID = 116)) T1
                 WHERE T.DWBM = T1.DWBM
                 ORDER BY T.SORTN
) t0

完整的是这么写的？

#9

引用 8 楼 ghx287524027 的回复:

SELECT t0.*, ROWNUM AS count_id FROM
( SELECT T1.*, T.DWMC DWJC
                  FROM (SELECT SORTN, D.DWBM, D.DWJC DWMC
                          FROM MW_APP.DM_DWBM_SORT K,
                               (SELECT DWJC, DWBM, OBJ_ID
                                  FROM MW_APP.DM_DWBM WD
                                 WHERE WD.JB <> 2
                                   AND WD.ZXRQ IS NULL
                                   AND WD.CXSJ IS NULL
                                   AND WD.XTFL LIKE '%2%'
                                   AND LENGTH(WD.DWBM) = 6
                                   AND WD.DWBM NOT IN ('012020',
                                                       '013030',
                                                       '014040',
                                                       '016060',
                                                       '010106',
                                                       '010104',
                                                       '010105',
                                                       '717151')
                                   AND WD.DWMC NOT LIKE '%示范区%'
                                   AND WD.DWMC NOT LIKE '%超高压') D
                         WHERE K.DW_OBJID = D.OBJ_ID) T,
                       (SELECT DWBM,
                               COL1,
                               COL2 DQTZ,
                               '2016-06-02至2016-06-02' TJQZ,
                               HXJSS HXJRJSS,
                               ZXJSS ZXJRJSS,
                               JRZS,
                               DECODE(HXJSL,
                                      '0',
                                      '0.00%',
                                      '100',
                                      '100.00%',
                                      TRIM(TO_CHAR(HXJSL, '99999999990.99')) || '%') HXJSL,
                               DECODE(ZXJSL,
                                      '0',
                                      '0.00%',
                                      '100',
                                      '100.00%',
                                      TRIM(TO_CHAR(ZXJSL, '99999999990.99')) || '%') ZXJSL
                          FROM MW_APP.ZXJC_ZMJC_JSX_TEMP
                         WHERE TYPE = 'GDDY'
                           AND COL1 = '汇总值'
                           AND COL2 = '汇总值'
                           AND PARENT_OBJ_ID =
                               (SELECT OBJ_ID
                                  FROM MW_APP.ZXJC_ZMJC_SS_TJ_TASK
                                 WHERE TASK_ID = 116)) T1
                 WHERE T.DWBM = T1.DWBM
                 ORDER BY T.SORTN
) t0

完整的是这么写的？

恩，是这样的

#10

你看一下加rownum和不加rownum的执行计划，找找区别在哪。可以加我q，晚上聊聊，212646490，先下班了~

#11

没有数据显示出来是这个视图又和其他表连接或者在这个基础上加了where条件之后很久查不出数据 ？
如果是这样的话也正常。