php - mysql查询在BETWEEN中查找记录并列出是否存在

Question

您好，我正在尝试进行查询，该查询将运行表中的记录，并且在两年之间工作正常，但我需要列出我正在搜索的所有值 BETWEEN

查询

select SUM(price) FROM orders where year BETWEEN 2004 and 2009 GROUP BY year
 2005 | 200
 2006 | 100
 2008 | 400

i am trying to show like this

 2005 | 200
 2006 | 100
 2007 | 0
 2008 | 400
 2009 | 0
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(10,0) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO orders VALUES ('1', '200', '2005');
INSERT INTO orders VALUES ('2', '100', '2006');
INSERT INTO orders VALUES ('3', '400', '2008');

或者我是否需要在 php 中使用一些循环或其他任何建议来执行此操作谢谢帮助

Answer 1

您需要一个可以外连接的辅助数字表。如果没有永久表，您可以使用派生表。

SELECT   coalesce(SUM(o.price),0) AS price
FROM     (SELECT 2004 AS yr
         UNION ALL
         SELECT 2005
         UNION ALL
         SELECT 2006
         UNION ALL
         SELECT 2007
         UNION ALL
         SELECT 2008
         UNION ALL
         SELECT 2009
         )
         y
         LEFT JOIN orders o
         ON       y.yr = o.year
GROUP BY y.yr