表结构如下
:
`id` int(11) NOT NULL auto_increment,
`brandName` varchar(30) default NULL,
`modelName` varchar(30) default NULL,
`version` varchar(10) default NULL,
`jarPath` varchar(50) default NULL,
`jadPath` varchar(50) default NULL,
`name` varchar(100) default NULL,
`info` varchar(200) default NULL,
PRIMARY KEY (`id`)我需要一句sql,来查出表中的所有记录,但是,目标是 如果jarPath和jadPath都一样,意思是 distinct(jarPath,jadPath)---这个语法是错的..怎么组合sql语句啊?
:
`id` int(11) NOT NULL auto_increment,
`brandName` varchar(30) default NULL,
`modelName` varchar(30) default NULL,
`version` varchar(10) default NULL,
`jarPath` varchar(50) default NULL,
`jadPath` varchar(50) default NULL,
`name` varchar(100) default NULL,
`info` varchar(200) default NULL,
PRIMARY KEY (`id`)我需要一句sql,来查出表中的所有记录,但是,目标是 如果jarPath和jadPath都一样,意思是 distinct(jarPath,jadPath)---这个语法是错的..怎么组合sql语句啊?
from 表
a b c
1 1 2
1 2 4
1 2 3a,b 这两条都为1 2 的记录你想怎么处理?建议象上面一样给一些例子,然后同时给出你希望得到的正确结果。这样方便理解你的问题。
我开始没描述清楚../
应该是 根据 jarPath和jadPath 去重..
谢谢..
1 1 2
1 2 4
1 2 3
这种情况下,去掉哪一条呢?还是任意去掉一条?
a 代表 jarPath ,b代表 jadpath的话.
就去除第2条或者第3条都可以..
没有特殊需求!!
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 4 |
| 1 | 2 | 3 |
+------+------+------+
3 rows in set (0.00 sec)mysql> delete a from yourTable a ,yourTable b where a.a=b.a and a.b=b.b and a.c<b.c;
Query OK, 1 row affected (0.09 sec)mysql> select * from yourTable;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 4 |
+------+------+------+
2 rows in set (0.00 sec)mysql>
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 4 |
| 1 | 2 | 7 |
| 1 | 2 | 3 |
+------+------+------+
4 rows in set (0.00 sec)mysql> select a.*
-> from yourTable a left join yourTable b on a.a=b.a and a.b=b.b and a.c<b.c
-> where b.a is null;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 7 |
+------+------+------+
2 rows in set (0.00 sec)mysql> select * from yourTable a
-> where not exists (select 1 from yourTable where a=a.a and b=a.b and c>a.c);
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 7 |
+------+------+------+
2 rows in set (0.00 sec)mysql>