Mysql在获取结果中返回值为0

Question

我有这两个表，

-- 
-- Table structure for table `t1`
-- 

CREATE TABLE `t1` (
  `pid` varchar(20) collate latin1_general_ci NOT NULL,
  `pname` varchar(20) collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `t1`
-- 

INSERT INTO `t1` VALUES ('p1', 'pro1');
INSERT INTO `t1` VALUES ('p2', 'pro2');

-- --------------------------------------------------------

-- 
-- Table structure for table `t2`
-- 

CREATE TABLE `t2` (
  `pid` varchar(20) collate latin1_general_ci NOT NULL,
  `year` int(6) NOT NULL,
  `price` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- 
-- Dumping data for table `t2`
-- 

INSERT INTO `t2` VALUES ('p1', 2009, 50);
INSERT INTO `t2` VALUES ('p1', 2010, 60);
INSERT INTO `t2` VALUES ('p3', 2007, 200);
INSERT INTO `t2` VALUES ('p4', 2008, 501);

我的查询是，

SELECT *
FROM `t1`
LEFT JOIN `t2` ON t1.pid = t2.pid

获取结果，

pid     pname   pid     year    price
p1      pro1    p1    2009    50
p1      pro1    p1    2010    60
p2      pro2    NULL    NULL    NULL

我的问题是，我想获取价格值为 0 而不是 NULL。我如何编写查询来获取价格值为 0。

提前感谢您的帮助。

Answer 1

需要使用IFNULL

SELECT pid,pname,pid,year,IFNULL(price,0)
  FROM `t1`
  LEFT JOIN `t2` 
    ON (t1.pid = t2.pid)

IFNULL(field_to_test, value_to_show) 当field_to_test字段为null时返回value_to_show，当field_to_test不为null时直接返回。