mysql语句排序求救。
ylmdy 2010-07-08 10:26:54 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, 'v31 '),
(5, 2, 2, 'v22 '),
(6, 3, 1, 'v21 ');
我现在的mysql语句:
SELECT a.aid, b.bid,b.bfield,c.cvalue
FROM (a, b )
LEFT JOIN 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"
1 4 "f4" NULL
2 1 "f1" "v31"
2 2 "f2" "v22"
2 3 "f3" NULL
2 4 "f4" NULL
3 1 "f1" "v21"
3 2 "f2" NULL
3 3 "f3" NULL
3 4 "f4" NULL
然后希望得出来的数据结构是:
aid bid bfield cvalue
1 1 "f1" "v11"
1 2 "f2" "v12"
1 3 "f3" "v13"
1 4 "f4" NULL
3 1 "f1" "v21"
3 2 "f2" NULL
3 3 "f3" NULL
3 4 "f4" NULL
2 1 "f1" "v31"
2 2 "f2" "v22"
2 3 "f3" NULL
2 4 "f4" NULL
注意红色部分。 主要是想根据不同aid的bfield的相同项目(比如f1)排序,然后保持相同的aid在一起并且cvalue的值能按序排列
哪位仁兄帮帮忙!