SQL连接——笛卡尔积
最近在用几张表连接起来查询的时候发现有时会得到一模一样的数据，有时却不会，为了搞清楚这是怎么回事，特地学习了一下关于笛卡尔积与连接相关的知识。所谓的笛卡尔积，也就是笛卡尔乘积，因此如果是普通的两张表连接，就是将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;
这时就得到我们想要的系统时间了。
页:
[1]