A表:
CREATE TABLE `a` (
`aid` int(11) NOT NULL,
`astate` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `a` (`aid`, `astate`) VALUES
(1, 1),
(2, 1),
(3, 1);
B表:
CREATE TABLE `b` (
`bid` int(11) NOT NULL,
`bfield` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `b` (`bid`, `bfield`) VALUES
(1, 'f1'),
(2, 'f2'),
(3, 'f3'),
(4, 'f4');
C表:
CREATE TABLE `c` (
`cid` int(11) NOT NULL,
`aid` int(11) NOT NULL,
`bid` int(11) NOT NULL,
`cvalue` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `c` (`cid`, `aid`, `bid`, `cvalue`) VALUES
(1, 1, 1, 'v11'),
(2, 1, 2, 'v12'),
(3, 1, 3, 'v13'),
(4, 2, 1, 'v21'),
(5, 2, 2, 'v22'),
(6, 3, 1, 'v31');我现在的mysql语句:
SELECT a.aid, b.bid, b.bfield, cvalue
FROM a
LEFT JOIN (b, c)
ON (a.aid = c.aid AND b.bid = c.bid)
ORDER BY a.aid ASC, b.bid ASC得到的结果是:
aid bid bfield cvalue
1 1 f1 v11
1 2 f2 v12
1 3 f3 v13
2 1 f1 v21
2 2 f2 v22
3 1 f1 v31然后希望得出来的数据结构是:
aid bid bfield cvalue
1 1 f1 v11
1 2 f2 v12
1 3 f3 v13
1 4 f4 null
2 1 f1 v21
2 2 f2 v22
2 3 f3 null
2 4 f4 null
3 1 f1 v31
3 2 f2 null
3 3 f3 null
3 4 f4 null
哪位仁兄帮帮忙!
CREATE TABLE `a` (
`aid` int(11) NOT NULL,
`astate` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `a` (`aid`, `astate`) VALUES
(1, 1),
(2, 1),
(3, 1);
B表:
CREATE TABLE `b` (
`bid` int(11) NOT NULL,
`bfield` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `b` (`bid`, `bfield`) VALUES
(1, 'f1'),
(2, 'f2'),
(3, 'f3'),
(4, 'f4');
C表:
CREATE TABLE `c` (
`cid` int(11) NOT NULL,
`aid` int(11) NOT NULL,
`bid` int(11) NOT NULL,
`cvalue` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `c` (`cid`, `aid`, `bid`, `cvalue`) VALUES
(1, 1, 1, 'v11'),
(2, 1, 2, 'v12'),
(3, 1, 3, 'v13'),
(4, 2, 1, 'v21'),
(5, 2, 2, 'v22'),
(6, 3, 1, 'v31');我现在的mysql语句:
SELECT a.aid, b.bid, b.bfield, cvalue
FROM a
LEFT JOIN (b, c)
ON (a.aid = c.aid AND b.bid = c.bid)
ORDER BY a.aid ASC, b.bid ASC得到的结果是:
aid bid bfield cvalue
1 1 f1 v11
1 2 f2 v12
1 3 f3 v13
2 1 f1 v21
2 2 f2 v22
3 1 f1 v31然后希望得出来的数据结构是:
aid bid bfield cvalue
1 1 f1 v11
1 2 f2 v12
1 3 f3 v13
1 4 f4 null
2 1 f1 v21
2 2 f2 v22
2 3 f3 null
2 4 f4 null
3 1 f1 v31
3 2 f2 null
3 3 f3 null
3 4 f4 null
哪位仁兄帮帮忙!
SELECT * FROM a,b
ORDER BY a.aid,bid) aa
LEFT JOIN c ON aa.bid=c.bid AND aa.aid=c.aid
-> from (a , b) left join c on a.aid=c.aid and b.bid=c.bid
-> order by 1,2;
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
| 2 | 1 | f1 | v21 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 3 | 1 | f1 | v31 |
| 3 | 2 | f2 | NULL |
| 3 | 3 | f3 | NULL |
| 3 | 4 | f4 | NULL |
+-----+-----+--------+--------+
12 rows in set (0.01 sec)mysql>
Thanks!
Thanks!