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

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

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

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

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

SQL语句：

SELECT
tempOrder.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) tempOrder
LEFT JOIN
order_detail
ON order_detail.order_d_no = tempOrder.order_no
WHERE 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');