Hologres这样子的行转列的数据 我一条id为445的过来 但是其余的字段 不想变成空?有什么办法吗 除了每个字段分开写入 ,STRING_AGG(distinct case when t3.id = 413 then t2.name end )
,STRING_AGG(distinct case when t3.id = 418 then t2.name end )
,STRING_AGG(distinct case when t3.id = 421 then t2.name end )
,STRING_AGG(distinct case when t3.id = 423 then t2.name end )
,STRING_AGG(distinct case when t3.id = 425 then t2.name end )
,STRING_AGG(distinct case when t3.id = 428 then t2.name end )
,STRING_AGG(distinct case when t3.id = 438 then t2.name end )
,STRING_AGG(distinct case when t3.id = 440 then t2.name end )
,STRING_AGG(distinct case when t3.id = 443 then t2.name end )
,STRING_AGG(distinct case when t3.id = 445 then t2.name end )
你可以使用COALESCE
函数来处理这个问题。COALESCE
函数会返回第一个非空值,如果所有值都为空,则返回NULL。这样,当某个字段没有对应的值时,它不会变成空。
假设你的表名为t1
,字段名为id
和name
,你可以这样修改你的查询:
SELECT
COALESCE(STRING_AGG(distinct case when t3.id = 413 then t2.name end), '') AS name_413,
COALESCE(STRING_AGG(distinct case when t3.id = 418 then t2.name end), '') AS name_418,
COALESCE(STRING_AGG(distinct case when t3.id = 421 then t2.name end), '') AS name_421,
COALESCE(STRING_AGG(distinct case when t3.id = 423 then t2.name end), '') AS name_423,
COALESCE(STRING_AGG(distinct case when t3.id = 425 then t2.name end), '') AS name_425,
COALESCE(STRING_AGG(distinct case when t3.id = 428 then t2.name end), '') AS name_428,
COALESCE(STRING_AGG(distinct case when t3.id = 438 then t2.name end), '') AS name_438,
COALESCE(STRING_AGG(distinct case when t3.id = 440 then t2.name end), '') AS name_440,
COALESCE(STRING_AGG(distinct case when t3.id = 443 then t2.name end), '') AS name_443,
COALESCE(STRING_AGG(distinct case when t3.id = 445 then t2.name end), '') AS name_445
FROM t1 t2
JOIN t3 ON t2.id = t3.id
GROUP BY t2.id
这样,当某个字段没有对应的值时,它将返回一个空字符串,而不是NULL。
你可以使用COALESCE
函数来处理这个问题。COALESCE
函数会返回第一个非空值,如果所有值都为空,则返回NULL。这样,当某个字段没有对应的值时,它不会变成空。
假设你的表名为t1
,字段名为id
和name
,你可以使用以下查询:
SELECT
COALESCE(STRING_AGG(distinct case when t3.id = 413 then t2.name end), '') AS name1,
COALESCE(STRING_AGG(distinct case when t3.id = 418 then t2.name end), '') AS name2,
COALESCE(STRING_AGG(distinct case when t3.id = 421 then t2.name end), '') AS name3,
COALESCE(STRING_AGG(distinct case when t3.id = 423 then t2.name end), '') AS name4,
COALESCE(STRING_AGG(distinct case when t3.id = 425 then t2.name end), '') AS name5,
COALESCE(STRING_AGG(distinct case when t3.id = 428 then t2.name end), '') AS name6,
COALESCE(STRING_AGG(distinct case when t3.id = 438 then t2.name end), '') AS name7,
COALESCE(STRING_AGG(distinct case when t3.id = 440 then t2.name end), '') AS name8,
COALESCE(STRING_AGG(distinct case when t3.id = 443 then t2.name end), '') AS name9,
COALESCE(STRING_AGG(distinct case when t3.id = 445 then t2.name end), '') AS name10
FROM
t1 t2
JOIN
t3 ON t2.id = t3.id
WHERE
t3.id = 445;
这个查询会根据id
字段的值将name
字段的值聚合到不同的列中。如果某个id
没有对应的name
值,那么对应的列将为空字符串。
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
本技术圈将为大家分析有关阿里云产品Hologres的最新产品动态、技术解读等,也欢迎大家加入钉钉群--实时数仓Hologres交流群32314975