怎样排除相同的项 select onefrom test a left join test b on a.one = b.onewhere b.two is null;没试过,不知对不对. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select onefrom test a left join test b on a.one = b.twowhere b.one is null;测试过,没有问题. mysql自身还真不好实现,或许你需要借助点成语言进行两次查询,如:String condition = "";boolean firstFlag = false;query = "select distinct two from test";rs = stmt.executeQuery(query);while(rs.next()){if(firstFlag) {condition = condition + "," + rs.getString(two);}else {firstFlag = true;condition = rs.getString(two);}}query = "select * from test where one not in(" + condition + ")";rs = stmt.executeQuery(query);while(rs.next()){System.out.println(rs.getString("one") + " " + rs.getString("two"));} 试试EXCEPTselect one from test except select two from test; 想到了两个方法:1、用left join实现mysql> select * from test;+-----+-----+| one | two |+-----+-----+| 1 | 2 || 2 | 3 || 4 | 1 || 5 | 0 || 6 | 4 |+-----+-----+5 rows in set (0.00 sec)mysql> select a.* -> from test a left join test b -> on a.one = b.two -> left join test c on a.two=c.one -> where b.one is null and c.two is null;+-----+-----+| one | two |+-----+-----+| 5 | 0 |+-----+-----+1 row in set (0.00 sec)2、用临时表实现mysql> select * from test;+-----+-----+| one | two |+-----+-----+| 1 | 2 || 2 | 3 || 4 | 1 || 5 | 0 || 6 | 4 |+-----+-----+5 rows in set (0.00 sec)mysql> create temporary table tmp select * from test;Query OK, 5 rows affected (0.06 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from tmp;+-----+-----+| one | two |+-----+-----+| 1 | 2 || 2 | 3 || 4 | 1 || 5 | 0 || 6 | 4 |+-----+-----+5 rows in set (0.00 sec)mysql> update tmp a,test b set a.one='delete' where a.one=b.two;Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from tmp;+--------+-----+| one | two |+--------+-----+| delete | 2 || delete | 3 || delete | 1 || 5 | 0 || 6 | 4 |+--------+-----+5 rows in set (0.02 sec)mysql> update tmp a,test b set a.one='delete' where a.two=b.one;Query OK, 1 row affected (0.00 sec)Rows matched: 3 Changed: 1 Warnings: 0mysql> select * from tmp;+--------+-----+| one | two |+--------+-----+| delete | 2 || delete | 3 || delete | 1 || 5 | 0 || delete | 4 |+--------+-----+5 rows in set (0.00 sec)mysql> delete from tmp where one='delete';Query OK, 4 rows affected (0.00 sec)mysql> select * from tmp;+-----+-----+| one | two |+-----+-----+| 5 | 0 |+-----+-----+1 row in set (0.00 sec) 多行合并为一行 MySQL用户定义完整性--如何使插入的值符合一定的规则 棘手问题!关于.frm文件的问题! 求教特殊汉字写入数据库的问题 在使用mysql query browser 时报错,继续帮助! 请问如何将excel文件导入mysql? 请教MySQL 高手: 如何实现下面的查询 ?????? 在线等待 删除mysql php给is传递字符串变量 系统 重装后 MYSQL 数据库 还原 帮忙呀!刚装一个mysql想学一下,create database mydb;就出错了………… 新手上路,请大虾推荐几本经典的书!!
from test a left join test b
on a.one = b.two
where b.one is null;
测试过,没有问题.
boolean firstFlag = false;
query = "select distinct two from test";
rs = stmt.executeQuery(query);
while(rs.next()){
if(firstFlag) {
condition = condition + "," + rs.getString(two);
}
else {
firstFlag = true;
condition = rs.getString(two);
}
}query = "select * from test where one not in(" + condition + ")";
rs = stmt.executeQuery(query);
while(rs.next()){
System.out.println(rs.getString("one") + " " + rs.getString("two"));
}
1、用left join实现mysql> select * from test;
+-----+-----+
| one | two |
+-----+-----+
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
| 5 | 0 |
| 6 | 4 |
+-----+-----+
5 rows in set (0.00 sec)mysql> select a.*
-> from test a left join test b
-> on a.one = b.two
-> left join test c on a.two=c.one
-> where b.one is null and c.two is null;
+-----+-----+
| one | two |
+-----+-----+
| 5 | 0 |
+-----+-----+
1 row in set (0.00 sec)2、用临时表实现mysql> select * from test;
+-----+-----+
| one | two |
+-----+-----+
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
| 5 | 0 |
| 6 | 4 |
+-----+-----+
5 rows in set (0.00 sec)mysql> create temporary table tmp select * from test;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select * from tmp;
+-----+-----+
| one | two |
+-----+-----+
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
| 5 | 0 |
| 6 | 4 |
+-----+-----+
5 rows in set (0.00 sec)mysql> update tmp a,test b set a.one='delete' where a.one=b.two;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from tmp;
+--------+-----+
| one | two |
+--------+-----+
| delete | 2 |
| delete | 3 |
| delete | 1 |
| 5 | 0 |
| 6 | 4 |
+--------+-----+
5 rows in set (0.02 sec)mysql> update tmp a,test b set a.one='delete' where a.two=b.one;
Query OK, 1 row affected (0.00 sec)
Rows matched: 3 Changed: 1 Warnings: 0mysql> select * from tmp;
+--------+-----+
| one | two |
+--------+-----+
| delete | 2 |
| delete | 3 |
| delete | 1 |
| 5 | 0 |
| delete | 4 |
+--------+-----+
5 rows in set (0.00 sec)mysql> delete from tmp where one='delete';
Query OK, 4 rows affected (0.00 sec)mysql> select * from tmp;
+-----+-----+
| one | two |
+-----+-----+
| 5 | 0 |
+-----+-----+
1 row in set (0.00 sec)