使用SYS_CONNECT_BY_PATH的Oracle累积计数 [英] Oracle cumulative count using SYS_CONNECT_BY_PATH
本文介绍了使用SYS_CONNECT_BY_PATH的Oracle累积计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
当我尝试对实际数据进行以下查询时，它返回了更多的记录.请帮助解决此问题.
When i have tried the below query on actual data it returned more number of records. Please help to resolve this issue.
下面是表DM_TEMP_SUMMING_DVC_BY_FW中的实际数据
Below is the actual data in table 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生成链，但未按预期返回结果.该查询返回的记录数更多.
I have tried to use query to generate the chain based on firmware_version and image_prerequisite but it is not returning the results as expected. This query is returning more number of records.
/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
Expected results from the data stored in 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 |
推荐答案
仅对SQL Fiddle示例数据进行反复试验，这是返回所需数据的查询:
Solely with a trial and error over your SQL Fiddle sample data, this is the query returning your expected data:
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
and T1.dmc_id = T2.dmc_id
and T1.charging_group_id = T2.charging_group_id
CONNECT BY nocycle PRIOR firmware_version=image_prerequisite and prior dmc_id = dmc_id and prior charging_group_id = charging_group_id
) sum_device
FROM DM_TEMP_SUMMING_DVC_BY_FW t1
START WITH image_prerequisite IS NULL
CONNECT BY nocycle PRIOR firmware_version = image_prerequisite
and prior dmc_id = dmc_id
and prior charging_group_id = charging_group_id
注意:是的，正如krokodilko在上面的评论中正确提到的，charging_group_id和dmc_id对于您的预期结果非常重要.
Note: Yes, the charging_group_id and dmc_id are very important for your expected result, as krokodilko correctly mentioned in his above comment.
这篇关于使用SYS_CONNECT_BY_PATH的Oracle累积计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
