首页 > 代码库 > 一个关于MYSQL IFNULL的用法

一个关于MYSQL IFNULL的用法

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的值。

一个关于MYSQL IFNULL的用法