//表
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
解决方案 »
- load data local infile "d:\\中文.txt" into table p4 提示找不到数据,非中文就可以
- mysql_query支持多语句联合操作吗?
- 关于MYSQL5.1中文参考手册
- 用mysqldump 无法恢复自己备份的数据库
- heartbeat这个软件有用过的人吗?
- Mysql-proxy 读写分离 多连接时,ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
- 帮我优化一下SQL语句!谢谢(mysql)
- mysql administrator一直连不上,是怎么回事?
- 请帮帮我的忙吧 !!!
- date_format问题
- 跪求大侠帮忙,解决多客户端访问数据库并发问题
- 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