1.分组合并 ,GROUP_CONACT ,ANY_VALUE
mysql中很多情况都会关闭分组,因为分组之后会造成字段不清晰,不明确,mysql不知道具体应该用那一个,回去出重复的记录,所以大多数情况下我们是关闭分组的,但是有时候又必须要分组查询,此时就需要ANY_VALUE()函数,另外,可以使用GROUP_CONCAT() 拼接字段,同事any_value()包裹会导致字段名变更,需要用as 将其转换为对应的字段
SELECT
GROUP_CONCAT( case ACTION_CODE when 6 then 3 when 3 then 5 else ACTION_CODE end ) as ACTION_CODE,
any_value ( PU_ID ) AS PU_ID,
any_value ( PUP_ID ) AS ID,
any_value ( OBJECT_CODE ) AS OBJECT_CODE,
'1' AS VERSION,
'1' AS ALIVE_FLAG,
'2021-9-8 00:00:00' AS REC_REVISE_TIME,
'1' as IS_OLD_ASYNC
FROM
rfq_project_user_privilege
WHERE
PU_ID IN ( SELECT u.PU_ID FROM rfq_project_user u WHERE REQUEST_ID IN ( SELECT ID FROM rfq_request WHERE UU_CODE in (${uuCode}) AND RFQ_METHOD = '${RFQ_METHOD}' ) )
GROUP BY
PU_ID