解决方案 »
- 帮个忙 一个ID初始值为000000 然后自动+1
- 如何知道Mysql视图是由那些表组成的?
- 发现 /etc/init.d /mysqld start -----------是失败的 无法启动
- linux下 mysql不能 启动??
- 有没有一个MySQL函数可以获取一个字符在字符串里的数量?
- 合并sql语句
- 可否使用win2000pro+asp+MySQL?
- Decimal类型的字段为什么不能为Null?
- mysql无法启动
- A机器上运行MySQL5.7,想把它的binlog文件,实时(注意:是实时)同步到B机器上,需要怎么做?想用rsync
- insert union all 不能成功执行
- 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
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即可