连接具有不同条件的同一张表的列-SQL

小编典典

连接具有不同条件的同一张表的列-SQL

sql

我能够用一个条件进行查询（如下图所示），但是当我使用几个条件查询并尝试使用不同条件创建同一表的两个相同列时，我没有任何结果。第二张图片是我无法检索到的预期结果。任何输入都受到高度赞赏。谢谢。

select save_line from save_output_table  
where execution_id in ('292') 
  and seq_id = '0' 
  and save_type='R' 
order by line_id ASC

+------------+
| Save_line  |
+------------+
|         17 |
|         22 |
|         23 |
+------------+



SELECT 
( select save_line 
  from save_output_table
  where execution_id in ('292') 
    and seq_id = '0' 
    and save_type='R' 
    order by line_id ASC 
) as save_line1,
( select save_line 
  from save_output_table  
  where execution_id in ('286') 
    and seq_id = '0' 
    and save_type='R' 
    order by line_id ASC 
) as save_line2
from save_output_table

 +-------------+------------+
 | Save_line 1 | Save_line2 |
 +-------------+------------+
 |          17 |          9 |
 |          22 |          5 |
 |          23 |          3 |
 +-------------+------------+

样本数据：

 +---------------+--------+-----------+---------+-----------+
 | execution_id  | seq_id | save_type | line_id | save_line |
 +---------------+--------+-----------+---------+-----------+
 |           286 |      0 | R         |       1 |        17 |
 |           286 |      0 | R         |       2 |        22 |
 |           286 |      0 | R         |       3 |        23 |
 |           286 |      0 | D         |       1 |        17 |
 |           286 |      0 | D         |       2 |        22 |
 |           286 |      0 | D         |       3 |        23 |
 |           292 |      0 | R         |       1 |         9 |
 |           292 |      0 | R         |       2 |         5 |
 |           292 |      0 | R         |       3 |         3 |
 |           292 |      0 | D         |       1 |        98 |
 |           292 |      0 | D         |       2 |       622 |
 |           292 |      0 | D         |       3 |       273 |
 +---------------+--------+-----------+---------+-----------+

阅读 189

2021-04-15

共1个答案

小编典典

因此，如果要按line_id的顺序排列save_line的列表，并根据save_type和execution_id在不同的列中，则需要进行透视。您可以通过几种不同的方法来执行此操作。无论您使用哪种SQL风格，都可以使用以下夫妇：

SELECT line_id, 
max(CASE WHEN execution_id = '292' and save_type = 'R' then save_line end) R_292,
max(CASE WHEN execution_id = '286' and save_type = 'R' then save_line end) R_286
FROM save_output_table
GROUP BY line_id

或者

SELECT t1.save_line save_line1,
t2.save_line save_line2
FROM 
    (SELECT *
    FROM save_output_table
    WHERE save_type = 'R'
    and execution_id = '292'
    ) t1

JOIN (SELECT *
    FROM save_output_table
    WHERE save_type = 'R'
    and execution_id = '286'
    ) t2
ON t1.line_id = t2.line_id

注意：对于第二个选项，如果每个条件的line_id数目相同，则联接仅给出完整列表。如果没有，则应将其更改为FULL OUTER
JOIN，这在MySQL和其他版本中将不起作用。

2021-04-15