mysql线上一些隐患查询sql

开发写了几个语句,觉得查询结果跟逻辑有点不相符,就拿到这里一起分析了下。

 

语句如下:

select tp.title, tp.amount,

ifnull(sum(case when tu.type = 1 then ti.invest_amount else 0 end),0) as aInvestAmount,

ifnull(sum(case when tu.type = 2 then ti.invest_amount else 0 end),0) as bInvestAmount,

ifnull(sum(case when tu.type = 3 then ti.invest_amount else 0 end),0) as cInvestAmount

from t_invest ti join t_user tu on ti.user_id = tu.id join t_project tp on ti.project_id = tp.id

where tp.id = '48346631623950333337353439383060';

其中t_project 中有id:48346631623950333337353439383060,但在t_invest中是没有此project_id的。所以这条语句理论上应该是没有任何输出,但实际上却输出了如下结果:

 

 

为了方便说明此问题,我们来建立如下的表格及数据。

mysql> select * from t1;

+----+-------+

| id | name  |

+----+-------+

|  3 | chen  |

|  1 | zhang |

+----+-------+

2 rows in set (0.00 sec)

 

mysql> select * from t2;

+------+--------+

| id   | course |

+------+--------+

|    2 | math   |

+------+--------+

1 row in set (0.00 sec)

 

然后执行如下语句1:

mysql> select  t1.id,t1.name,t2.id,t2.course,sum(t1.id) from t1 join t2 on t1.id=t2.id where t1.id=1 ;

+----+-------+------+--------+------------+

| id | name  | id   | course | sum(t1.id) |

+----+-------+------+--------+------------+

|  1 | zhang | NULL | NULL   |       NULL |

+----+-------+------+--------+------------+

1 row in set (0.00 sec)

发现竟然有输出,再执行如下的语句2:

mysql> select  t1.id,t1.name,t2.id,t2.course,sum(t1.id) from t1 join t2 on t1.id=t2.id where t1.id in(1,null) ;

+----+------+------+--------+------------+

| id | name | id   | course | sum(t1.id) |

+----+------+------+--------+------------+

| NULL | NULL | NULL | NULL   |       NULL |

+----+------+------+--------+------------+

1 row in set (0.00 sec)

发现全部为null。

执行如下语句3,返回Empty set。

mysql> select  t1.id,t1.name,t2.id,t2.course,sum(t1.id) from t1 join t2 on t1.id=t2.id where t1.id=1 group by t1.id;

Empty set (0.00 sec)

 

mysql中的语法并不是特别的严格,语句1与语句2其实在oracle中是会报语法检查不通过的。会报:ORA-00937: 不是单组分组函数

 

一般情况下用到聚合函数一般得加上group by会比较严格些,而出来这样的状况只有在有join的时候出来,单表查询还是没问题的,联合表查询聚合函数有使用的话推荐用语句3的写法。

posted @ 2016-02-25 12:00  泽锦  阅读(458)  评论(0编辑  收藏  举报