SQL查询以基于交易状态(Char)获取OrderID，transactionID，Status [英] SQL query to fetch OrderID, transactionID, Status based on transaction status which is Char

问题描述

我在下面的表格中希望根据状态(字符)获得最低的交易记录.

I have below tables where I want to get lowest transaction entry based on Status which is Char.

表1(顺序):

OrderID    Product
------------------
   1          A 
   2          B
   3          A

表2(交易):

OrderID   TransactionID    Status
---------------------------------
   1           1           LOW
   1           2           HIGH
   1           3           MID
   2           4           MID
   2           5           HIGH
   3           6           LOW

如何获得状态最低的交易

How can I get transaction with the lowest status

OrderID    Status
-----------------
  1        LOW    
  2        MID    
  3        LOW

推荐答案

一种方法使用row_number():

select t.*
from (select t.*,
             row_number() over (partition by orderid
                                order by instr('LOW,MEDIUM,HIGH', status) as seqnum
      from transaction t
     ) t
where seqnum = 1;

instr()只是将顺序分配给字符串的一种便捷方法.它返回状态在第一个参数中的位置，在这种情况下便于排序.

instr() is just a convenient way to assign an ordering to strings. It returns the position of the status in the first argument, which is convenient for sorting purposes in this case.

这篇关于SQL查询以基于交易状态(Char)获取OrderID，transactionID，Status的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！