分组取最小值

[zhouxuancq]

--建表语句
CREATE TABLE tt (
  _id int(11) NOT NULL AUTO_INCREMENT,
  _key int(11),
  _orderCode int(11),
  PRIMARY KEY (_id)
)
--插入数据
insert into tt(_key ,_orderCode ) values(1,0);
insert into tt(_key ,_orderCode ) values(2,1);
insert into tt(_key ,_orderCode ) values(3,2);
insert into tt(_key ,_orderCode ) values(4,3);
insert into tt(_key ,_orderCode ) values(5,1);
insert into tt(_key ,_orderCode ) values(1,2);
insert into tt(_key ,_orderCode ) values(2,0);
insert into tt(_key ,_orderCode ) values(3,1);
insert into tt(_key ,_orderCode ) values(6,1);

--结果
_id _key _ordercode
1 1 0
4 4 3
5 5 1
7 2 0
8 3 1
9 6 1
按_key分组，取_ordercode最小的值，要与_key对应

[ACMAIN_CHM]

[Quote]按_key分组，取_ordercode最小的值，要与_key对应[/Quote]如果按照你的要求，如果多条记录为最小值，则是应该取出的！

如果只取一条？哪取哪一条？你的规则是什么？你可以按照下面语句的思路解决。

select * from tt t
where not exists (select 1  from tt where _key=t._key and (_ordercode<t._ordercode or _ordercode=t._ordercode and _id <t._id ))

[ACMAIN_CHM]

[Quote]看过了，没有找到相对应的。[/Quote]
#17楼 的看了吗？

select * from (select * from tt order by _orderCode) t group by _key

[rucypli]

mysql> select * from tt A where not exists (select 1 from tt B where A._key=B._key and A._orderCode>B._orderCode) order by _id;
+-----+------+------------+
| _id | _key | _orderCode |
+-----+------+------------+
| 1 | 1 | 0 |
| 4 | 4 | 3 |
| 5 | 5 | 1 |
| 7 | 2 | 0 |
| 8 | 3 | 1 |
| 9 | 6 | 1 |
+-----+------+------------+
6 rows in set (0.00 sec)

[wwwwb]

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

引用 3 楼 的回复:

其中的一种解法如下。

SQL code
select * from tt t
where not exists (select 1 from tt where _key=t._key and _ordercode<t._ordercode)

不行的，如果_ordercode有相同的，还是会出现2条
[/Quote]
贴你的数据

mysql> SELECT * FROM tt;
+-----+------+------------+
| _id | _key | _orderCode |
+-----+------+------------+
|   1 |    1 |          0 |
|   2 |    2 |          1 |
|   3 |    2 |          1 |
|   4 |    3 |          2 |
|   5 |    4 |          3 |
|   6 |    5 |          1 |
|   7 |    1 |          2 |
|   8 |    2 |          0 |
|   9 |    3 |          1 |
|  10 |    6 |          1 |
+-----+------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tt  a WHERE NOT EXISTS(
    -> SELECT 1 FROM tt WHERE a.`_key`=`_key` AND a.`_orderCode`>`_orderCode`
    -> )
    -> ;
+-----+------+------------+
| _id | _key | _orderCode |
+-----+------+------------+
|   1 |    1 |          0 |
|   5 |    4 |          3 |
|   6 |    5 |          1 |
|   8 |    2 |          0 |
|   9 |    3 |          1 |
|  10 |    6 |          1 |
+-----+------+------------+
6 rows in set (0.00 sec)

mysql>

[wwwwb]

mysql> SELECT * FROM tt;
+-----+------+------------+
| _id | _key | _orderCode |
+-----+------+------------+
|   1 |    1 |          0 |
|   2 |    2 |          1 |
|   3 |    2 |          0 |
|   4 |    3 |          2 |
|   5 |    4 |          3 |
|   6 |    5 |          1 |
|   7 |    1 |          2 |
|   8 |    2 |          0 |
|   9 |    3 |          1 |
|  10 |    6 |          1 |
+-----+------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tt  a WHERE NOT EXISTS(
    -> SELECT 1 FROM tt WHERE a.`_key`=`_key` AND (a.`_orderCode`>`_orderCode`
    -> OR a.`_orderCode`=`_orderCode` AND a._id>_id)
    ->
    -> )
    -> ;
+-----+------+------------+
| _id | _key | _orderCode |
+-----+------+------------+
|   1 |    1 |          0 |
|   3 |    2 |          0 |
|   5 |    4 |          3 |
|   6 |    5 |          1 |
|   9 |    3 |          1 |
|  10 |    6 |          1 |
+-----+------+------------+
6 rows in set (0.00 sec)

mysql>

[ACMAIN_CHM]

参考下贴中的多种方法

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

[zhouxuancq]

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

参考下贴中的多种方法

http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
[征集]分组取最大N条记录方法征集，及散分....
[/Quote]
看过了，没有找到相对应的。

[ACMAIN_CHM]

其中的一种解法如下。

select * from tt t
where not exists (select 1  from tt where _key=t._key and _ordercode<t._ordercode)

[zhouxuancq]

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

其中的一种解法如下。

SQL code
select * from tt t
where not exists (select 1 from tt where _key=t._key and _ordercode<t._ordercode)
[/Quote]
不行的，如果_ordercode有相同的，还是会出现2条

[useky]

select _id,_key,min(_orderCode) from tb
group by _key

[useky]

select _id,_key,min(_orderCode) from tt
group by _key