MySQL数据库与其他数据库的3个常用语法区别（外链接、分组排序row_number() over(partition by ) 、group by和distinct）

MySQL的小巧灵活易用性，和开源性，使得应用面非常广，但是缺点也 比较多，一些常用的sql语句也有差别。以前也遇到过，最近做项目又有体会，还是记录下来省的以后又忘记了。

1、MySQL数据库不支持最常用的外全链接，即无FULL JOIN。只有内连接、左外和右外链接。这个就非常不方便了。一般是通过union all 来实现由左外+右外的null部分或者左null+右外实现。几种具体形象的解释见下图。

2、MySQL没有功能强大的，常用的一些函数，比如应用非常广的分组排序函数row_number() over(partition by ) 就没有。这个时候的解决就比较麻烦，要进行非常多的表关联或者使用设置变量赋值的方法，总之都很麻烦，效率也比较低。不过，如果特殊情况下的分组排序取第一或者最末的记录，也可以通过其他方法实现。这个就是接下来要说的group by 的功能。

3、MySQL中的 group by语法与标准的SQL语法不一样，我们知道常规的 sql，对于group by来说一定要结合聚合函数，而且选择的字段除了聚合函数外，还必须在group by中出现，否则报错。但是mysql却不是。在mysql中扩展了这样的功能
首先对于不加聚合函数的sql来说，它的功能结合了limit来得出结果，仔细想想的时候有点Oracle分析函数的味道，limit的时候得到的并不是 最大最小的值，而是某一下group by结果集中第一行，也就是刚才说的相当与先group by， 然后在每个group by下面进行limit 1。
其次，刚才还说了常规的group by结合聚合函数的时候，选择的字段除了聚合函数外，必须在group by中存在，但是在mysql中不是这样了，它具有隐含字段的功能。对于没有选择的字段，和上面说到的一样，是根据得到的结果集然后根据每个group by 进行limit 1得到的结果。这样的话对于order by排序后的分组，再进行group by就会相当于分组后取最大或者最小的序号了。

但是，要注意，必须是排序完成后再进行group by 的，否则会得不到正确结果。如：

select c.*,min(time_abs) from (
select a.lname,a.ldirection,a.sname,a.busno,a.slno,a.number,a.card_types,a.sdate,a.time_hour,a.time_all,b.price_cut,b.price,b.cut,b.sname as sname_down,b.slno as slno_down,b.time_all time_all_down,abs(TIME_TO_SEC(a.time_all)-TIME_TO_SEC(b.time_all))as time_abs FROM
(select * from ic_inout_tiger_20161031_price WHERE types= '分段制上车') a
inner JOIN 
(select * from ic_inout_tiger_20161031_price WHERE types= '分段制下车') b
on a.lname=b.lname and a.busno=b.busno and a.number=b.number and a.ldirection=b.ldirection 
ORDER BY a.lname,a.ldirection,a.sname,a.busno,a.card_types,a.sdate,a.time_hour,time_all,time_abs ASC
LIMIT 80000 ##要有，否则结果报错。
)c GROUP BY c.lname,c.ldirection,c.sname,c.busno,c.card_types,c.sdate,c.time_hour,c.time_all

就无问题，但是一旦将limit 部分注释掉就会出现错误结果。原因应该是跟mysql内部运行机制有关。这点要注意。正确的结果如下图

去除limit的的话结果如下，后面两列就会不相等，结果错误，如图（不知道csdn能否显示出图来，前面几篇博客老是显示不了）

4、因为以上缘故，使得这个group by和distinc的非常相似。

DISTINCT 实际上和 GROUP BY 操作的实现非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以，DISTINCT 的实现和 GROUP BY 的实现也基本差不多，没有太大的区别，同样可以通过松散索引扫描或者是紧凑索引扫描来实现。

      那DISTINCT 和GROUP BY哪个效率更高？
       DISTINCT操作只需要找出所有不同的值就可以了。而GROUP BY操作还要为其他聚集函数进行准备工作。从这一点上将，GROUP BY操作做的工作应该比DISTINCT所做的工作要多一些。
       但实际上，GROUP BY 效率会更高点，为什么呢？对于DISTINCT操作，它会读取了所有记录，而GROUP BY需要读取的记录数量与分组的组数量一样多，也就是说比实际存在的记录数目要少很多。