mysql更新表，按价格列排序

Question

我有一个包含如下数据的表格:

Id   Item     Price
1      a       2
2      a       1
3      b       7
4      a       2,3
5      c       66
6      c       50,9
7      b       8,2
8      a       4
9      c       55,2

是否可以创建将为我提供结果的更新查询

 Id   Item     Price      Sort
    1      a       2        2
    2      a       1        1
    3      b       7        1
    4      a       2,3      3
    5      c       66       3
    6      c       50,9     1
    7      b       8,2      2
    8      a       4        4
    9      c       55,2     2

或按商品和价格排序的更好 View

 Id   Item     Price      Sort
    2      a       1        1
    1      a       2        2
    4      a       2,3      3
    8      a       4        4
    3      b       7        1
    8      b       8,2      2
    6      c       50,9     1
    9      c       55,2     2
    5      c       66       3

Answer 1

这是一种方法，尽管它的扩展性不好，您可能需要考虑如何处理关系...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(Id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Item     CHAR(1) NOT NULL
,Price DECIMAL(5,2)
);

INSERT INTO my_table VALUES
(1      ,'a',       2),
(2      ,'a',       1),
(3      ,'b',       7),
(4      ,'a',       2.3),
(5      ,'c',       66),
(6      ,'c',       50.9),
(7      ,'b',       8.2),
(8      ,'a',       4),
(9      ,'c',       55.2);

SELECT x.*
     , COUNT(*) rank 
  FROM my_table x 
  JOIN my_table y 
    ON y.item = x.item 
   AND y.price <= x.price 
 GROUP 
    BY x.id 
 ORDER 
    BY item,rank;
+----+------+-------+------+
| Id | Item | Price | rank |
+----+------+-------+------+
|  2 | a    |  1.00 |    1 |
|  1 | a    |  2.00 |    2 |
|  4 | a    |  2.30 |    3 |
|  8 | a    |  4.00 |    4 |
|  3 | b    |  7.00 |    1 |
|  7 | b    |  8.20 |    2 |
|  6 | c    | 50.90 |    1 |
|  9 | c    | 55.20 |    2 |
|  5 | c    | 66.00 |    3 |
+----+------+-------+------+

当性能成为问题时，传统的响应看起来像这样......

SELECT id
     , item
     , price
     , rank
  FROM 
     ( SELECT *
            , @pitem := @citem
            , @pprice := @cprice
            , @citem := item
            , @cprice := price
            , @rank := IF(@pitem = @citem, IF(@pprice = @cprice,@rank,@rank+1), @rank:=1) rank
         FROM my_table
         , (SELECT @citem := null, @pitem := null, @cprice = null, @pprice = null, @rank := 0) vars
        ORDER 
           BY item,price
     ) x;

+----+------+-------+------+
| id | item | price | rank |
+----+------+-------+------+
|  2 | a    |  1.00 |    1 |
|  1 | a    |  2.00 |    2 |
|  4 | a    |  2.30 |    3 |
|  8 | a    |  4.00 |    4 |
|  3 | b    |  7.00 |    1 |
|  7 | b    |  8.20 |    2 |
|  6 | c    | 50.90 |    1 |
|  9 | c    | 55.20 |    2 |
|  5 | c    | 66.00 |    3 |
+----+------+-------+------+

或者，您也可以在应用程序级代码中处理此类内容