casewhen多条件查询_SQL进阶【union、 joins、case when、以及其他细碎的点】

进阶知识只是随便起的名字，但是最近在实习的时候发现自己已有的SQL知识有时候有些不够严谨，因此希望能总结一下最近遇到的问题，分享给大家。

1、union与union all的一些小知识

union 其实严格的来讲，它的全名叫union distinct，只不过有一些sql引擎不支持union distinct这个说法，但是union的功能其实是等同于union distinct的，将两个表纵向连接以后去重。union all则不去重。

在连接两个表的时候，union/union all期待的是：列名，列数，列数据类型完全对应相同。

千万不要以为union/union all是将第二个表的内容拼接在第一个表的内容后面，其实是混乱排序的，当你使用union/union all以后，可以理解为，里面的所有内容，全部混乱，毫无顺序可言。

因此，可能有的人希望按照某一列来降序排序：

select id,price from a

union all

select id,pricefrom b

order by price desc;

这么写，hive和impala是不支持的，结果一定还是那么混乱。

那么，怎么样可以降序排序呢？ 直接写一个子查询就好。

同样,可能有时候只想返回一些limit过的数据：

select id,price from a

union all

select id,pricefrom b

limit 10；

你可能会期待，返回union all之后的前10行，但是impala和hive还是不支持这么写，还是只能写在子查询里。

2、在用cast 的时候留一个心眼

cast有可能会返回一些null值、也有可能会返回一些截断后的值。

比如，将存储的是字母而不是数字的字符型转化为int型是完全不行的，会返回null。

再比如，我曾经在写一个将毫秒级的timestamp列转化为秒级的，然后再利用from_unixtime()函数，提取其中的小时、分钟等等。

在转化时，需要cast(timestamp/1000 as bigint)，如果as int的话，数据长度会不够。

在hive中，一个数为19.99，cast as int之后，会直接截断小数点，返回19。而如果在mysql中，则会返回小数点四舍五入之后的数。

3、 case when使用时注意

case when 条件1 then 结果1

when 条件2 then 结果2

else 其他 end；

这样一组语法条件，按顺序判断，如果某一数据满足其中一行，则直接跳出case when的判断，不再执行后面的判断。

例如，对于下面这组数据：

结果：

4、join的相关知识

如果只写join不写on的话，默认为笛卡尔积；而如果写了on，则等同于inner join。

left semi join相当于inner join的优化，只返回左边表的内容，它在存在是由于hive中不支持select * from a where a.字段 in (select 字段 from b);这种写法。

join优化;hive假定最后一张表是最大的那个表，因此，在执行表连接操作时，它会尝试将其他表缓存起来，然后扫描最后的那张表进行计算，因此，最好保证查询中表的大小从左到右是依次递增的，当然，也可以利用/*+STRAMTABLE(s)*/来标记，告知查询优化器哪张表是大表。