订单与订单明细一对多查询语句。
需求:查出每个有效订单中的可用订单明细中价格最高的那条订单明细。要求:显示订单号,订单名称,货物名称,货物价格(不为空)
说明: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
最终结果:
附SQL建表语句:
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');