56,940
社区成员




CREATE TABLE A(i INT);
INSERT A VALUES (1),(2),(3),(4),(5),(6);
CREATE TABLE B(i INT);
INSERT B VALUES (1),(2),(3);
CREATE TABLE C(i INT);
INSERT C VALUES (4),(5),(6);
mysql> select a.i,ifnull(b.i,-1) ,ifnull(c.i,-1)
-> from a left join b on a.i=b.i
-> left join c on a.i=c.i;
+------+----------------+----------------+
| i | ifnull(b.i,-1) | ifnull(c.i,-1) |
+------+----------------+----------------+
| 1 | 1 | -1 |
| 2 | 2 | -1 |
| 3 | 3 | -1 |
| 4 | -1 | 4 |
| 5 | -1 | 5 |
| 6 | -1 | 6 |
+------+----------------+----------------+
6 rows in set (0.06 sec)
mysql>
Select A.i,if(B.i<>'',B.i,-1) as "B.i",if(c.i<>'',c.i,-1) as "C.i"
From A Left Join B on A.i=B.i
Left JOin C on A.i=C.i
SELECT DISTINCT a.i AS 'a.i',IF(b.i!=a.i,-1,b.i) AS 'b.i',IF(c.i!=a.i,-1,c.i) AS 'c.i'
FROM a,b,c
WHERE a.i=b.i AND a.i=c.i OR a.i=c.i AND a.i!=b.i OR a.i=b.i AND a.i!=c.i