Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 53 to server version: 4.0.12-nt-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use test; Database changed mysql> SELECT DISTINCT(x.id), x.* FROM t1 AS x,t1 AS y -> WHERE x.f1=y.f1 AND y.f3='文五' AND y.f2=0 and x.f2=1; +------+------+------+----+------+ | id | id | f1 | f2 | f3 | +------+------+------+----+------+ | 1 | 1 | A | 1 | 张三 | +------+------+------+----+------+ 1 row in set (0.00 sec)mysql> SELECT DISTINCT(x.id),x.* FROM t1 AS x -> LEFT JOIN t1 AS y ON x.f1=y.f1 -> WHERE x.f1=y.f1 and y.f3='文五' AND y.f2=0 AND x.f2=1; +------+------+------+----+------+ | id | id | f1 | f2 | f3 | +------+------+------+----+------+ | 1 | 1 | A | 1 | 张三 | +------+------+------+----+------+ 1 row in set (0.00 sec)mysql>
# # Table structure for table 't1' #CREATE TABLE `t1` ( `id` tinyint(4) default NULL, `f1` char(2) default NULL, `f2` tinyint(1) unsigned NOT NULL default '0', `f3` char(10) NOT NULL default '', KEY `trid` (`id`) ) TYPE=MyISAM;# # Dumping data for table 't1' #INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("1", "A", "1", "张三"); INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("2", "A", "0", "李四"); INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("3", "A", "0", "文五"); INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("4", "A", "0", "文五"); INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("5", "B", "1", "xxxx");
Your MySQL connection id is 53 to server version: 4.0.12-nt-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use test;
Database changed
mysql> SELECT DISTINCT(x.id), x.* FROM t1 AS x,t1 AS y
-> WHERE x.f1=y.f1 AND y.f3='文五' AND y.f2=0 and x.f2=1;
+------+------+------+----+------+
| id | id | f1 | f2 | f3 |
+------+------+------+----+------+
| 1 | 1 | A | 1 | 张三 |
+------+------+------+----+------+
1 row in set (0.00 sec)mysql> SELECT DISTINCT(x.id),x.* FROM t1 AS x
-> LEFT JOIN t1 AS y ON x.f1=y.f1
-> WHERE x.f1=y.f1 and y.f3='文五' AND y.f2=0 AND x.f2=1;
+------+------+------+----+------+
| id | id | f1 | f2 | f3 |
+------+------+------+----+------+
| 1 | 1 | A | 1 | 张三 |
+------+------+------+----+------+
1 row in set (0.00 sec)mysql>
# Table structure for table 't1'
#CREATE TABLE `t1` (
`id` tinyint(4) default NULL,
`f1` char(2) default NULL,
`f2` tinyint(1) unsigned NOT NULL default '0',
`f3` char(10) NOT NULL default '',
KEY `trid` (`id`)
) TYPE=MyISAM;#
# Dumping data for table 't1'
#INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("1", "A", "1", "张三");
INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("2", "A", "0", "李四");
INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("3", "A", "0", "文五");
INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("4", "A", "0", "文五");
INSERT INTO `t1` (`id`, `f1`, `f2`, `f3`) VALUES("5", "B", "1", "xxxx");