数据分组，去重复记录，急！

[zhouxuancq]

//表
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

[kiyha]

select _keyvalue,min(_ordercode)
from tt
group by _keyvalue

[useky]

select a._keyValue , min(a._ordercode),a._id
from tt a
group by a._keyValue

[WWWWA]

select * from tt a where not exists(select 1 from tt where a._keyvalue=_keyvalue
and a._ordercode>_ordercode
)

[zhouxuancq]

不行啊~ 重得记录是N条啊

[WWWWA]

[Quote=引用 2 楼 的回复:]

不行啊~ 重得记录是N条啊
[/Quote]

贴建表及插入记录的SQL，及要求结果出来看看

[zhouxuancq]

--表语句
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

[zhouxuancq]

[Quote=引用 3 楼 的回复:]

引用 2 楼 的回复:

不行啊~ 重得记录是N条啊


贴建表及插入记录的SQL，及要求结果出来看看
[/Quote]

已贴，帮忙看看~

[WWWWA]

CREATE TABLE tt2 (
`_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
上述结果不对吗？

[ACMAIN_CHM]

参考下贴中的多种方法

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....

[ACMAIN_CHM]

select _id ,  _keyvalue  ,_ordercode
from tt
group by _keyvalue

[zhouxuancq]

[Quote=引用 6 楼 的回复:]

CREATE TABLE tt2 (
`_id` INT(11) NOT NULL AUTO_INCREMENT,
`_keyValue` INT(11) DEFAULT NULL COMMENT '主键值' ,
`_orderCode` INT(11) DEFAULT NULL COMMENT '排序码',
KEY (`_……
[/Quote]
嗯。不对啊

[rucypli]

[Quote=引用 1 楼 的回复:]

select * from tt a where not exists(select 1 from tt where a._keyvalue=_keyvalue
and a._ordercode>_ordercode
)
[/Quote]
这个没问题

[WWWWA]

你要求的结果是什么

[啊啦嘣嘣]

selct a.keyvalue , a.ordercode
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;

[啊啦嘣嘣]

selct a.keyvalue , a.ordercode
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;

[啊啦嘣嘣]

selct a.keyvalue , a.ordercode
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分组