一对多带限制条件的SQL语句

订单与订单明细一对多查询语句。

需求：查出每个有效订单中的可用订单明细中价格最高的那条订单明细。要求：显示订单号，订单名称，货物名称，货物价格(不为空)

说明：valid字段表示是否可用：0-不可用，1-可用

订单表和订单明细表如下：订单明细表通多字段order_d_no与订单表order_no关联

SQL语句：

SELECTtempOrder.order_no AS 订单号,tempOrder.order_name AS 订单名称,order_detail.product AS 货物名称,tempOrder.maxPrice AS 货物价格FROM   (SELECT order_no,order_name,(SELECT MAX(price) FROM order_detail WHERE order_detail.order_d_no = `order`.order_no AND order_detail.valid = 1) AS maxPrice   FROM `order`   WHERE `order`.valid = 1) tempOrderLEFT JOINorder_detailON order_detail.order_d_no = tempOrder.order_noWHERE maxPrice IS NOT NULL AND maxPrice = order_detail.price

最终结果：

附ＳＱＬ建表语句：

SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for order-- ----------------------------DROP TABLE IF EXISTS `order`;CREATE TABLE `order` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `order_no` varchar(255) DEFAULT NULL,  `order_name` varchar(255) DEFAULT NULL,  `valid` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `order_no` (`order_no`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;-- ------------------------------ Records of order-- ----------------------------INSERT INTO `order` VALUES ('1', 'X001', '第一单', '1');INSERT INTO `order` VALUES ('2', 'X002', '第二单', '1');

SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for order_detail-- ----------------------------DROP TABLE IF EXISTS `order_detail`;CREATE TABLE `order_detail` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `order_d_no` varchar(255) DEFAULT NULL,  `product` varchar(255) DEFAULT NULL,  `price` decimal(10,0) DEFAULT NULL,  `valid` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `oo` (`order_d_no`),  CONSTRAINT `oo` FOREIGN KEY (`order_d_no`) REFERENCES `order` (`order_no`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of order_detail-- ----------------------------INSERT INTO `order_detail` VALUES ('1', 'X001', '裤子', '158', '1');INSERT INTO `order_detail` VALUES ('2', 'X001', '裤子2', '236', '0');INSERT INTO `order_detail` VALUES ('3', 'X002', '棉袄', '456', '1');INSERT INTO `order_detail` VALUES ('4', 'X001', '鞋子', '198', '1');INSERT INTO `order_detail` VALUES ('5', 'X002', '包包', '1288', '1');