sql - 如何从行获取 SQL 结果视图到单独的列

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  

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

有没有办法简化它？

标签： sqlsql-serverselectsubquery

Answer 1

您可以聚合并使用min()and 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;