/* Navicat MySQL Data TransferSource Server : mysql Source Server Version : 60011 Source Host : localhost:3306 Source Database : testTarget Server Type : MYSQL Target Server Version : 60011 File Encoding : 65001Date: 2010-12-27 15:26:52 */SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `category` -- ---------------------------- DROP TABLE IF EXISTS `category`; CREATE TABLE `category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of category -- ------------------------------ ---------------------------- -- Table structure for `product` -- ---------------------------- DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, `cat_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ---------------------------- -- Records of product -- ----------------------------
select * from product where cat_id in (select id from category where name like '电器'
还有我这个并不是模糊查询,查询条件是=而不是like
这里虽然用了like,但是后面的匹配字符串中并没有使用'%',所以这个和'='是一样的!
最好测试数据也写几条进去,和要求的返回结果也贴出来; 看字面理解 觉得你的的记录只需要 连接表后,再在分类表里面取出名字有关电器的记录select * from product join category on product.cat_id=category.id where category.name like '%电器%'
select * from product where cat_id in (select id from category where name like '电器' union select id from category where parent_id in (select id from category where name like '电器'))
每个产品都有分类,分类是单独一张category表,category表中有id(分类id),parent_id(父类id),parent_id和id有关联关系,分类可能有父类,也可能没有。分类id和产品表中的cat_id关联,譬如说我现在要查分类名为“电器”,但电器下又有好多分类譬如家用电器,办公室电器之类的,我要把所有分类或父类的名称为电器的产品全部查出来,就这个意思
/*
Navicat MySQL Data TransferSource Server : mysql
Source Server Version : 60011
Source Host : localhost:3306
Source Database : testTarget Server Type : MYSQL
Target Server Version : 60011
File Encoding : 65001Date: 2010-12-27 15:26:52
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `category`
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of category
-- ------------------------------ ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`cat_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of product
-- ----------------------------
看字面理解 觉得你的的记录只需要 连接表后,再在分类表里面取出名字有关电器的记录select * from product join category
on product.cat_id=category.id where category.name like '%电器%'
select * from product where cat_id in
(select id from category where name like '电器' union select id from category where parent_id in (select id from category where name like '电器'))
是这样的,我这里数据库是公司内部的数据库,在服务器上的,不方便导,其实这个难点就是如何把分类id不是电器但parent_id是电器的数据也给查出来
好了,可以了,这个sql语句写得妙,结贴给分