悲剧,绝对的悲剧,悲剧中的悲剧。
分类: Mysql/postgreSQL
2012-12-08 12:09:08
Student: | Book: | |||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
2 | yong | null | def | |
[Inner] Join | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
Left [Outer] Join: 左表所有行, 右表没有匹配行则均为null | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
2 | yong | null | null | |
Right [Outer] Join | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
null | null | null | def | |
Full [Outer] Join | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
2 | yong | null | null | |
null | null | null | def | |
CROSS JOIN | ||||
ID | Name | SID | Book | |
1 | feng | 1 | abc | |
1 | feng | null | def | |
2 | yong | 1 | abc | |
2 | yong | null | def |
现在有三张表
a表
id goodsid 数量
1 1 12
2 2 13
b表
id name unitid
1 材料1 1
2 材料2 2
c表
unitid name
2 吨
3 kg
如果采用select b.name,c.name,a.数量 from a,b,c where a.goodsid = b.id and b.unitid=c.unitid 就无法检索出材料一,
此时应该用:
select b.name,c.name,a.数量
from a left outer join b on a.goodsid = b.id
left outer join c on b.unitid=c.unitid
子查询例子:
1、单行子查询
select ename,deptno,sal
from emp
where deptno=(select deptno from dept where loc='NEW YORK');
2、多行子查询
SELECT ename,job,sal
FROM EMP
WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%');
3、多列子查询
SELECT deptno,ename,job,sal
FROM EMP
WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno);
4、内联视图子查询
(1)SELECT ename,job,sal,rownum
FROM (SELECT ename,job,sal FROM EMP ORDER BY sal);
(2)SELECT ename,job,sal,rownum
FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
WHERE rownum<=5;
5、在HAVING子句中使用子查询
SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN');
INNER JOIN 查询:
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON
表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON
Member.字段号=表X.字段号
转载自:http://www.cnblogs.com/yanluckly/archive/2009/02/24/1397013.html