为什么我的层次结构查询显示重复记录? [英] Why my hierarchy query is showing duplicate records?

问题描述

我的要求是查找一个月中所有过去的日子.以下是我的示例查询.

My requirement is to find all lapsed days in a month. Below are my sample queries.

CREATE TABLE custom.date_full (
    sno         NUMBER,
    curr_date   DATE);

INSERT INTO custom.date_full VALUES ( 1,'27-sep-2018' );
INSERT INTO custom.date_full VALUES ( 2,'27-sep-2018' );

--Query:1 - RETURNS 4 RECORDS AS EXPECTED
 SELECT  curr_date, 
        TRUNC (curr_date, 'MM') + LEVEL - 1 AS DAY,
        LEVEL 
   FROM (SELECT * FROM custom.date_full WHERE sno=1)
CONNECT BY level<=4
  ORDER BY DAY;

--Query 2: RETURNS 15 RECORDS WITH DUPLICATES
SELECT  curr_date, 
        TRUNC (curr_date, 'MM') + LEVEL - 1 AS DAY,
        LEVEL 
   FROM custom.date_full 
  WHERE sno=1
CONNECT BY level<=4
  ORDER BY DAY;

我的Query 1工作正常，但Query 2显示重复记录.为什么?

My Query 1 works good but Query 2 is showing duplicate records. Why?

推荐答案

您不了解CONNECT BY的工作方式.这是Oracle如何评估第二查询的演练.

You are not understanding how CONNECT BY works. Here is a walkthrough of how Oracle is evaluating your 2nd query.

没有START WITH子句，表中的每一行都将用作起点或层次结构中的根".

Without a START WITH clause, every row in your table with be used as a starting point, or "root" in your hierarchy.

由于您没有CONNECT BY条件(例如"columnA = PRIOR columnB")，因此表中的每一行都将被视为每隔一行的子级.这将永远发生，直到达到您的LEVEL <=4条件.

Since you have no CONNECT BY conditions (like "columnA = PRIOR columnB"), every row in your table will be considered a child of every other row. This will happen forever, until your LEVEL <=4 condition is reached.

所以

LEVEL 1
--------
SNO 1
SNO 2

说明:表中的每一行都是其自身层次结构的起点(因为您没有START WITH条件).

Explanation: Each row in your table is a starting point of its own hierarchy (because you have no START WITH conditions).

LEVEL 2
--------
SNO 1 -> SNO 1
SNO 1 -> SNO 2 
SNO 2 -> SNO 1
SNO 2 -> SNO 2

这4行的说明-SNO 1和SNO 2都是根，而对于每个根，SN​​O 1和SNO 2都是子代.因此，2x2行= 4行.

Explanation of those 4 rows -- both SNO 1 and SNO 2 are roots, and for each root, SNO 1 and SNO 2 are children. So, 2x2 rows = 4 rows.

LEVEL 3 
-------
SNO 1 -> SNO 1 -> SNO 1
SNO 1 -> SNO 1 -> SNO 2
SNO 1 -> SNO 2 -> SNO 1 
SNO 1 -> SNO 2 -> SNO 2 
SNO 2 -> SNO 1 -> SNO 1
SNO 2 -> SNO 1 -> SNO 2
SNO 2 -> SNO 2 -> SNO 1
SNO 2 -> SNO 2 -> SNO 2

这8行的说明.从第2级开始的4行开始，SNO 1和SNO 2都是子级，在第3级给出4x2 = 8行.

Explanation of those 8 rows. Starting with the 4 rows from level 2, both SNO 1 and SNO 2 are children of each, giving 4x2 = 8 rows at level 3.

我不会画的第4级，同样会给出8x2 = 16行.

Level 4, which I won't draw out, will similarly give 8x2 = 16 rows.

因此，总共有2 + 4 + 8 + 16 = 30行. (即1级+ 2级+ 3级+ 4级).

So, in total, you have 2 + 4 + 8 + 16 = 30 rows. (That's level 1 + level 2 + level 3 + level 4).

然后，在CONNECT BY处理之后(如上所示)，在之后，应用WHERE子句，将最终结果限制为值(层次结构最低级别)为SNO = 1.这恰好是您得到的30行(即15行)的一半.

Then, after your CONNECT BY processing (shown above), the WHERE clause is applied, limiting your final results to rows where the value (at the lowest level of the hierarchy) is SNO = 1. That is exactly half of the 30 rows, or 15 rows, which is what you are getting.

这篇关于为什么我的层次结构查询显示重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！