WITH cars AS (SELECT '1' carid,'东风' carname, '11' driveraid, '12' driverbid FROM dual), driver AS (SELECT '11' driverid,1 carnum,'aaa' drivername FROM dual UNION SELECT '12' driverid,1 carnum,'bbb' drivername FROM dual ) SELECT CARID, CARNAME, (SELECT DRIVERNAME || '(' || DRIVERID || ')' FROM DRIVER K WHERE K.DRIVERID = CARS.DRIVERAID AND K.CARNUM = CARS.CARID), (SELECT DRIVERNAME || '(' || DRIVERID || ')' FROM DRIVER K WHERE K.DRIVERID = CARS.DRIVERBID AND K.CARNUM = CARS.CARID) FROM CARS
with driver as ( select '11' as driverid,'1' as carnum,'aaa' as drivername from dual union all select '12' as driverid,'1' as carnum,'bbb' as drivername from dual ), car as ( select '1' as carid,'東風' as carname,'11' as driveraid,'12' as driverbid from dual ) select a.carid, a.carname, b.drivername as driveraname, c.drivername as driverbname from car a left join driver b on a.driveraid = b.driverid and a.carid = b.carnum left join driver c on a.driverbid = c.driverid and a.carid = c.carnum
WITH cars AS
(SELECT '1' carid,'东风' carname, '11' driveraid, '12' driverbid FROM dual),
driver AS
(SELECT '11' driverid,1 carnum,'aaa' drivername FROM dual UNION
SELECT '12' driverid,1 carnum,'bbb' drivername FROM dual )
SELECT CARID,
CARNAME,
(SELECT DRIVERNAME || '(' || DRIVERID || ')'
FROM DRIVER K
WHERE K.DRIVERID = CARS.DRIVERAID
AND K.CARNUM = CARS.CARID),
(SELECT DRIVERNAME || '(' || DRIVERID || ')'
FROM DRIVER K
WHERE K.DRIVERID = CARS.DRIVERBID
AND K.CARNUM = CARS.CARID) FROM CARS
select '11' as driverid,'1' as carnum,'aaa' as drivername from dual
union all
select '12' as driverid,'1' as carnum,'bbb' as drivername from dual
),
car as (
select '1' as carid,'東風' as carname,'11' as driveraid,'12' as driverbid from dual
)
select a.carid,
a.carname,
b.drivername as driveraname,
c.drivername as driverbname
from car a
left join driver b on a.driveraid = b.driverid
and a.carid = b.carnum
left join driver c on a.driverbid = c.driverid
and a.carid = c.carnum