mysql - 连接两个表，按两个不同的列输入和排序

Question

我有两个表:

bsl_prioritas_perasat                 rek_perasat
+=========+========+=====+            +=======+==========+======+
|RUPER    |CODE    |SORT |            |CODE   |NAME      |SUB   |
+=========+========+=====+            +=======+==========+======+
|CB2RA    |R0004   |1    |            |R0004  |JASA 1    |AKA   |
|CB2RA    |R0006   |2    |            |R0006  |JASA 2    |AKB   |
|CB3RI    |R0006   |1    |            |R0008  |JASA VK   |VK    |
|CB3RI    |R0008   |2    |            |R0026  |ACCOMT    |AKOM  |
+=========+========+=====+            +=======+==========+======+

输入将为RUPER和SUB。

如果bsl_prioritas_perasat上存在RUPER，那么它将按SORT和NAME排序，并且如果 bsl_prioritas_perasat 上的 RUPER 的 >CODE 不存在/NULL，它将放在最后

如果RUPER不存在，它将忽略SORT并按NAME排序

示例:

1st                    2nd                    3rd
RUPER=CB2RA, SUB=AK    RUPER=CB3RI, SUB=AK    RUPER=CB5DB, SUB=AK
Result:                Result:                Result:
+==========+           +==========+           +==========+
|NAME      |           |NAME      |           |NAME      |
+==========+           +==========+           +==========+
|JASA 1    |           |JASA 2    |           |ACCOMT    |
|JASA 2    |           |ACCOMT    |           |JASA 1    |
|ACCOMT    |           |JASA 1    |           |JASA 2    |
+==========+           +==========+           +==========+

我怎样才能得到这个结果？

Answer 1

您应该能够将 LEFT JOIN 与 IFNULL 和默认排序值一起使用，例如:

SELECT name, sort
FROM (SELECT r.NAME, IFNULL(b.SORT, 100),
  FROM rek_perasat AS r LEFT JOIN bsl_prioritas_perasat AS b
    ON r.CODE = b.CODE AND b.RUPER = ?
  WHERE r.SUB LIKE ? + '%');

更新:添加了子选择。