Hotel表的SQL文件
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for hotel
-- ----------------------------
DROP TABLE IF EXISTS `hotel`;
CREATE TABLE `hotel` (
`hotelid` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET gbk DEFAULT NULL,
PRIMARY KEY (`hotelid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `hotel` VALUES ('1', '井冈山大酒店');
INSERT INTO `hotel` VALUES ('2', '南昌春天大酒店');
INSERT INTO `hotel` VALUES ('3', '南昌富豪酒店');
House表的SQL文件
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for house
-- ----------------------------
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
`hotelid` int(11) NOT NULL DEFAULT '0',
`housetitle` varchar(255) CHARACTER SET gbk DEFAULT NULL,
`price` double NOT NULL DEFAULT '0',
PRIMARY KEY (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `house` VALUES ('3', '情侣间', '129');
INSERT INTO `house` VALUES ('2', '标准间', '150');
INSERT INTO `house` VALUES ('3', '双人间', '155');
INSERT INTO `house` VALUES ('1', '双人间', '180');
INSERT INTO `house` VALUES ('1', '豪华间', '230');
INSERT INTO `house` VALUES ('1', '蜜月套房', '800');
INSERT INTO `house` VALUES ('3', '夫妻亲子房', '888');
INSERT INTO `house` VALUES ('2', '海景套房', '1088');
我想统计出 宾馆名字 关键为 '南昌' 并且 房间 价格小于300的宾馆个数。
按照上述查询条件,查询应该为:2
我只需要个数,就是运行查询之后,结果为2
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for hotel
-- ----------------------------
DROP TABLE IF EXISTS `hotel`;
CREATE TABLE `hotel` (
`hotelid` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET gbk DEFAULT NULL,
PRIMARY KEY (`hotelid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `hotel` VALUES ('1', '井冈山大酒店');
INSERT INTO `hotel` VALUES ('2', '南昌春天大酒店');
INSERT INTO `hotel` VALUES ('3', '南昌富豪酒店');
House表的SQL文件
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for house
-- ----------------------------
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
`hotelid` int(11) NOT NULL DEFAULT '0',
`housetitle` varchar(255) CHARACTER SET gbk DEFAULT NULL,
`price` double NOT NULL DEFAULT '0',
PRIMARY KEY (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `house` VALUES ('3', '情侣间', '129');
INSERT INTO `house` VALUES ('2', '标准间', '150');
INSERT INTO `house` VALUES ('3', '双人间', '155');
INSERT INTO `house` VALUES ('1', '双人间', '180');
INSERT INTO `house` VALUES ('1', '豪华间', '230');
INSERT INTO `house` VALUES ('1', '蜜月套房', '800');
INSERT INTO `house` VALUES ('3', '夫妻亲子房', '888');
INSERT INTO `house` VALUES ('2', '海景套房', '1088');
我想统计出 宾馆名字 关键为 '南昌' 并且 房间 价格小于300的宾馆个数。
按照上述查询条件,查询应该为:2
我只需要个数,就是运行查询之后,结果为2
select count(distinct(house.hotelid)) from house inner join hotel on hotel.hotelid=house.hotelid
where house.price<300 and hotel.title like '%南昌%'
上述方法也可以吧。