postgresql 查询重复，多行合并

-- postgresql
-- 替换字符串
UPDATE tmp SET phone = REPLACE(phone, 'myzs', '');
-- 查询替换中间4位为 * 
SELECT CONCAT_WS('****', SUBSTR(phone,1,3), SUBSTR(phone,8)) as new_phone_number FROM tmp;
-- 更新手机号为中间四位为 *
UPDATE tmp set newphone = CONCAT_WS('****', SUBSTR(phone,1,3), SUBSTR(phone,8));

SELECT DISTINCT(newphone) FROM tmp;

SELECT concat_ws(',', phone), newphone FROM tmp WHERE newphone in (

SELECT newphone FROM (
        SELECT count(*) as count, newphone from tmp GROUP BY newphone
) tmp where tmp.count > 1

) GROUP BY newphone;

-- 合并多行记录值进行查询
SELECT     
    newphone
    ,string_agg(phone,',') AS cname
FROM 
    tmp
WHERE
    newphone in (
            SELECT newphone FROM (
                SELECT count(*) as count, newphone from tmp GROUP BY newphone
            ) tmp where tmp.count > 1
        )
GROUP BY 
    newphone;