一.行列倒转
以下为需要合并的数据信息:
关键在:
1.使用group by 进行分组
2.使用聚合函数提取数据
3.聚合函数内部使用CASE、WHEN、THEN、END语句,筛出需要的
SQL语句如下:
SELECT
c_id
,MAX(CASE param_ename WHEN 'otheritscPlanformId' THEN param_value ELSE '' END) AS otheritscPlanformId
,MAX(CASE param_ename WHEN 'otheritscPlanId' THEN param_value ELSE '' END) AS otheritscPlanId
,MAX(CASE param_ename WHEN 'otheritscSalt' THEN param_value ELSE '' END) AS otheritscSalt
,MAX(CASE param_ename WHEN 'otheritscUnitId' THEN param_value ELSE '' END) AS otheritscUnitId
,MAX(CASE param_ename WHEN 'otheritscPlanName' THEN param_value ELSE '' END) AS otheritscPlanName
,MAX(CASE param_ename WHEN 'otheritscAlertType' THEN param_value ELSE '' END) AS otheritscAlertType
FROM
base_channel_param
WHERE
c_id in (29,30)
GROUP BY
c_id
二.合并为逗号分隔的字符串形式
如需要合并为逗号分隔的字符串,可以使用函数 string_agg(合并对象,连接字符),如下图:
SQL 语句:
SELECT
c_id
,string_agg(param_cname,',') AS cname
,string_agg(param_value,',') AS value
FROM
base_channel_param
WHERE
c_id in (29,30)
GROUP BY
c_id
以上两个例子的内容中,使用的聚合函数都可以别其他替换,关键的中心思想是,分组后使用聚合函数提取出需要的数据
如第一例子中的 Max(),就可以使用 string_agg() 替换:
SQL语句:
SELECT c_id
,string_agg(CASE param_ename WHEN 'otheritscPlanformId' THEN param_value ELSE '' END, '') AS otheritscPlanformId
,string_agg(CASE param_ename WHEN 'otheritscPlanId' THEN param_value ELSE '' END, '') AS otheritscPlanId
,string_agg(CASE param_ename WHEN 'otheritscSalt' THEN param_value ELSE '' END, '') AS otheritscSalt
,string_agg(CASE param_ename WHEN 'otheritscUnitId' THEN param_value ELSE '' END, '') AS otheritscUnitId
,string_agg(CASE param_ename WHEN 'otheritscPlanName' THEN param_value ELSE '' END, '') AS otheritscPlanName
,string_agg(CASE param_ename WHEN 'otheritscAlertType' THEN param_value ELSE '' END, '') AS otheritscAlertType
FROM
base_channel_param
WHERE
c_id in (29,30)
GROUP BY
c_id
转载个 postgresql----聚合函数 的链接:
https://www.cnblogs.com/alianbog/p/5674838.html