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
按文章里的表结构运行你的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会报错。 兄弟你再看看
注意:QD为VIEWmysql> 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>
要修改一下: 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即可
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
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会报错。 兄弟你再看看
-> 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>
bfield=f1 那cvalue的值的排序就要根据bfield=f1的时候排序(同样hid要在一起)
bfield=f2 那cvalue的值的排序就要根据bfield=f2的时候排序现在的语句的话还没满足这一点
+-----+-----+--------+--------+
| 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 |
+-----+-----+--------+--------+
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即可