очень важны для вашего ожидаемого результата, так как крокодилко правильно упомянул в своем приведенном выше комментарии.
я попробовал приведенный ниже запрос по фактическим данным, он вернул большее количество записей. Пожалуйста, помогите решить эту проблему.
Ниже приведены фактические данные в таблице DM_TEMP_SUMMING_DVC_BY_FW
+-----------+-------+-----------------+------------+------------------+
device_count| dmc_id| firmware_version| cg_id |image_prerequisite|
+-----------+-------+-----------------+------------+------------------+
| 40 | 408 |RT2 |0000 |RT1 |
| 24 | 408 |RT3 |0000 |RT2 |
| 18 | 408 |RT4 |0000 |RT3 |
| 2109 | 408 |RT1 |0000 |null |
| 1 | 142 |RT1 |0000 |null |
| 1 | 142 |RT2 |0000 |RT1 |
| 1 | 408 |RT1 |HFOTA1 |null |
| 1 | 408 |RT1 |HFOTA2 |null |
| 1 | 408 |RT1 |HFOTA3 |null |
| 1 | 408 |RT1 |HFOTA4 |null |
| 1 | 408 |RT1 |HFOTA5 |null |
+-----------+-------+-----------------+------------+------------------+
Запрос, который я выполнил:
SELECT SYS_CONNECT_BY_PATH(firmware_version, '/') path_,
firmware_version,
device_count,
dmc_id,
charging_group_id ,
IMAGE_PREREQUISITE,
(SELECT SUM(device_count)
FROM DM_TEMP_SUMMING_DVC_BY_FW t2
START WITH t1.firmware_version =t2.firmware_version
CONNECT BY nocycle PRIOR firmware_version=image_prerequisite
) sum_device
FROM DM_TEMP_SUMMING_DVC_BY_FW t1
START WITH image_prerequisite IS NULL
CONNECT BY nocycle PRIOR firmware_version =image_prerequisite
Ожидаемый результат:
+--------------------+-------+-----------------+-------+--------------------+
cumm_device_count | dmc_id| firmware_version| cg_id |chain |
+--------------------+-------+-----------------+-------+--------------------+
| 82 | 408 |RT2 |0000 |null/RT1/RT2 |
| 42 | 408 |RT3 |0000 |null/RT1/RT2/RT3 |
| 18 | 408 |RT4 |0000 |null/RT1/RT2/RT3/RT4|
| 2191 | 408 |RT1 |0000 |null/RT1 |
| 2 | 142 |RT1 |0000 |null/RT1 |
| 1 | 142 |RT2 |0000 |null/RT1/RT2 |
| 1 | 408 |RT1 |HFOTA1 |null/RT1 |
| 1 | 408 |RT1 |HFOTA2 |null/RT1 |
| 1 | 408 |RT1 |HFOTA3 |null/RT1 |
| 1 | 408 |RT1 |HFOTA4 |null/RT1 |
| 1 | 408 |RT1 |HFOTA5 |null/RT1 |
+--------------------+-------+-----------------+-------+--------------------+
Фактические возвращенные результаты запроса:
Я пытался использовать запрос для создания цепочки на основе firmware_version и image_prerequisite, но он не возвращает результаты, как ожидалось. Этот запрос возвращает большее количество записей, с.
/RT1 RT1 2109 408 0000000000000000 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1 RT1 1 408 HFOTA-0000001885 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1 RT1 1 408 HFOTA-0000000041 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1 RT1 1 408 HFOTA-0000000441 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1 RT1 1 408 HFOTA-0000000359 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1 RT1 1 408 HFOTA-0000000334 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1 RT1 1 1422 0000000000000000 2990
/RT1/RT2 RT2 40 408 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
/RT1/RT2 RT2 1 1422 0000000000000000 RT1 125
/RT1/RT2/RT3 RT3 24 408 0000000000000000 RT2 42
/RT1/RT2/RT3/RT4 RT4 18 408 0000000000000000 RT3 18
Ожидаемые результаты от данных, хранящихся в sqlfiddle:http://sqlfiddle.com/#!4/3cd9b/1
|/RT1/RT2/RT3/RT4| RT4 | 18 | 408| 0000000000000000| 24028| 18|
|/RT1/RT2/RT3 | RT3 | 24 | 408| 0000000000000000| 24028| 42|
|/AP1/AP2/AP3 | AP3 | 1 | 408| 0000000000000000| 24028| 1 |
|/RT1/RT2 | RT2 | 40 | 408| 0000000000000000| 24028| 82|
|/AP1/AP2 | AP2 | 2 | 408| 0000000000000000| 2 | 3 |
|/AP1 | AP1 | 1 | 408| 0000000000000000| 1 | 4 |
|/RT1 | RT1 |2109| 408| 0000000000000000| 24028|2191|
|/AS1 | AS1 | 1 | 408| 0000000000000000| 24028| 1 |
|/LRA1.NOV9.01 |LRA1.NOV9.01| 2 | 408| 0000000000000002| 106 | 2 |
|/LRA001 | LRA001 | 9 | 408| 0000000000000002| 106 | 9 |
|/LR1R_01 | LR1R_01 |15 | 408| 0000000000000002| 106 | 15|
|/APK29.2013 |APK29.2013 | 4 | 408| 0000000000000002| 106 | 4 |
|/APK2013.29 |APK2013.29 | 2 | 408| 0000000000000002| 106 | 2 |
|/ADR_TLRA1 |ADR_TLRA1 | 2 | 408| 0000000000000002| 106 | 2 |
|/ADR37 |ADR37 | 1 | 408| 0000000000000002| 106 | 1 |
|/A0 |A0 | 5 | 408| 0000000000000002| 106 | 5 |
|/36 |36 | 2 | 408| 0000000000000002| 106 | 2 |
|/LRA1_K01 |LRA1_K01 | 2 | 408| 0000000000000002| 106 | 2 |
|/abc |abc | 5 | 408| 0000000000000002| 106 | 5 |
|/VZW_U01 |VZW_U01 | 1 | 408| 0000000000000002| 106 | 1 |
|/VZW.NOV9.01 |VZW.NOV9.01 | 1 | 408| 0000000000000002| 106 | 1 |
|/TOSS_01 |TOSS_01 | 1 | 408| 0000000000000002| 106 | 1 |
|/TK_ST001 |TK_ST001 | 1 | 408| 0000000000000002| 106 | 1 |
|/SVP01 |SVP01 | 1 | 408| 0000000000000002| 106 | 1 |
|/LRA1v1 |LRA1v1 | 1 | 408| 0000000000000002| 106 | 1 |
|/LRA2_R01 |LRA2_R01 | 2 | 408| 0000000000000002| 106 | 2 |
|/MMY02-2013 |MMY02-2013 | 3 | 408| 0000000000000002| 106 | 3 |
|/PP0_MR1 |PP0_MR1 | 1 | 408| 0000000000000002| 106 | 1 |
|/RT1 |RT1 | 1 | 408| HFOTA-0000000041| 1 | 1 |
|/RT1 |RT1 | 1 | 408| HFOTA-0000000334| 2 | 1 |
|/RT1 |RT1 | 1 | 408| HFOTA-0000000359| 1 | 1 |
|/RT1 |RT1 | 1 | 408| HFOTA-0000000441| 1 | 1 |
|/RT1 |RT1 | 1 | 408| HFOTA-0000001885| 2 | 1 |
|/RT1/RT2 |RT2 | 1 | 1422| 0000000000000000| 7 | 1 |
|/RT1 |RT1 | 1 | 1422| 0000000000000000| 7 | 2 |