mysql查询

1.提升效率（多表关联查询）查询：

A.左链接查询，左边匹配表数据全部列在结果中。

select a.cMDN,a.cName,a.xxx,b.cMDN,b.fleet_id,c.group,c.xxx from phr_history a left join phr_subscribe b on a.cMDN=b.cMDN left join phr_fleet c b.fleet_id = c.fleet_id;

其中a表示最左边的表（phr_history ）的别名，b表示第二个左边表（phr_subscribe ）的别名，c表示三个表（ phr_fleet ）的别名

B.update时使用inter join，update不支持up某个select结果

update phr_licorderhistory inner join phr_subscriber on phr_licorderhistory.cMDN = phr_subscriber.cMDN set phr_licorderhistory.dOperateActivateDate = phr_subscriber.dActiveDate;

C.select跨表查询，以左链接为例子：

select * from PHR_licOrderHistory a left join phr_subscriber b on a.cMDN = b.cMDN left join phr_fleet c on b.nfid = c.nfid where 1 = 1 and c.cFleetName = '弟弟顶顶顶顶' order by a.dOperateActivateDate DESC;

select nfid,count(*) from  phr_subscriber where nFID>=0 and nFID<=2000 group by nfid order by count(*);

***前面显示部分省略*****
|  206 |      387 |
| 1563 |      400 |
|   50 |      422 |
|  749 |      432 |
|   75 |      467 |
|   53 |      480 |
|  103 |      483 |
|  180 |      484 |
|  119 |      500 |
|  185 |      502 |
|  107 |      512 |
|   19 |      520 |
|  106 |      556 |
|  110 |      556 |
|  183 |      558 |
|    4 |      600 |
|  181 |      609 |
|   97 |      625 |
|  105 |      682 |
|  398 |      702 |
|   82 |      744 |
|  395 |      756 |
|  108 |      785 |
|  128 |      785 |
|  104 |      790 |
|   28 |      839 |
|  125 |      855 |
|  397 |      899 |
|   61 |     1003 |
|   41 |     1013 |
|  403 |     1016 |
|   37 |     1044 |
|   42 |     1046 |
|   36 |     1053 |
|   44 |     1193 |
|  182 |     1281 |
|  184 |     1319 |
|  120 |     1404 |
|  307 |     1512 |
|   73 |     1541 |
|   43 |     1556 |
|  202 |     1576 |
|   84 |     2005 |
|  549 |     2920 |
|   31 |     3051 |
|  586 |     9900 |

mysql> select nfid,count(*) from  phr_subscriber where nFID>=0 and nFID<=2000 group by nfid having count(*)>2000 order by count(*);
+------+----------+
| nfid | count(*) |
+------+----------+
|   84 |     2005 |
|  549 |     2920 |
|   31 |     3051 |
|  586 |     9900 |
|    0 |   186353 |
+------+----------+
5 rows in set (0.10 sec)