-- --------------------------------

-- 利用sql子查询嵌套

-- --------------------------------

-- step 1. 子查询：找到每组最大的子类年龄

select parent_id,max(child_age) from child group by parent_id;

-- step 2. 找到符合的行 (很可能出现重复)

select cc.* from child cc

  INNER JOIN (select parent_id,max(child_age) child_age from child group by parent_id) tmp

  on tmp.parent_id = cc.parent_id and tmp.child_age = cc.child_age

  order by cc.parent_id,cc.child_id;

-- step 3. 为了解决重复需要再加一层,取唯一标识id最大/最小的一行

select max(cc.child_id) from child cc

  INNER JOIN (select parent_id,max(child_age) child_age from child group by parent_id) tmp

  on tmp.parent_id = cc.parent_id and tmp.child_age = cc.child_age

  group by cc.parent_id,cc.child_age

-- step 4. 在3中就找到了符合条件行的id

select * from parent pa

  LEFT JOIN (SELECT cc.* from child cc

  INNER JOIN (select max(cc.child_id) child_id from child cc

  INNER JOIN (select parent_id,max(child_age) child_age from child group by parent_id) tmp 

  on tmp.parent_id = cc.parent_id and tmp.child_age = cc.child_age

  group by cc.parent_id,cc.child_age) tmp on TMP.child_id = cc.child_id

  ) tmp on tmp.parent_id = pa.parent_id

 where 1=1 ORDER BY pa.parent_id;

-- --------------------------------

-- 利用oracle分析函数

-- --------------------------------

-- step-1：找到子类符合的id, DISTINCT去重

select DISTINCT first_value(cc.child_id)

    over(partition by cc.parent_id order by cc.parent_id,cc.child_age desc,cc.child_id desc) child_id

  from child cc;

-- step-2: 符合的子类结果集

SELECT * from child cc where cc.child_id in(

  select DISTINCT first_value(cc.child_id)

    over(partition by cc.parent_id order by cc.parent_id,cc.child_age desc,cc.child_id desc) child_id

  from child cc

);

SELECT * from child cc where EXISTS ( select 1 from (

  select DISTINCT first_value(cc.child_id)

    over(partition by cc.parent_id order by cc.parent_id,cc.child_age desc,cc.child_id desc) child_id

  from child cc) tmp where tmp.child_id = cc.child_id

);

-- step-3: 最终结果集

select * from parent pa

  LEFT JOIN child cc on cc.parent_id = pa.parent_id

  and cc.child_id in(

    select DISTINCT first_value(cc.child_id)over(partition by cc.parent_id order by cc.parent_id,cc.child_age desc,cc.child_id desc)

  from child cc)

where 1=1 ORDER BY pa.parent_id

-- -----------------------------

-- PARENT 父表

-- -----------------------------

CREATE TABLE "PARENT" (

"PARENT_ID" NUMBER NOT NULL ,

"PARENT_NAME" VARCHAR2(255 BYTE) NOT NULL

)

LOGGING NOCOMPRESS NOCACHE;

-- Records of PARENT

INSERT INTO "PARENT" VALUES ('', '周父');

INSERT INTO "PARENT" VALUES ('', '吴父');

INSERT INTO "PARENT" VALUES ('', '郑父');

INSERT INTO "PARENT" VALUES ('', '王父');

INSERT INTO "PARENT" VALUES ('', '赵父');

-- Checks structure for table PARENT

ALTER TABLE "PARENT" ADD CHECK ("PARENT_ID" IS NOT NULL);

ALTER TABLE "PARENT" ADD CHECK ("PARENT_NAME" IS NOT NULL);

-- -----------------------------

-- PARENT 子表

-- -----------------------------

CREATE TABLE "CHILD" (

"CHILD_ID" NUMBER NOT NULL ,

"CHILD_NAME" VARCHAR2(255 BYTE) NOT NULL ,

"CHILD_AGE" NUMBER NOT NULL ,

"PARENT_ID" VARCHAR2(255 BYTE) NOT NULL

)

LOGGING NOCOMPRESS NOCACHE;

-- ----------------------------

-- Records of CHILD

-- ----------------------------

INSERT INTO "CHILD" VALUES ('', '周一', '', '');

INSERT INTO "CHILD" VALUES ('', '周二', '', '');

INSERT INTO "CHILD" VALUES ('', '周三', '', '');

INSERT INTO "CHILD" VALUES ('', '吴一', '', '');

INSERT INTO "CHILD" VALUES ('', '吴二', '', '');

INSERT INTO "CHILD" VALUES ('', '吴三', '', '');

INSERT INTO "CHILD" VALUES ('', '吴四', '', '');

INSERT INTO "CHILD" VALUES ('', '郑一', '', '');

INSERT INTO "CHILD" VALUES ('', '郑二', '', '');

INSERT INTO "CHILD" VALUES ('', '王一', '', '');

-- Checks structure for table CHILD

ALTER TABLE "CHILD" ADD CHECK ("CHILD_ID" IS NOT NULL);

ALTER TABLE "CHILD" ADD CHECK ("CHILD_NAME" IS NOT NULL);

ALTER TABLE "CHILD" ADD CHECK ("CHILD_AGE" IS NOT NULL);

ALTER TABLE "CHILD" ADD CHECK ("PARENT_ID" IS NOT NULL);

测试表结构和数据