//表
CREATE TABLE tt (
`_id` int(11) NOT NULL AUTO_INCREMENT,
`_keyValue` int(11) DEFAULT NULL COMMENT '主键值'
`_orderCode` int(11) DEFAULT NULL COMMENT '排序码',
PRIMARY KEY (`_id`)
)
字段_keyvalue 有重复值的对_keyvalue进行分组,同类取出_ordercode最小的数据行
如:
_id _keyvalue _ordercode
1 1 0
2 2 1
3 1 1
4 3 1
5 2 0结果:
_id _keyvalue _ordercode
1 1 0
4 3 1
5 2 0
解决方案 »
- Undeclared variable: temp
- 请教一下,这个Mysql的表分区应该如何写?
- mysql 正则表达式的问题
- Replication显示建立起来了,实际上数据不同步的问题?
- 关于insert一条新记录的问题!
- MYSQL连接问题
- 求助:mysql中isAfterLast()方法怎么总是false啊!!!!!!!
- 请教各位,如何将mysql数据库的表名和字段名都变成大写的?
- msyql存储过程查询出某条数据在数据库所有表的表名以及所在表的行数
- MySQL for EXCEL 加载不了
- 跪求大侠帮忙,解决多客户端访问数据库并发问题
- JOIN 怎么查询一个分类 和他关联的子物品查询三个 JOIN查询
and a._ordercode>_ordercode
)
贴建表及插入记录的SQL,及要求结果出来看看
--表语句
CREATE TABLE tt (
`_id` int(11) NOT NULL AUTO_INCREMENT,
`_keyValue` int(11) DEFAULT NULL COMMENT '主键值'
`_orderCode` int(11) DEFAULT NULL COMMENT '排序码',
)
--插入数据
INSERT INTO `tt` VALUES (1,1,1);
INSERT INTO `tt` VALUES (2,2,1);
INSERT INTO `tt` VALUES (3,3,1);
INSERT INTO `tt` VALUES (4,1,0);
INSERT INTO `tt` VALUES (6,1,1);
INSERT INTO `tt` VALUES (8,5,1);
INSERT INTO `tt` VALUES (11,8,1);
INSERT INTO `tt` VALUES (12,10,1);
INSERT INTO `tt` VALUES (15,12,1);
INSERT INTO `tt` VALUES (52,2,0);
_id _keyvalue _ordercode
3 3 1
4 1 0
8 5 1
11 8 1
12 10 1
15 12 1
52 2 0
`_id` INT(11) NOT NULL AUTO_INCREMENT,
`_keyValue` INT(11) DEFAULT NULL COMMENT '主键值' ,
`_orderCode` INT(11) DEFAULT NULL COMMENT '排序码',
KEY (`_id`)
)_id _keyvalue _ordercode
3 3 1
4 1 0
8 5 1
11 8 1
12 10 1
15 12 1
52 2 0
上述结果不对吗?
[征集]分组取最大N条记录方法征集,及散分....
from tt
group by _keyvalue
from tt a
left join
( select keyvalue,min(ordercode) as mincode from tt group by ordercode ) as bwhere
a.keyvalue=b.keyvalue
and
a.ordercode <= b.mincode;
from tt a
left join
( select keyvalue,min(ordercode) as mincode from tt group by ordercode ) as b
where
a.keyvalue=b.keyvalue
and
a.ordercode <= b.mincode;
from tt a
left join
( select keyvalue,min(ordercode) as mincode from tt group by keyvalue ) as b
where
a.keyvalue=b.keyvalue
and
a.ordercode <= b.mincode;
写错了 按照keyvalue分组
from tt
group by _keyvalue
from tt a
group by a._keyValue