sql - 如何从行获取 SQL 结果 View 以分隔列

Question

我有一个返回结果的复杂选择查询:

  1_id   2_id   3_id    4_id   CultureId
value1  value2  value3    a       1
value1  value2  value3    b       2

我需要以这样的格式获得结果:

  1_id   2_id   3_id    4_id_1   4_id_2  
value1  value2  value3    a        b       

我能够想出这一点，但也许有更好的方法来实现这一目标？

SELECT 1_id, 2_id, 3_id, (SELECT 4_id from table where 1_id = value1 and CultureId = 1) as 4_id_1, 
                         (SELECT 4_id from table where 1_id = value1 and CultureId = 2) as 4_id_2 
from table where 1_id = value1  

问题是我在解决方案中使用了 3 次的实际内部 SELECT 查询(“SELECT 1_id, 2_id, 3_id, 4_id CultureId from table where 1_id = value1”)是一个复杂的查询，并且包含的​​连接很少，使该解决方案变得巨大。
有没有办法简化它？

Answer 1

您可以聚合和使用 min()和 max() :

select id_1, id_2, id_3,
       min(cultureId) as cultureid_1,
       nullif(max(cultureId), min(cultureId)) as cultureid_2
from t
group by id_1, id_2, id_3;