SQL的关联查询

来源：互联网发布：日本经济数据2016 编辑：程序博客网时间：2024/05/22 18:11

SQL的关联查询,分为inner join, full outer join , left (outer) join , right (outer) join, 以及 cross join (注,最后一个就是笛卡尔积)

select ugw.*, usn.*, gxy.* from

(select sno, imsi, imsdn, proceduretype, accesstime, releasetime from r_comp where netype in (4) and proceduretype in (10)) ugw

left join

(select sno, imsi, imsdn, proceduretype, accesstime, releasetime from r_comp where netype in (3) and proceduretype in (1, 2 , 3)) usn

on ((ugw.imsi = usn.imsi) and not (ugw.imsdn is not null and usn.imsdn is not null and ugw.imsdn <> usn.imsdn)

and ((ugw.accesstime- usn.accesstime) < 5 and (ugw.accesstime > usn.accesstime)))

left join

(select sno, imsi, imsdn, proceduretype, accesstime, releasetime from r_comp where netype in (4) and proceduretype in (100, 101, 102)) gxy

on ((ugw.imsi = gxy.imsi) and not (ugw.imsdn is not null and gxy.imsdn is not null and ugw.imsdn <> gxy.imsdn)

and ((gxy.accesstime - ugw.accesstime) < 5) and (gxy.accesstime > ugw.accesstime))

where (usn.sno is not null or gxy.sno is not null)

;

drop table r_comp

;

create table r_comp (

sno int,

imsi int,

imsdn int,

netype int,

proceduretype int,

accesstime int,

releasetime int

)

;

-- usn

insert into r_comp values (1, 1, 1, 3, 1, 100, 150);

insert into r_comp values (2, 2, 2, 3, 2, 200, 250);

insert into r_comp values (3, 3, 3, 3, 3, 300, 350);

insert into r_comp values (4, 3, 3, 3, 3, 300, 350);

insert into r_comp values (5, 3, 3, 3, 4, 300, 350); -- not fullish any one of procedure type

insert into r_comp values (6, 6, null, 3, 3, 400, 450);

-- ugw

insert into r_comp values (100, 1, 1, 4, 10, 101, 103); -- fulllish with 1

insert into r_comp values (101, 1, 1, 4, 10, 105, 109); -- not fullish with any one

insert into r_comp values (102, 1, 1, 4, 10, 104, 104); -- fullish with 1

insert into r_comp values (103, 1, 1, 4, 10, 110, 115);

insert into r_comp values (104, 3, 3, 4, 10, 301, 304); -- fulllish with 3, 4

insert into r_comp values (105, 3, 3, 4, 10, 302, 304); -- fulllish with 3, 4

insert into r_comp values (106, 6, 6, 4, 10, 401, 403); -- fulllish with 6

-- gxy

insert into r_comp values (1000, 1, 1, 4, 100, 102, 102); -- fullish with 100

insert into r_comp values (1001, 1, 1, 4, 100, 103, 103); -- fulllist with 100

insert into r_comp values (1002, 1, 1, 4, 101, 111, 112); -- fulllist with ugw 103

insert into r_comp values (1003, 3, 3, 4, 102, 302, 303); -- fullist with ugw 104, 105

insert into r_comp values (1004, 3, 3, 4, 102, 303, 303); -- fullist with ugw 104, 105

insert into r_comp values (1005, 3, 3, 4, 102, 310, 320); -- nothing with relation

insert into r_comp values (1006, 6, null, 4, 102, 402, 402); -- fulllish with 106

0 0