SELECT IFNULL(cr.num-rs.num*10,-1)
FROM resource_stuff rs
LEFT JOIN castle_resource cr
ON (cr.castle_id = 10 AND rs.stuff_id = cr.res_id)
WHERE rs.res_id = 7;+-----------------------------+
| IFNULL(cr.num-rs.num*10,-1) |
+-----------------------------+
| 9223372036854775807 |
| -1 |
+-----------------------------+
2 rows in set, 1 warning (0.00 sec)结果比较奇怪,好像不是我要的负数 呵呵castle_resource中可能没有对应的数据所以 cr.num 可能为null
我的原意是想获取最小的值,并假设cr.num是null的化为0p_num 是一个数字SELECT MIN(IFNULL(cr.num -(rs.num*p_num),-1)) INTO v_min_val
FROM resource_stuff rs
LEFT JOIN castle_resource cr
ON (cr.castle_id = p_castle_id AND rs.stuff_id = cr.res_id)
WHERE rs.res_id = p_res_id;在mysql命令模式中执行报 ERROR 1264 (22003): Out of range value for column 'num' at row 1下面的数据结构#
# Structure for the `castle_resource` table :
#CREATE TABLE `castle_resource` (
`castle_id` int(11) NOT NULL DEFAULT '0',
`res_id` int(11) NOT NULL DEFAULT '0',
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`castle_id`,`res_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# Structure for the `resource_stuff` table :
#CREATE TABLE `resource_stuff` (
`res_id` int(11) NOT NULL DEFAULT '0',
`stuff_id` int(11) NOT NULL DEFAULT '0',
`num` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`res_id`,`stuff_id`),
KEY `res_id` (`res_id`),
KEY `stuff_id` (`stuff_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;#
# Data for the `resource_stuff` table (LIMIT 0,500)
#INSERT INTO `resource_stuff` (`res_id`, `stuff_id`, `num`) VALUES
(6,3,2),
(7,3,1),
(7,4,2),
(8,3,3),
(8,4,2),
(9,3,1),
(9,4,1),
(10,3,5),
(10,4,1),
(11,3,1),
(11,4,2),
(12,1,5);COMMIT;#
# Data for the `castle_resource` table (LIMIT 0,500)
#INSERT INTO `castle_resource` (`castle_id`, `res_id`, `num`) VALUES
(10,1,600737),
(10,2,1846326),
(10,3,0),
(10,6,30);COMMIT;
FROM resource_stuff rs
LEFT JOIN castle_resource cr
ON (cr.castle_id = 10 AND rs.stuff_id = cr.res_id)
WHERE rs.res_id = 7;+-----------------------------+
| IFNULL(cr.num-rs.num*10,-1) |
+-----------------------------+
| 9223372036854775807 |
| -1 |
+-----------------------------+
2 rows in set, 1 warning (0.00 sec)结果比较奇怪,好像不是我要的负数 呵呵castle_resource中可能没有对应的数据所以 cr.num 可能为null
我的原意是想获取最小的值,并假设cr.num是null的化为0p_num 是一个数字SELECT MIN(IFNULL(cr.num -(rs.num*p_num),-1)) INTO v_min_val
FROM resource_stuff rs
LEFT JOIN castle_resource cr
ON (cr.castle_id = p_castle_id AND rs.stuff_id = cr.res_id)
WHERE rs.res_id = p_res_id;在mysql命令模式中执行报 ERROR 1264 (22003): Out of range value for column 'num' at row 1下面的数据结构#
# Structure for the `castle_resource` table :
#CREATE TABLE `castle_resource` (
`castle_id` int(11) NOT NULL DEFAULT '0',
`res_id` int(11) NOT NULL DEFAULT '0',
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`castle_id`,`res_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# Structure for the `resource_stuff` table :
#CREATE TABLE `resource_stuff` (
`res_id` int(11) NOT NULL DEFAULT '0',
`stuff_id` int(11) NOT NULL DEFAULT '0',
`num` int(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`res_id`,`stuff_id`),
KEY `res_id` (`res_id`),
KEY `stuff_id` (`stuff_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;#
# Data for the `resource_stuff` table (LIMIT 0,500)
#INSERT INTO `resource_stuff` (`res_id`, `stuff_id`, `num`) VALUES
(6,3,2),
(7,3,1),
(7,4,2),
(8,3,3),
(8,4,2),
(9,3,1),
(9,4,1),
(10,3,5),
(10,4,1),
(11,3,1),
(11,4,2),
(12,1,5);COMMIT;#
# Data for the `castle_resource` table (LIMIT 0,500)
#INSERT INTO `castle_resource` (`castle_id`, `res_id`, `num`) VALUES
(10,1,600737),
(10,2,1846326),
(10,3,0),
(10,6,30);COMMIT;
FROM resource_stuff rs
LEFT JOIN castle_resource cr
ON (cr.castle_id = 10 AND rs.stuff_id = cr.res_id)
WHERE rs.res_id = 7;
===========================
cr.num rs.num
0 1
null 2结果中包含有个null列
如果不加IFNULL cr.num-rs.num*10 => 0,null;手工计算的结果应该是 -10,null而你看上面 加上IFNULL后成了 9223372036854775807
=====================
这里,rs.num是unsigned,而cr.num是signed,减的时候,换算成了unsigned,而结果是负的,因此出现了错误.
改了就OK了