sql - 在 DISTINCT SELECT 中组合 Oracle SQL 中的结果

Question

我有这个要求，我选择了不同的 order_no、code、display_order、level、description，然后匹配它的模式。

我目前有这条sql语句

 SELECT DISTINCT order_no, code, disp_order, lvl, description, 
CASE WHEN pattern='1' THEN supplier_code ELSE NULL END AS pattern1,
CASE WHEN pattern='2' THEN supplier_code ELSE NULL END AS pattern2,
CASE WHEN pattern='3' THEN supplier_code ELSE NULL END AS pattern3, 
CASE WHEN pattern='4' THEN supplier_code ELSE NULL END AS pattern4, 
CASE WHEN pattern='5' THEN supplier_code ELSE NULL END AS pattern5  
from tbl_pattern

这给了我这个结果集

+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| order_no | code | disp_order | lvl | description | pattern1 | pattern2 | pattern3 | pattern4 | pattern5 |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |          |          |          |          |   0821   |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |   1021   |          |          |          |          |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |          |   4547   |          |          |          |
| RM001-01 | 1    | 2          | 3   | HK140904-1C |   4547   |          |          |          |          |
| RM001-01 | 1    | 3          | 3   | HK140904-1D |          |          |          |   3540   |          |
| RM001-01 | 1    | 4          | 2   | HK140904-1E |          |          |          |          |          |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+

但是我想要实现的是这样的:

+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| order_no | code | disp_order | lvl | description | pattern1 | pattern2 | pattern3 | pattern4 | pattern5 |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |          |          |          |          |   0821   |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |   1021   |   4547   |          |          |          |
| RM001-01 | 1    | 2          | 3   | HK140904-1C |   4547   |          |          |          |          |
| RM001-01 | 1    | 3          | 3   | HK140904-1D |          |          |          |   3540   |          |
| RM001-01 | 1    | 4          | 2   | HK140904-1E |          |          |          |          |          |
+----------+------+------------+-----+-------------+----------+----------+----------+----------+----------+

表格中的示例数据

+----------+------+------------+-----+-------------+---------+---------------+
| order_no | code | disp_order | lvl | description | pattern | supplier_code | 
+----------+------+------------+-----+-------------+---------+---------------+
| RM001-01 | 1    | 0          | 1   | HK140904-1A |     5   |     0821      |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |     1   |     1021      |
| RM001-01 | 1    | 1          | 2   | HK140904-1B |     2   |     4547      |
| RM001-01 | 1    | 2          | 3   | HK140904-1C |     1   |     4547      |
| RM001-01 | 1    | 3          | 3   | HK140904-1D |     4   |     3540      |
| RM001-01 | 1    | 4          | 2   | HK140904-1E |         |               |
+----------+------+------------+-----+-------------+---------+---------------+

Answer 1

像下面这样尝试

SELECT  order_no, code, disp_order, lvl, description, 
    max(CASE WHEN pattern='1' THEN supplier_code ELSE NULL END) AS pattern1,
    max( CASE WHEN pattern='2' THEN supplier_code ELSE NULL END) AS pattern2,
    max(CASE WHEN pattern='3' THEN supplier_code ELSE NULL END ) AS pattern3, 
    max( CASE WHEN pattern='4' THEN supplier_code ELSE NULL END) AS pattern4, 
    max( CASE WHEN pattern='5' THEN supplier_code ELSE NULL END) AS pattern5  
    from tbl_pattern
    group by order_no, code, disp_order, lvl, description