SQL连接——笛卡尔积

agangliu0400

　　最近在用几张表连接起来查询的时候发现有时会得到一模一样的数据，有时却不会，为了搞清楚这是怎么回事，特地学习了一下关于笛卡尔积与连接相关的知识。
　　所谓的笛卡尔积，也就是笛卡尔乘积，因此如果是普通的两张表连接，就是将2张表乘起来显示，如有以下2张表：
　　number:
　　id
　　value
　　1
　　100
　　2
　　150
　　string:
　　Id
　　value
　　1
　　hello
　　2
　　hi
　　输入select number.*, string.* from number, string;语句，将得到：
　　number.id
　　number.value
　　string.id
　　string.value
　　1
　　100
　　1
　　hello
　　1
　　100
　　2
　　hi
　　2
　　150
　　1
　　hello
　　2
　　150
　　2
　　hi
　　同样的，如果再多一张表，比如说是有3条记录的表，那么三张表连接得到的将是2*2*3 = 12条记录，这就是所谓的笛卡尔乘积。
　　等值连接就是在笛卡尔乘积的基础上剔除不相等的记录，如：
　　输入select number.*, string.* from number, string where number.id = string.id;语句，sql将根据条件number.id = string.id这个等值关系将这两个值不相等的记录剔除，得到
　　number.id
　　number.value
　　string.id
　　string.value
　　1
　　100
　　1
　　hello
　　2
　　150
　　2
　　hi
　　笛卡尔积就简单介绍完了，接着说什么时候会出现一模一样的表，这时又应该怎么办？
　　假如有以下三张表：
　　game:
　　id
　　name
　　1001
　　LG20120801
　　winning:
　　game_id
　　serial_no
　　level
　　prize
　　1001
　　20120801001
　　1
　　100000
　　1001
　　20120801020
　　2
　　10000
　　1001
　　20120801300
　　3
　　1000
　　prize_statistics
　　game_id
　　level
　　count
　　prize
　　1001
　　1
　　1
　　100000
　　1001
　　2
　　1
　　10000
　　1001
　　3
　　1
　　1000
　　这里第二张是中奖信息表，第三张表是中奖统计表，当然了，这里只列出其中一个game的信息，事实上可能有很多game, 这里只以一个game来介绍查询情况，所以只列出一个game的信息即可，如果要查询详细的中奖情况，则输入：
　　select g.name,
　　w.serial_no,
　　w.level,
　　p.count,
　　p.prize,
　　from game g, winning w, prize_statistics p
　　where g.id = w.game_id
　　and w.game_id = p.game_id
　　and w.level = p.level;
　　这时得到：
　　name
　　serial_no
　　level
　　count
　　prize
　　LG20120801
　　20120801001
　　1
　　1
　　100000
　　LG20120801
　　20120801020
　　2
　　1
　　10000
　　LG20120801
　　20120801300
　　3
　　1
　　1000
　　这里得到的结果是不会重复的，那什么情况下会出现重复的记录呢
　　假如上面game表改一下：
　　game:
　　id
　　name
　　1001
　　LG20120801
　　1002
　　LG20120802
　　输入：
　　select g.name,
　　w.serial_no,
　　w.level,
　　p.count,
　　p.prize,
　　from game g, winning w, prize_statistics p
　　where w.game_id = p.game_id
　　and w.level = p.level;
　　得到：
　　name
　　serial_no
　　level
　　count
　　prize
　　LG20120801
　　20120801001
　　1
　　1
　　100000
　　LG20120801
　　20120801020
　　2
　　1
　　10000
　　LG20120801
　　20120801300
　　3
　　1
　　1000
　　LG20120802
　　20120801001
　　1
　　1
　　100000
　　LG20120802
　　20120801020
　　2
　　1
　　10000
　　LG20120802
　　20120801300
　　3
　　1
　　1000
　　如果这时不列出game name的话就得到：
　　serial_no
　　level
　　count
　　prize
　　20120801001
　　1
　　1
　　100000
　　20120801020
　　2
　　1
　　10000
　　20120801300
　　3
　　1
　　1000
　　20120801001
　　1
　　1
　　100000
　　20120801020
　　2
　　1
　　10000
　　20120801300
　　3
　　1
　　1000
　　这样就得到3个2条一模一样的记录，如果要得到唯一的数据，可以用distinct过一遍，也就是：
　　select distinct g.name,
　　w.serial_no,
　　w.level,
　　p.count,
　　p.prize,
　　from game g, winning w, prize_statistics p
　　where w.game_id = p.game_id
　　and w.level = p.level;
　　不过这里不建议这么做，在数据的表中，一般记录都是唯一的，这时用连接查出来的结果也应该是唯一的，在少输出某些条件的情况下可能得到看似一样的数据，但是事实上并不是一样的（这跟上面的情况不一样），所以用distinct时会把这些数据给过滤掉，因此正确的作法是先确定一下在数据库中所查询的表里面的记录是不是唯一的，如果是唯一的而用连接查询到多条一样的记录的话那么有可能是因为少输出某些条件，也有可能是因为查询语句不正确，上面就是因为查询语句不正确导致的，这里少了判断game.id = winning.game_id这个条件，如果加上这个条件就正确了，在使用多表连接时最常出现的错误的就是where语句少判断条件，往往是多张表，只写了2张表的等值关系，正确的做法应该是如果有多张表的话，在where语句中要涉及到每张表，不能少了某张表。像我上次要查LG20120801这个游戏的中奖情况，使用以下查询语句：
　　select gi.game_instance_name,
　　gi.draw_date,
　　w.ticket_serialno,
　　ws.prize_level,
　　ws.prize_number,
　　ws.prize_amount,
　　ws.actual_payout
　　from winning_statistics ws, game_instance gi, winning w
　　where ws.game_instance_id = gi.game_instance_id
　　and w.game_instance_id = gi.game_instance_id –-ws, w, gi都涉及
　　and w.prize_level = ws.prize_level
　　and w.version = gi.version –-w, gi, ws 都要涉及到
　　and ws.version = w.version –-少了这个条件，找了半天才发现，加上后结果就正确了。
　　and gi.game_instance_name = 'LG20120801'
　　order by gi.game_instance_name, ws.prize_level;
　　连接就讲到这里了，接着说说distinct。上面我说连接查询的时候尽量不要用distinct，那distinct什么时候用呢，如果确定记录是一模一样的时候，去掉这些重复的记录时就派上用场了，最简单的一个用法，如我要查看一下当前的时间，这时我用：
　　select sysdate from winning;
　　这时sql会查询出N（N=winning的记录数）条记录出来，而且内容全部都是当前时间，这时应该用
　　select distinct sysdate from winning;
　　这时就得到我们想要的系统时间了。