sql,hql等join条件字段为null时,易出现错误以及解决办法
来源:互联网 发布:船舶签证软件下载 编辑:程序博客网 时间:2024/06/01 19:29
1.null=null不成立,null<>null也不成立
2.
运行汇总当字段为null时,出现bug,及解决思路
join的字段为null,不会出现p1.id=p2.id
mysql> select * from yunxing_huizong;
+------+------+------+
| id | cost | year |
+------+------+------+
| 1 | 1 | 2016 |
| 1 | 2 | 2017 |
| NULL | 2 | 2016 |
| NULL | 3 | 2017 |
+------+------+------+
=============================================
汇总
mysql> select id,sum(cost),year from(select p2.id,p2.cost,p1.year from
(select * from yunxing_huizong)p1 left join
(select * from yunxing_huizong)p2 on p1.id=p2.id and p1.year>=p2.year)p group by id,year;
+------+-----------+------+
| id | sum(cost) | year |
+------+-----------+------+
| NULL | NULL | 2016 |
| NULL | NULL | 2017 |
| 1 | 1 | 2016 |
| 1 | 3 | 2017 |
+------+-----------+------+
===============================================
修改bug思路
将id为null的手动赋值为0或unknown,求出最终结果之后,再将id为0或unknown的改为null,即可
mysql> select case when id is NULL then 0 else id end as id,cost,year from yunxing_huizong;
+----+------+------+
| id | cost | year |
+----+------+------+
| 1 | 1 | 2016 |
| 1 | 2 | 2017 |
| 0 | 2 | 2016 |
| 0 | 3 | 2017 |
+----+------+------+
mysql> select id,sum(cost),year from
(select p2.id,p2.cost,p1.year from
(select case when id is NULL then "unkow" else id end as id,cost,year from yunxing_huizong)p1 left join
(select case when id is NULL then "unkow" else id end as id,cost,year from yunxing_huizong)p2 on p1.id=p2.id and p1.year>=p2.year)p group by id,year;
+-------+-----------+------+
| id | sum(cost) | year |
+-------+-----------+------+
| 1 | 1 | 2016 |
| 1 | 3 | 2017 |
| unkow | 2 | 2016 |
| unkow | 5 | 2017 |
+-------+-----------+------+
得到正确结果
mysql> select case when id="unkow" then null else id end as id,`sum(cost)` sum_cost,year from (select id,sum(cost),year from (select p2.id,p2.cost,p1.year from (select case when id is NULL then "unkow" else id end as id,cost,year from yunxing_huizong)p1 left join (select case when id is NULL then "unkow" else id end as id,cost,year from yunxing_huizong)p2 on p1.id=p2.id and p1.year>=p2.year)p group by id,year)pp;
+------+----------+------+
| id | sum_cost | year |
+------+----------+------+
| 1 | 1 | 2016 |
| 1 | 3 | 2017 |
| NULL | 2 | 2016 |
| NULL | 5 | 2017 |
+------+----------+------+
- sql,hql等join条件字段为null时,易出现错误以及解决办法
- SQL字段为null返回0或其他解决办法
- 开发日志:SQL/HQL按外键字段排序,同时能够读取外键为null的数据
- SQL 字段为NULL查询
- ArraryList<T>等中T为int时 出现错误解决,以及java包装类型解释
- sql的join语句转成hql出现的问题
- sql语句 字段update为null
- SQL Server 附加数据库时 出现错误 “无法为请求检索数据,Error:5123 ” 的解决办法
- html显示数据库中的字段内容为sql语句时,出现隐藏了换行等字符的问题解决
- sql求和出现null时赋值为0
- mysql where 条件中的字段有NULL值时的sql语句写法
- mysql where 条件中的字段有NULL值时的sql语句写法
- sql 条件中判断字段值等于NULL
- mySQL给空间字段建立索引出现错误All parts of a SPATIAL index must be NOT NULL的解决办法
- SQL join 和 where 等多条件连用
- hive第一次启动时,常出现的错误以及解决办法
- sql 查询条件为null和datetime类型时用法点滴
- sql查询条件为null的查询语句
- 理解MySQL——索引与优化
- PAT
- html5 实现网页截屏 页面生成图片(源代码)
- Unity PlayMaker插件
- Android开发实例详解之IMF(输入法)(Android SDK Sample—SoftKeyboard)
- sql,hql等join条件字段为null时,易出现错误以及解决办法
- mysql 行级锁的使用
- Java读取文本文件中文乱码问题
- 微信小程序三级联动(数据在我的资源里)
- css+div水平垂直居中布局总结(3种方案)
- 一些常用的小玩意之redis
- 用Thread类创建线程
- 协议、反向传值
- You are using safe update mode and you tried to update a table without a WHERE that uses a KEY colum