问一个问题,我现在多表查询,如下select a.id,b.something from
table1 a,table2 b where a.id=b.id
group by a.id,b.something having sum(a.number)>1000如果A里面某些ID B里面没有,那我查出来的数据就只有B里面有ID的数据,那我如何查出所有符合sum(a.number)>1000条件的A表里面的所有记录,如果B表没有相关数据,那B.SOMETHINE就留空
table1 a,table2 b where a.id=b.id
group by a.id,b.something having sum(a.number)>1000如果A里面某些ID B里面没有,那我查出来的数据就只有B里面有ID的数据,那我如何查出所有符合sum(a.number)>1000条件的A表里面的所有记录,如果B表没有相关数据,那B.SOMETHINE就留空
table1 a,table2 b where a.id=b.id(+)
group by a.id,b.something having sum(a.number)>1000用个外连接就可以了
等价于
select a.*,b.* from tableA a,tableB b where a.id=b.id(+) 这个表示选择所有tableA的记录,如果不满足a.id=b.id的tableB的相关值全部为null
table1 a,table2 b where a.id=b.id(+) --注意这里多了个(+)
group by a.id,b.something having sum(a.number)>1000
--在9i以前可以这么写:
--左联:
SELECT a.id, a.name, b.address FROM a, b WHERE a.id = b.id(+);
--右联:
SELECT a.id, a.name, b.address FROM a, b WHERE a.id(+) = b.id;
--外联
SELECT a.id, a.name, b.address FROM a, b
WHERE a.id = b.id(+)
UNION
SELECT b.id, '' NAME, b.address FROM b WHERE NOT EXISTS (SELECT * FROM a WHERE a.id = b.id);
--在9i以上,已经开始支持SQL99标准,所以,以上语句可以写成:
--默认内部联结:
SELECT a.id, a.name, b.address, c.subject
FROM (a INNER JOIN b ON a.id = b.id)
INNER JOIN c
ON b.name = c.name
WHERE other_clause;
--左联
SELECT a.id, a.name, b.address FROM a LEFT OUTER JOIN b ON a.id = b.id WHERE other_clause;
--右联
SELECT a.id, a.name, b.address FROM a RIGHT OUTER JOIN b ON a.id = b.id WHERE other_clause;
--外联
SELECT a.id, a.name, b.address FROM a FULL OUTER JOIN b ON a.id = b.id WHERE other_clause;
SELECT a.id, a.name, b.address FROM a FULL OUTER JOIN b USING (id) WHERE other_clause;
select a.id,b.something from
table1 a
right left join table2 b
on a.id=b.id
group by a.id,b.something having sum(a.number)>1000
from table1 a right join table2 on b a.id=b.id
group by a.id,nvl(b.something,0)
having sum(a.number)>1000
from table1 a right join table2 b on a.id=b.id
group by a.id,nvl(b.something,0)
having sum(a.number)>1000