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的值能按序排列
哪位仁兄帮帮忙!
...全文
145 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
语句里需要两个参数是灵活的(因为程序里带参数进去查询), 就是bfield的项 和从大到小或从小到大(MAX|MIN)||(DESC|ASC)
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
按bfield = 'f1':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 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 |
| 1 | 1 | f1 | v11 |
| 1 | 2 | f2 | v12 |
| 1 | 3 | f3 | v13 |
| 1 | 4 | f4 | NULL |
+-----+-----+--------+--------+


按bfield = 'f2':cvalue 从大到小排
+-----+-----+--------+--------+
| aid | bid | bfield | cvalue |
+-----+-----+--------+--------+
| 2 | 1 | f1 | v31 |
| 2 | 2 | f2 | v22 |
| 2 | 3 | f3 | NULL |
| 2 | 4 | f4 | NULL |
| 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 |
+-----+-----+--------+--------+


按bfield = 'f3':cvalue 从大到小排
+-----+-----+--------+--------+
| 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 |
+-----+-----+--------+--------+
WWWWA 2010-07-08
  • 打赏
  • 举报
回复
贴结果出来看看
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 wwwwa 的回复:]

不能,要根据cvalue在不同的bfield中的大小来排序
[/Quote]
就是能不能改满足你说的这样?
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
好像我描述有误, 比如:
bfield=f1 那cvalue的值的排序就要根据bfield=f1的时候排序(同样hid要在一起)
bfield=f2 那cvalue的值的排序就要根据bfield=f2的时候排序

现在的语句的话还没满足这一点
WWWWA 2010-07-08
  • 打赏
  • 举报
回复
不能,要根据cvalue在不同的bfield中的大小来排序
WWWWA 2010-07-08
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 ylmdy 的回复:]
就是希望能在目前的情况下让bfield灵活起来 可以根据bfield的不同项排序bfield对应的cvalue的值
[/Quote]
不能,要根据cvalue在不同的bfield中的顺序来排序
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
就是希望能在目前的情况下让bfield灵活起来 可以根据bfield的不同项排序bfield对应的cvalue的值
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
还有个问题没注意看, 这样子就不能根据bfield的值排序了? 比如如果我要让cvalue的值的排序在bfield=f1的情况下呢??
wwwwb 2010-07-08
  • 打赏
  • 举报
回复
注意:QD为VIEW

mysql> SELECT a.* FROM qd a
-> LEFT JOIN
-> (
-> SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM (SELECT aid,MAX(cvalue) AS ma F
ROM qd GROUP BY aid) a
-> LEFT JOIN (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) b
-> ON a.ma>=b.ma
-> GROUP BY a.aid,a.ma
-> ORDER BY COUNT(b.aid)) d
-> ON a.aid=d.aid
-> ORDER BY d.dd;
+-----+-----+--------+--------+
| 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 |
+-----+-----+--------+--------+
12 rows in set (0.03 sec)

mysql>
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
OK 我没建视图 现在可以啦! 谢谢!
ylmdy 2010-07-08
  • 打赏
  • 举报
回复
按文章里的表结构运行你的
SELECT a.* FROM qd a
LEFT JOIN
(
SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) a
LEFT JOIN (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) b
ON a.ma>=b.ma
GROUP BY a.aid,a.ma
ORDER BY COUNT(b.aid)) d
ON a.aid=d.aid
ORDER BY d.dd
会报错。 兄弟你再看看
WWWWA 2010-07-08
  • 打赏
  • 举报
回复
QD为VIEW,代码为
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


SELECT a.* FROM qd a
LEFT JOIN
(
SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) a
LEFT JOIN (SELECT aid,MAX(cvalue) AS ma FROM qd GROUP BY aid) b
ON a.ma>=b.ma
GROUP BY a.aid,a.ma
ORDER BY COUNT(b.aid)) d
ON a.aid=d.aid
ORDER BY d.dd
wwwwb 2010-07-08
  • 打赏
  • 举报
回复
要修改一下:
SELECT a.* FROM qd a LEFT JOIN
(SELECT a.aid,a.ma,COUNT(b.aid) AS dd FROM
(SELECT aid,COALESCE(MAX(cvalue),0) AS ma FROM qd WHERE bfield='f3' GROUP BY aid) a
LEFT JOIN (SELECT aid,COALESCE(MAX(cvalue),0) AS ma FROM qd WHERE bfield='f3' GROUP BY aid) b
ON a.ma>=b.ma GROUP BY a.aid,a.ma ORDER BY COUNT(b.aid)) d ON a.aid=d.aid ORDER BY d.dd DESC;

用SP传参数,将 bfield='f3'中的F3修改为F2、F1即可

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧