先把下边的表结构和数据导入数据库

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50540
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50540
File Encoding         : 65001

Date: 2017-08-03 16:24:29
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `books`
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`books_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '预约表主键',
`gc_id` int(11) NOT NULL COMMENT '分类id',
PRIMARY KEY (`books_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES ('1', '1');
INSERT INTO `books` VALUES ('2', '12');
INSERT INTO `books` VALUES ('3', '13');
INSERT INTO `books` VALUES ('4', '14');
INSERT INTO `books` VALUES ('5', '15');
INSERT INTO `books` VALUES ('6', '16');

-- ----------------------------
-- Table structure for `needs`
-- ----------------------------
DROP TABLE IF EXISTS `needs`;
CREATE TABLE `needs` (
`needs_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '需求表主键',
`gc_id` int(11) NOT NULL COMMENT '分类id',
PRIMARY KEY (`needs_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of needs
-- ----------------------------
INSERT INTO `needs` VALUES ('1', '2');
INSERT INTO `needs` VALUES ('2', '21');
INSERT INTO `needs` VALUES ('3', '22');
INSERT INTO `needs` VALUES ('4', '23');
INSERT INTO `needs` VALUES ('5', '24');
INSERT INTO `needs` VALUES ('6', '25');

-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单表主键',
`nb_id` int(11) NOT NULL COMMENT '预约订单id或需求订单id',
`prop` tinyint(4) NOT NULL COMMENT 'prop=0 books表订单 prop=1 needs表订单',
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '1', '0');
INSERT INTO `orders` VALUES ('2', '2', '1');
INSERT INTO `orders` VALUES ('3', '3', '1');
INSERT INTO `orders` VALUES ('4', '4', '0');
INSERT INTO `orders` VALUES ('5', '5', '1');
INSERT INTO `orders` VALUES ('6', '6', '0');
INSERT INTO `orders` VALUES ('7', '7', '0');

sql语句为

SELECT
orders.*, (
CASE
WHEN orders.prop = 1 THEN
needs.gc_id
ELSE
books.gc_id
END
) AS gc_id
FROM
orders AS orders
LEFT JOIN needs AS needs ON needs.needs_id = orders.nb_id
LEFT JOIN books AS books ON books.books_id = orders.nb_id

运行结果我就不上图了,大家运行看看结果。

解读:

orders表
如果prop=1则需要关联needs表orders.nb_id = needs.needs_id;
如果prop=0则需要关联needs表orders.nb_id = books.books_id;
通过这个sql查出orders表所对应的gc_id数值了

如果需要过滤gc_id = 21的数据怎么办呢
只需要在最后加上HAVING gc_id = 21

SELECT
	orders.*, (
		CASE
		WHEN orders.prop = 1 THEN
			needs.gc_id
		ELSE
			books.gc_id
		END
	) AS gc_id
FROM
	orders AS orders
LEFT JOIN needs AS needs ON needs.needs_id = orders.nb_id
LEFT JOIN books AS books ON books.books_id = orders.nb_id
HAVING
	gc_id = 21

这样就可以了,试试看哦。

如未注明,均为原创,转载请注明来自Peak Xin's Blog

No Comments »