mysql - 订单行并显示订单号

Question

我已经有一个查询，其结果如下

+-----------+------------+---------------+--------+
| DetailsNo |  SaleDate  | Country       | Value  |
+-----------+------------+---------------+--------+
|       277 | 2012-02-10 | PHI           |     42 |
|       279 | 2012-02-10 | PHI           |     10 |
|       280 | 2012-02-10 | USA           |     30 |
|       281 | 2012-02-10 | USA           |     25 |
|       282 | 2012-02-10 | FRA           |     65 |
|       283 | 2012-02-10 | FRA           |     36 |
|       284 | 2012-02-10 | GER           |     47 |
+-----------+------------+---------------+--------+

我需要的是添加另一列并显示一种订单号，如下所示:

+-----------+------------+---------------+--------+--------+
| DetailsNo |  SaleDate  | Country       | Value  | Order  |
+-----------+------------+---------------+--------+--------+
|       277 | 2012-02-10 | PHI           |     42 |      1 |
|       279 | 2012-02-10 | PHI           |     10 |      2 |
|       280 | 2012-02-10 | USA           |     30 |      1 |
|       281 | 2012-02-10 | USA           |     25 |      2 |
|       282 | 2012-02-10 | FRA           |     65 |      1 |
|       283 | 2012-02-10 | FRA           |     36 |      2 |
|       284 | 2012-02-10 | GER           |     47 |      1 |
+-----------+------------+---------------+--------+--------+

这可能吗？

Answer 1

您可以为此使用变量:

SELECT DetailsNo, SaleDate, Country, Value,
       @rn := IF(@c = Country, @rn + 1,
                 IF(@c := Country, 1, 1)) AS 'Order'
FROM mytable
CROSS JOIN (SELECT @rn := 0, @c := '') AS vars
ORDER BY Country, DetailsNo

Demo here