CREATE TEMPORARY TABLE IF NOT EXISTS table1( pid string, feature int)
CREATE TEMPORARY TABLE IF NOT EXISTS table2( pid string, lable int)
INSERT INTO TABLE table1(pid, feature) VALUES('a', 1)
Select * from (select pid, max(feature) from table1 group by pid) as a left join table2 as b on a.pid = b.pid
Select * from (select pid, max(feature) from table1 group by pid) as a join table2 as b on a.pid = b.pid
Select * from (select pid, max(feature) from table1 group by pid) as a left join (select pid, max(feature) from table1 where pid='a' group by pid) as b on a.pid = b.pid
Select a.pid, a.max_f, b.max_f, c.lable from (select pid, max(feature) as max_f from table1 group by pid) as a left join (select pid, max(feature) as max_f from table1 where pid='a' group by pid) as b on a.pid = b.pid left join table2 as c ON (c.pid = a.pid) -> 위의 데이터와 다름
Select coalesce(a.pid, b.pid, c.pid), ff from (select pid, max(feature) as ff from table1 group by pid) as a full join table2 as b on a.pid = b.pid full join table2 as c on b.pid=c.pid;
DESC 테이블명 -> 테이블의 컬럼 정보를 보여줌
NVL() 함수 : Null인 경우 다른 값으로 대체 할 수 있음
예) select nvl(컬럼명, 0) as 컬럼명 from 테이블명
위의 컬럼에서 Null이 있는 경우 0으로 치환할 수 있음