查询每个订单中订单明细中只支付了部分或为支付的订单

来源：互联网发布：最新网络名词英文编辑：程序博客网时间：2024/04/27 19:21

/*Navicat MySQL Data TransferSource Server         : oa_dbSource Server Version : 50540Source Host           : localhost:3306Source Database       : test_dbTarget Server Type    : MYSQLTarget Server Version : 50540File Encoding         : 65001Date: 2017-04-20 20:13:23*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `order_detail`-- ----------------------------DROP TABLE IF EXISTS `order_detail`;CREATE TABLE `order_detail` (  `detail_id` int(11) NOT NULL AUTO_INCREMENT,  `detail_name` varchar(30) DEFAULT NULL,  `is_pay` tinyint(1) DEFAULT '0' COMMENT '1表示已经支付，0表示未支付',  `order_id` int(11) DEFAULT NULL COMMENT '关联订单表的id',  PRIMARY KEY (`detail_id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ------------------------------ Records of order_detail-- ----------------------------INSERT INTO `order_detail` VALUES ('1', '红楼梦', '1', '1');INSERT INTO `order_detail` VALUES ('2', '西游记', '0', '1');INSERT INTO `order_detail` VALUES ('3', '三国演义', '0', '1');INSERT INTO `order_detail` VALUES ('4', '水浒传', '0', '1');INSERT INTO `order_detail` VALUES ('5', '苹果', '1', '2');INSERT INTO `order_detail` VALUES ('6', '梨子', '1', '2');-- ------------------------------ Table structure for `order_info`-- ----------------------------DROP TABLE IF EXISTS `order_info`;CREATE TABLE `order_info` (  `o_id` int(11) NOT NULL AUTO_INCREMENT,  `order_name` varchar(50) DEFAULT NULL,  `is_delete` tinyint(1) DEFAULT '0' COMMENT '1表示已删除，0表示没有删除',  PRIMARY KEY (`o_id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Records of order_info-- ----------------------------INSERT INTO `order_info` VALUES ('1', ' 书籍', '0');INSERT INTO `order_info` VALUES ('2', '食品', '0');INSERT INTO `order_info` VALUES ('3', '衣服', '1');

# 查询每个订单中订单明细中只支付了部分或未支付的订单SELECT t.*FROM        (    SELECT o_id, order_name AS '订单名称', is_delete    ,(SELECT COUNT(1) FROM order_detail WHERE is_pay=0 AND order_info.o_id=order_detail.order_id) c    FROM order_info    WHERE is_delete=0    ) tWHERE t.c>0

0 0