mysql纵表查询遇到的一个问题

A表:
CREATE   TABLE   `a`   (
    `aid`   int(11)   NOT   NULL,
    `astate`   int(1)   NOT   NULL
)   ENGINE=InnoDB   DEFAULT   CHARSET=utf8;

INSERT   INTO   `a`   (`aid`,   `astate`)   VALUES
(1,   1),
(2,   1),
(3,   1);


B表:
CREATE   TABLE   `b`   (
    `bid`   int(11)   NOT   NULL,
    `bfield`   varchar(255)   NOT   NULL
)   ENGINE=InnoDB   DEFAULT   CHARSET=utf8;

INSERT   INTO   `b`   (`bid`,   `bfield`)   VALUES
(1,   'f1 '),
(2,   'f2 '),
(3,   'f3 '),
(4,   'f4 ');


C表:
CREATE   TABLE   `c`   (
    `cid`   int(11)   NOT   NULL,
    `aid`   int(11)   NOT   NULL,
    `bid`   int(11)   NOT   NULL,
    `cvalue`   varchar(255)   NOT   NULL
)   ENGINE=InnoDB   DEFAULT   CHARSET=utf8;

INSERT   INTO   `c`   (`cid`,   `aid`,   `bid`,   `cvalue`)   VALUES
(1,   1,   1,   'v11 '),
(2,   1,   2,   'v12 '),
(3,   1,   3,   'v13 '),
(4,   2,   1,   'v21 '),
(5,   2,   2,   'v22 '),
(6,   3,   1,   'v31 ');

我现在的mysql语句:
SELECT   a.aid,   b.bid,   b.bfield,   cvalue
FROM   a  
LEFT   JOIN   (b,   c)  
ON   (a.aid   =   c.aid   AND   b.bid   =   c.bid)  
ORDER   BY   a.aid   ASC,   b.bid   ASC

得到的结果是:
aid   bid   bfield   cvalue
1       1       f1             v11
1       2       f2             v12
1       3       f3             v13
2       1       f1             v21
2       2       f2             v22
3       1       f1             v31

然后希望得出来的数据结构是:
aid   bid   bfield   cvalue
1       1       f1             v11
1       2       f2             v12
1       3       f3             v13
1       4       f4             null
2       1       f1             v21
2       2       f2             v22
2       3       f3             null
2       4       f4             null
3       1       f1             v31
3       2       f2             null
3       3       f3             null
3       4       f4             null

 

在csdn开三贴,最后总算得到想要的答案!

WWWWA的回答:

SELECT aa.aid,aa.bid,aa.bfield,c.cvalue FROM (
SELECT * FROM a,b
ORDER BY a.aid,bid) aa
LEFT JOIN c ON aa.bid=c.bid AND aa.aid=c.aid

 

ACMAIN_CHM的回答:

mysql> select a.aid,b.bid,b.bfield,c.cvalue

-> from (a , b) left join c on a.aid=c.aid and b.bid=c.bid

-> order by 1,2;

+-----+-----+--------+--------+

| aid | bid | bfield | cvalue |

+-----+-----+--------+--------+

| 1 | 1 | f1 | v11 |

| 1 | 2 | f2 | v12 |

| 1 | 3 | f3 | v13 |

| 1 | 4 | f4 | NULL |

| 2 | 1 | f1 | v21 |

| 2 | 2 | f2 | v22 |

| 2 | 3 | f3 | NULL |

| 2 | 4 | f4 | NULL |

| 3 | 1 | f1 | v31 |

| 3 | 2 | f2 | NULL |

| 3 | 3 | f3 | NULL |

| 3 | 4 | f4 | NULL |

+-----+-----+--------+--------+

12 rows in set (0.01 sec)

mysql>

标签: Aid, bid, NULL, NOT, int

sql内连接和外连接的区别

在之前,我对MSSQL中的内连接和外连接所得出的数据集不是很清楚。这几天重新温习了一下SQL的书本,现在的思路应该是很清楚了,现在把自己的理解发出来给大家温习下。希望和我一样对SQL的连接语句不太理解的朋友能够有所帮助.
有两个表A和表B。
表A结构如下:
Aid:int;标识种子,主键,自增ID
Aname:varchar

数据情况,即用select 

from A出来的记录情况如下图1所示:


图1:A表数据

表B结构如下:
Bid:int;标识种子,主键,自增ID
Bnameid:int

数据情况,即用select from B出来的记录情况如下图2所示:



图2:B表数据

为了把Bid和Aid加以区分,不让大家有误解,所以把Bid的起始种子设置为100。
有SQL基本知识的人都知道,两个表要做连接,就必须有个连接字段,从上表中的数据可以看出,在A表中的Aid和B表中的Bnameid就是两个连接字段。
下图3说明了连接的所有记录集之间的关系:



图3:连接关系图

现在我们对内连接和外连接一一讲解。
1.内连接:利用内连接可获取两表的公共部分的记录,即图3的记录集C
语句如下:Select from JOIN ON A.Aid=B.Bnameid
运行结果如下图4所示:



图4:内连接数据

其实select from A,B where A.Aid=B.Bnameid与Select from JOIN ON A.Aid=B.Bnameid的运行结果是一样的。
2.外连接:外连接分为两种,一种是左连接(Left JOIN)和右连接(Right JOIN)
  (1)左连接(Left JOIN):即图3公共部分记录集C+表A记录集A1。     
     语句如下:select from Left JOIN ON A.Aid=B.Bnameid
     运行结果如下图5所示:



图5:左连接数据

     说明:
           在语句中,A在B的左边,并且是Left Join,所以其运算方式为:A左连接B的记录=图3公共部分记录集C+表A记录集A1
           在图3中即记录集C中的存在的Aid为:2           
           图1中即表A所有记录集A中存在的Aid为:1 9
           表A记录集A1中存在的Aid=(图1中即A表中所有Aid)-(图3中即记录集C中存在的Aid),最终得出为:1 9
           由此得出图5中A左连接B的记录=图3公共部分记录集C+表A记录集A1,
           最终得出的结果图5中可以看出Bnameid及Bid非NULL的记录都为图3公共部分记录集C中的记录;Bnameid及Bid为NULL的Aid为1 9的四笔记录就是表A记录集A1中存在的Aid。

  (2)右连接(Right JOIN):即图3公共部分记录集C+表B记录集B1。
     语句如下:select from Right JOIN ON A.Aid=B.Bnameid
     运行结果如下图6所示:



图6:右连接数据

     说明:
           在语句中,A在B的左边,并且是Right Join,所以其运算方式为:A右连接B的记录=图3公共部分记录集C+表B记录集B1
           在图3中即记录集C中的存在的Aid为:2           
           图2中即表B所有记录集B中存在的Bnameid为:2 11
           表B记录集B1中存在的Bnameid=(图2中即B表中所有Bnameid)-(图3中即记录集C中存在的Aid),最终得出为:11
           由此得出图6中A右连接B的记录=图3公共部分记录集C+表B记录集B1,
           最终得出的结果图6中可以看出Aid及Aname非NULL的记录都为图3公共部分记录集C中的记录;Aid及Aname为NULL的Aid为11的记录就是表B记录集B1中存在的Bnameid。
     
总结:

通过上面的运算解说,相信很多人已经想到,上面的情况(包括图3的关系图)说明的都只是A在B的左边的情况,
以下语句B在A的右边的又会出现什么情况呢??
select from Left JOIN ON A.Aid=B.Bnameid
select from Right JOIN ON A.Aid=B.Bnameid

其实对图3左右翻转一下就可以得出以下结论:
select from Left JOIN ON A.Aid=B.Bnameid和select from Right JOIN ON A.Aid=B.Bnameid所得出的记录集是一样的

select from Right JOIN ON A.Aid=B.Bnameid和select from Left JOIN ON A.Aid=B.Bnameid所得出的记录集也是一样的。




 

 



假设一个数据库中有两张表,一张是学生表StudentInfo,一张是班级表ClassInfo,两张表之间用ClassId字段进行关联。

如果用内连接,正常的写法是这样的:

Select StudentInfo.*,ClassInfo.* from StudentInfo join ClassInfo on StudentInfo.ClassId=ClassInfo.ClassId

用这种写法不会出现笛卡尔积,但是内连接是允许省略连接条件的,也就是可以省略掉on后面的内容,所以如果写成这样:

Select StudentInfo.*,ClassInfo.* from StudentInfo,ClassInfo

也是可以通过编译的,但是这样一来就产生了一个笛卡尔积(关于笛卡尔积的内容见文章最后注解)。

但是因为怕产生笛卡尔积而拒绝使用内链接是不正确的,只要不省略掉连接条件,是不会产生笛卡尔积的。

具体采用内连接还是外连接要 看具体问题的需要。内连接的特性是只显示符合连接条件的记录,以刚才的语句为例,查询结果只显示学生表和班级表中ClassId相等的记录。举例来说,如 果有条学生记录的ClassId字段为空,那么用内链接将不会显示这条学生记录,因为在班级表里面是找不到对应的ClassId的。

而外连 接就不一样,以左外连接为例(右外连接类似),它除了显示符合连接条件的记录以外,还会显示所有左表中的记录(右外连接就是所有右表中的记录)。还是以刚 才的例子说明,如果学生表中有条学生记录的ClassId字段为空,用学生表左外连接班级表,那么是会显示这条学生记录的。

所以如果要求只显示已有分班的学生记录,那么就应该用内连接查询;如果要求查询所有学生记录,那么就应该采用外连接查询。

 

笛卡尔积:
假 设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。 可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。