Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4149663
  • 博文数量: 240
  • 博客积分: 11504
  • 博客等级: 上将
  • 技术积分: 4277
  • 用 户 组: 普通用户
  • 注册时间: 2006-12-28 14:24
文章分类

全部博文(240)

分类:

2008-02-12 14:29:55

有关PGSQL的INTERSECT和EXCEPT以及在MYSQL中的实现。
一下是表语句。
create table t1 (id serial not null,game_id int not null);

insert into t1(game_id) values (1),(2),(3),(5),(6),(8);

create table t2 (id serial not null,game_id int not null);

insert into t2(game_id) values (1),(2),(6),(8),(10),(4);
PGSQL:
交集,
t_girl=# select game_id from t1 intersect select game_id from t2;
 game_id
---------

       1
       2
       6
       8
(4 rows)

Time: 0.400 ms
t_girl=# select game_id from t1 where game_id not in (select t1.game_id from t1 left join t2 on t1.game_id = t2.game_id where t2.game_id is null);
 game_id
---------

       1
       2
       6
       8
(4 rows)

Time: 0.530 ms
t_girl=#
差集,
t_girl=# select game_id from t1 except select game_id from t2;
 game_id
---------

       3
       5
(2 rows)

Time: 0.371 ms
t_girl=# select t1.game_id from t1 left join t2 using(game_id) where t2.game_id is null;
 game_id
---------

       3
       5
(2 rows)

Time: 0.471 ms
MYSQL没有提供这两个关键字,可以用LEFT JOIN来代替。
交集,
mysql> select game_id from t1 where game_id not in (select t1.game_id from t1 left join t2 on t1.game_id = t2.game_id where t2.game_id is null);
+---------+

| game_id |
+---------+

| 1 |
| 2 |
| 6 |
| 8 |
+---------+

4 rows in set (0.01 sec)

mysql>
差集,

mysql> select t1.game_id from t1 left join t2 using(game_id) where t2.game_id is null;
+---------+

| game_id |
+---------+

| 3 |
| 5 |
+---------+

2 rows in set (0.00 sec)

以后的再更新。
阅读(5923) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

chinaunix网友2008-11-06 18:02:04

select game_id from t1 where game_id not in (select t1.game_id from t1 left join t2 on t1.game_id = t2.game_id where t2.game_id is null); 好像不能跑诶,提示在“(select t1.game_id ”附近有错误呀