如果ua_pilot有2 ua_type在一行上，我想创建一行。

Question

SELECT
    UA_TYPE, UA_SCHEDULED_START_TIME, ua_pilot_id, 
    ua_assignmentreason_abbrev, ua_scheduled_end_time,  
    UA_CANCEL_TIME,ua_noq_reason
FROM
    unavailabilityassignment
WHERE 
    UA_TYPE IN ('NOQ')
    AND UA_ASSIGNMENT_TIME > '2022-01-01' 
    AND ua_scheduled_start_time  > '2022-01-21'
    AND ua_assignmentreason_abbrev IN 'APMA'

UNION

SELECT
    UA_TYPE, UA_SCHEDULED_START_TIME, ua_pilot_id, 
    ua_assignmentreason_abbrev, ua_scheduled_end_time, 
    UA_CANCEL_TIME,ua_noq_reason
FROM
    unavailabilityassignment
WHERE
    UA_TYPE IN ('SCK')
    AND UA_ASSIGNMENT_TIME > '2022-01-01'
    AND ua_scheduled_start_time > '2022-01-21'

UNION

SELECT
    UA_TYPE, UA_SCHEDULED_START_TIME, ua_pilot_id,  
    ua_assignmentreason_abbrev, ua_scheduled_end_time, 
    UA_CANCEL_TIME,ua_noq_reason
FROM
    unavailabilityassignment
WHERE
    UA_NOQ_REASON IN 'APMA'
    AND ua_scheduled_start_time > '2022-01-21'
ORDER BY
    3

​

​

Answer 1

这取决于您使用的是什么db，但是在选择所需的其他字段时，您可以使用GROUP BY ua_pilot_id并使用类似于MySQL GROUP_CONCAT的函数。例如

SELECT GROUP_CONCAT(UA_TYPE)
...the rest of your query...
GROUP BY ua_pilot_id