#
# Table structure for table 't1'
#CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 't1'
#INSERT INTO t1 VALUES("1", "aa");
INSERT INTO t1 VALUES("2", "bb");
INSERT INTO t1 VALUES("3", "cc");
INSERT INTO t1 VALUES("4", "dd");
#
# Table structure for table 't2'
#CREATE TABLE t2 (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 't2'
#INSERT INTO t2 VALUES("1", "aa");
INSERT INTO t2 VALUES("2", "aa");
INSERT INTO t2 VALUES("3", "bb");
INSERT INTO t2 VALUES("4", "bb");
mysql> SELECT t1.* FROM t1 LEFT JOIN t2
> ON t1.name = t2.name
> WHERE t2.id IS NULL;
+----+------+
| id | name |
+----+------+
| 3 | cc |
| 4 | dd |
+----+------+
2 rows in set (0.00 sec)
# Table structure for table 't1'
#CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 't1'
#INSERT INTO t1 VALUES("1", "aa");
INSERT INTO t1 VALUES("2", "bb");
INSERT INTO t1 VALUES("3", "cc");
INSERT INTO t1 VALUES("4", "dd");
#
# Table structure for table 't2'
#CREATE TABLE t2 (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;#
# Dumping data for table 't2'
#INSERT INTO t2 VALUES("1", "aa");
INSERT INTO t2 VALUES("2", "aa");
INSERT INTO t2 VALUES("3", "bb");
INSERT INTO t2 VALUES("4", "bb");
mysql> SELECT t1.* FROM t1 LEFT JOIN t2
> ON t1.name = t2.name
> WHERE t2.id IS NULL;
+----+------+
| id | name |
+----+------+
| 3 | cc |
| 4 | dd |
+----+------+
2 rows in set (0.00 sec)
还有一点,我说的是not in 没有问题,in 有问题
这一句是什么意思??
mysql> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
4 rows in set (0.03 sec)mysql> SELECT * FROM t2;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | aa |
| 3 | bb |
| 4 | bb |
+----+------+
4 rows in set (0.01 sec)mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name;
+----+------+------+------+
| id | name | id | name |
+----+------+------+------+
| 1 | aa | 1 | aa |
| 1 | aa | 2 | aa |
| 2 | bb | 3 | bb |
| 2 | bb | 4 | bb |
| 3 | cc | NULL | NULL |
| 4 | dd | NULL | NULL |
+----+------+------+------+
6 rows in set (0.02 sec)mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name
-> WHERE t2.id IS NOT NULL;
+----+------+------+------+
| id | name | id | name |
+----+------+------+------+
| 1 | aa | 1 | aa |
| 1 | aa | 2 | aa |
| 2 | bb | 3 | bb |
| 2 | bb | 4 | bb |
+----+------+------+------+
4 rows in set (0.00 sec)mysql> SELECT DISTINCT(t1.id),t1.name FROM t1 LEFT JOIN t2 ON t1.name = t2.name
-> WHERE t2.id IS NOT NULL;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)mysql>
假设t1表中的记录为:(注意第四条记录的name=aa)
1 aa
2 bb
3 cc
4 aa
t2表中的记录是:
1 aa
2 aa
3 bb
4 bb
---------------
这样的话select name from t1 where name in (select name from t2)
的结果是3条记录,而用SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name WHERE t2.id IS NOT NULL 或者SELECT DISTINCT(t1.id),t1.name FROM t1 LEFT JOIN t2 ON t1.name = t2.name WHERE t2.id IS NOT NULL 选出的结果都不跟第一个语句的执行结果一样。
或者这么说吧:
t1,t2 两个表是两项世界纪录的变动纪录,记录了改写该项纪录的人和时间等信息
那么我想统计曾经改写过t2纪录的人,又改写了t1记录的次数,
就可以写成:
select name,count(name) from t1 where name in (select name from t2) group by name
那么这个语句怎么转换成等效的不带子选择的语句呢??
要么使用子查询,
不过
因为目前 MySQL 4.1 还不是稳定版 ,
所以 ....
:(