帮忙写一条SQL语句

Question

是面试的一道SQL题 要求如下：有一张表T_family 有三个字段 id（不为零）、name、pid(如果没有父亲，则为零），
id和pid是对应的 表结构如下：
    
     id    name    pid
     1      A       0
     2      B       1
     3      C       2
     4      D       3
      5      E       4
     ..     ..      ..
     ..     ..      ..求出做过爷爷的人

Answer 1

如果单这点数据,好做
不过按表的结果,应该是会有多个家庭,就会有多个PID=0的
就复杂了

Answer 2

没有PK 只有这个表 而且是有多个家庭的，还有PID=0的

Answer 3

下面的结构：
   id    name    pid 
    1      A      0 
    2      B      1 
    3      C      2 
    4      D      3 
    5      E      4 
    6      F      0
    7      G      6
    8      H      5
    9      I      7
    ..    ..      .. 
    ..    ..      ..

Answer 4

区分家庭我觉得pid=0作为一个家庭的开始

Answer 5

/* Formatted on 2008/07/19 19:36 (Formatter Plus v4.8.8) */
WITH t_family AS
     (SELECT 1 ID, 'A' NAME, 0 pid
        FROM DUAL
      UNION ALL
      SELECT 2, 'B', 1
        FROM DUAL
      UNION ALL
      SELECT 3, 'C', 2
        FROM DUAL
      UNION ALL
      SELECT 4, 'D', 3
        FROM DUAL
      UNION ALL
      SELECT 5, 'E', 4
        FROM DUAL
      UNION ALL
      SELECT 6, 'F', 0
        FROM DUAL
      UNION ALL
      SELECT 7, 'G', 6
        FROM DUAL
      UNION ALL
      SELECT 8, 'H', 5
        FROM DUAL
      UNION ALL
      SELECT 9, 'I', 7
        FROM DUAL)
SELECT ID, NAME, pid
  FROM (SELECT aa.*,
                 LEAD (lv, 2, lv) OVER (PARTITION BY family_id ORDER BY lv)
               - lv gf
          FROM (SELECT     a.*, LEVEL lv,
                           SUBSTR
                              (SYS_CONNECT_BY_PATH (ID, ','),
                               2,
                               DECODE (INSTR (SYS_CONNECT_BY_PATH (ID, ','),
                                              ',',
                                              1,
                                              2
                                             ),
                                       0, LENGTH (SYS_CONNECT_BY_PATH (ID,
                                                                       ',')
                                                 ),
                                         INSTR (SYS_CONNECT_BY_PATH (ID, ','),
                                                ',',
                                                1,
                                                2
                                               )
                                       - 2
                                      )
                              ) family_id
                      FROM t_family a
                START WITH pid = 0
                CONNECT BY PRIOR ID = pid) aa)
 WHERE gf = 2结果Row# ID NAME PID1 1 A 0
2 2 B 1
3 3 C 2
4 4 D 3
5 6 F 0

Answer 6

看看数据是否符合你要求
ROW#那一列不用看,只是我的TOAD级结果集编号

Answer 7

select distinct yy.* from T_family  ez ,T_family  bb ,T_family yy 
where ez.pid=bb.id and bb.pid=yy.id 

Answer 8

If your Oracle version is 10g, try below SQL:
SQL> select * from t_family;       FID NAME                        PID
---------- -------------------- ----------
         1 A                             0
         2 B                             1
         3 C                             2
         4 D                             3
         5 E                             4
         8 H                             6
         9 H                             57 rows selectedSQL> 
SQL> select root_first,
  2         max(NAME)
  3    from (
  4          select connect_by_root fid as root_first
  5            from t_family tf
  6           connect by prior pid = fid
  7         )results,
  8         t_family
  9   where fid = root_first
 10   group by root_first
 11   having count(*) > 1;ROOT_FIRST MAX(NAME)
---------- --------------------
         2 B
         3 C
         4 D
         5 E
         9 HSQL>