有tab1,有3列货物名称,今天到多少,顺序
goodname,arrival_t,order
gd1,30,1
gd1,40,2
gd1,50,3
gd2,10,1
gd2,11,2
gd3,1,1
gd3,12,2我要取排序最大的gd,取出来的结果集应该是这样的
gd1,50,3
gd2,11,2
gd3,12,2不知道mysql用一条语句怎么取,oracle可以用分区排序后取,mysql好像没这个函数
goodname,arrival_t,order
gd1,30,1
gd1,40,2
gd1,50,3
gd2,10,1
gd2,11,2
gd3,1,1
gd3,12,2我要取排序最大的gd,取出来的结果集应该是这样的
gd1,50,3
gd2,11,2
gd3,12,2不知道mysql用一条语句怎么取,oracle可以用分区排序后取,mysql好像没这个函数
select * from tab1 t
where not exists
(select 1 from tab1 where goodname=t1.goodname and arrival_t>t.arrival_t);
+----------+-----------+-------+
| goodname | arrival_t | order |
+----------+-----------+-------+
| gd1 | 30 | 1 |
| gd1 | 40 | 2 |
| gd1 | 50 | 3 |
| gd2 | 10 | 1 |
| gd2 | 11 | 2 |
| gd3 | 1 | 1 |
| gd3 | 12 | 2 |
+----------+-----------+-------+
7 rows in set (0.00 sec)mysql> select a.*
-> from tab1 a inner join (
-> select goodname,max(`order`) as max_order
-> from tab1
-> group by goodname
-> ) b on a.goodname=b.goodname and a.order=b.max_order;
+----------+-----------+-------+
| goodname | arrival_t | order |
+----------+-----------+-------+
| gd1 | 50 | 3 |
| gd2 | 11 | 2 |
| gd3 | 12 | 2 |
+----------+-----------+-------+
3 rows in set (0.00 sec)mysql>
from tab1 t
where
not exists(select 1 from tab1 where goodname=t.goodname and `order`>t.`order`)--或者select *
from tab1 t
where `order`=(select max(`order`) from tab1 where goodname=t.goodname)