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
Structure of the----table ' Dog_giftnumrecord '--create table IF not EXISTS ' Dog_giftnumrecord ' (' id ' int (one) unsigned not NULL auto_incre ment COMMENT ' prop count table id ', ' giftid ' int (one) NOT NULL COMMENT ' item ID ', ' receiveid ' int (one) not NULL COMMENT ' recipient ID ', ' type ' Int (one) not null COMMENT ' prop corresponding type (1 corresponding member, 2 for dog) ', ' num ' int (one) not null COMMENT ' number of props ', ' createdate ' int (one) not null COM ment ' Create Time ', ' updatedate ' int (one) default NULL COMMENT ' Update Time ', PRIMARY KEY (' id ')) engine=innodb default Charset=utf8 A uto_increment=11;----Dump the data in the table ' 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, 141301 5944), (5, 2, 139, 1, 1, 1413015946, NULL), (6, 1, 140, 1, 3, 1413015961, 1413015963), (7, 2, 140, 1, 2, 1413015965, 14130159 (8, 1, 141, 1, 3, 1413015972, 1413015973), (9, 1, 142, 1, 1, 1413015978, NULL), (10, 2, 142, 1, 8, 1413015980, 141301825 0);
Its form is ifnull (fielda,fieldb), meaning that when the field Fielda is null, the FIELDB is taken, not NULL when the value of Fielda is taken.
A usage of MySQL ifnull