一个关于MYSQL IFNULL的用法

最新推荐文章于 2024-09-30 09:09:52 发布

weixin_30703911

最新推荐文章于 2024-09-30 09:09:52 发布

阅读量130

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/jthb/p/4019618.html

版权

select a.receiveID,(a.num - IFNULL(b.num,0)) as num 
from (SELECT num,receiveID from dog_giftnumrecord where giftID = 1) as a 
left join (SELECT num,receiveID from dog_giftnumrecord where giftID = 2 ) as b 
on a.receiveID = b.receiveID

--
-- 表的结构 `dog_giftnumrecord`
--

CREATE TABLE IF NOT EXISTS `dog_giftnumrecord` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '道具计数表ID',
  `giftID` int(11) NOT NULL COMMENT '道具ID',
  `receiveID` int(11) NOT NULL COMMENT '接受者ID',
  `type` int(11) NOT NULL COMMENT '道具对应类型(1对应会员,2对应狗狗)',
  `num` int(11) NOT NULL COMMENT '道具数量',
  `createDate` int(11) NOT NULL COMMENT '创建时间',
  `updateDate` int(11) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- 转存表中的数据 `dog_giftnumrecord`
--

INSERT INTO `dog_giftnumrecord` (`id`, `giftID`, `receiveID`, `type`, `num`, `createDate`, `updateDate`) VALUES
(3, 1, 138, 1, 4, 1413014344, 1413015921),
(4, 1, 139, 1, 2, 1413015942, 1413015944),
(5, 2, 139, 1, 1, 1413015946, NULL),
(6, 1, 140, 1, 3, 1413015961, 1413015963),
(7, 2, 140, 1, 2, 1413015965, 1413015967),
(8, 1, 141, 1, 3, 1413015972, 1413015973),
(9, 1, 142, 1, 1, 1413015978, NULL),
(10, 2, 142, 1, 8, 1413015980, 1413018250);

它的形式是IFNULL(fieldA,fieldB),意义是当字段fieldA是NULL时取fieldB，不是NULL时取fieldA的值。

转载于:https://www.cnblogs.com/jthb/p/4019618.html