一道数据库面试题 一张表包括出库和入库状态:货品名字 出入库 数量A 出库 100A 入库 200A 出库 null求商品A的库存总数 入库减去出库我当时是在MYSQL环境下写的不知道怎么处理空值望各位大虾 帮助 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select SUM(IF(出入库='入库',数量,-数量))from 一张表where 货品名字='A' 处理null,可以用IFNULL函数IFNULL(数量,0) 我在数据库中写下如下代码,其中没有goods_id =4 的货品select sum(ifnull(repertory_number,0)) from repertory where goods_id =4但是写出来仍然是null 这是测试结果,建议你提供实际的测试用例,以及期望的正确结果。mysql> select * from t_bllizard;+-------+------+------+| marno | mflg | qty |+-------+------+------+| A | O | 100 || A | I | 200 || A | O | NULL |+-------+------+------+3 rows in set (0.00 sec)mysql> select sum(if(mflg='I',qty,-qty)) -> from t_bllizard -> where marno='A';+----------------------------+| sum(if(mflg='I',qty,-qty)) |+----------------------------+| 100 |+----------------------------+1 row in set (0.00 sec)mysql> 假如:mysql> select * from t_bllizard;+-------+------+------+| marno | mflg | qty |+-------+------+------+| A | I | 200 |只有入库记录 没有出库,我要求出库存量(入库减去出库)为多少怎么办?谢谢 一样的SQL语句。mysql> delete from t_bllizard;Query OK, 3 rows affected (0.08 sec)mysql> insert into t_bllizard values -> ('A','I',200);Query OK, 1 row affected (0.06 sec)mysql> select * from t_bllizard;+-------+------+------+| marno | mflg | qty |+-------+------+------+| A | I | 200 |+-------+------+------+1 row in set (0.00 sec)mysql> select sum(if(mflg='I',qty,-qty)) -> from t_bllizard -> where marno='A';+----------------------------+| sum(if(mflg='I',qty,-qty)) |+----------------------------+| 200 |+----------------------------+1 row in set (0.00 sec)mysql> MYSQL存储过程删除重复数据,高手速度来 关于mysql索引块, 键缓冲区块, 键缓冲区的疑问 mysqldump 导入但导入不完全 employees数据库样例的导入问题 为什么指定索引更新数据也会造成死锁? 数据库事务隔离级别Read Committed和Repeatable Read的区分 pgsql中,如何将查询的结果导出成excel文件 查询当月数据 请大家帮我看看这个存储过程有什么语法错误.谢谢. 查询统计语句。错误在哪?求大牛指正 急查询数据并把数据放到一个新表里面! MySQL 游标不能循环了
select SUM(IF(出入库='入库',数量,-数量))
from 一张表
where 货品名字='A'
select sum(ifnull(repertory_number,0)) from repertory where goods_id =4
但是写出来仍然是
null
mysql> select * from t_bllizard;
+-------+------+------+
| marno | mflg | qty |
+-------+------+------+
| A | O | 100 |
| A | I | 200 |
| A | O | NULL |
+-------+------+------+
3 rows in set (0.00 sec)mysql> select sum(if(mflg='I',qty,-qty))
-> from t_bllizard
-> where marno='A';
+----------------------------+
| sum(if(mflg='I',qty,-qty)) |
+----------------------------+
| 100 |
+----------------------------+
1 row in set (0.00 sec)mysql>
mysql> select * from t_bllizard;
+-------+------+------+
| marno | mflg | qty |
+-------+------+------+
| A | I | 200 |只有入库记录 没有出库,
我要求出库存量(入库减去出库)为多少怎么办?谢谢
Query OK, 3 rows affected (0.08 sec)mysql> insert into t_bllizard values
-> ('A','I',200);
Query OK, 1 row affected (0.06 sec)mysql> select * from t_bllizard;
+-------+------+------+
| marno | mflg | qty |
+-------+------+------+
| A | I | 200 |
+-------+------+------+
1 row in set (0.00 sec)mysql> select sum(if(mflg='I',qty,-qty))
-> from t_bllizard
-> where marno='A';
+----------------------------+
| sum(if(mflg='I',qty,-qty)) |
+----------------------------+
| 200 |
+----------------------------+
1 row in set (0.00 sec)mysql>