【database】oracle关联查询主表对应的特定一行从表结果集

主表：

从表：

结果集： 查询从表中年龄最大的一行数据，如果存在年龄相等的则为了保证唯一取id(主键)最大的一行。

一、利用sql子查询嵌套

-- --------------------------------
-- 利用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自带的分析函数

-- --------------------------------
-- 利用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

三、分析区别

sql嵌套当然要更通用点，而且看执行计划，它的效率、消耗都要比oracle少很多；

oracle分析函数感觉还是子查询嵌套了一层，而且效率、消耗都比较高。

(才知道oracle的执行计划，完全不懂。只是看着sql嵌套查询的执行计划高效很多)

四、测试数据

-- -----------------------------
-- 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 ('1', '周父');
INSERT INTO "PARENT" VALUES ('2', '吴父');
INSERT INTO "PARENT" VALUES ('3', '郑父');
INSERT INTO "PARENT" VALUES ('4', '王父');
INSERT INTO "PARENT" VALUES ('5', '赵父');
-- 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 ('1', '周一', '6', '1');
INSERT INTO "CHILD" VALUES ('2', '周二', '8', '1');
INSERT INTO "CHILD" VALUES ('3', '周三', '7', '1');

INSERT INTO "CHILD" VALUES ('4', '吴一', '5', '2');
INSERT INTO "CHILD" VALUES ('5', '吴二', '3', '2');
INSERT INTO "CHILD" VALUES ('6', '吴三', '5', '2');
INSERT INTO "CHILD" VALUES ('7', '吴四', '2', '2');

INSERT INTO "CHILD" VALUES ('8', '郑一', '8', '3');
INSERT INTO "CHILD" VALUES ('9', '郑二', '4', '3');

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

-- 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);