SQL
/*
Navicat MySQL Data TransferSource Server : localhost
Source Server Version : 50527
Source Host : localhost:3306
Source Database : zhiaiTarget Server Type : MYSQL
Target Server Version : 50527
File Encoding : 65001Date: 2014-11-07 12:17:51
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for za_item_property
-- ----------------------------
DROP TABLE IF EXISTS `za_item_property`;
CREATE TABLE `za_item_property` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`itemId` int(11) DEFAULT NULL,
`propId` int(11) DEFAULT NULL,
`valueId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `za_item_property_itemId` (`itemId`),
KEY `za_item_property_propId` (`propId`),
KEY `za_item_property_valueId` (`valueId`),
CONSTRAINT `za_item_property_itemId` FOREIGN KEY (`itemId`) REFERENCES `za_item` (`id`),
CONSTRAINT `za_item_property_propId` FOREIGN KEY (`propId`) REFERENCES `za_property_name` (`id`),
CONSTRAINT `za_item_property_valueId` FOREIGN KEY (`valueId`) REFERENCES `za_property_value` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of za_item_property
-- ----------------------------
INSERT INTO `za_item_property` VALUES ('1', '4', '1', '1');
INSERT INTO `za_item_property` VALUES ('2', '4', '2', '4');
INSERT INTO `za_item_property` VALUES ('3', '4', '3', '8');
INSERT INTO `za_item_property` VALUES ('4', '6', '1', '3');
INSERT INTO `za_item_property` VALUES ('5', '6', '2', '5');
INSERT INTO `za_item_property` VALUES ('6', '6', '3', '8');
INSERT INTO `za_item_property` VALUES ('9', '5', '2', '4');
INSERT INTO `za_item_property` VALUES ('11', '5', '1', '3');
/*
Navicat MySQL Data TransferSource Server : localhost
Source Server Version : 50527
Source Host : localhost:3306
Source Database : zhiaiTarget Server Type : MYSQL
Target Server Version : 50527
File Encoding : 65001Date: 2014-11-07 12:17:51
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for za_item_property
-- ----------------------------
DROP TABLE IF EXISTS `za_item_property`;
CREATE TABLE `za_item_property` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`itemId` int(11) DEFAULT NULL,
`propId` int(11) DEFAULT NULL,
`valueId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `za_item_property_itemId` (`itemId`),
KEY `za_item_property_propId` (`propId`),
KEY `za_item_property_valueId` (`valueId`),
CONSTRAINT `za_item_property_itemId` FOREIGN KEY (`itemId`) REFERENCES `za_item` (`id`),
CONSTRAINT `za_item_property_propId` FOREIGN KEY (`propId`) REFERENCES `za_property_name` (`id`),
CONSTRAINT `za_item_property_valueId` FOREIGN KEY (`valueId`) REFERENCES `za_property_value` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of za_item_property
-- ----------------------------
INSERT INTO `za_item_property` VALUES ('1', '4', '1', '1');
INSERT INTO `za_item_property` VALUES ('2', '4', '2', '4');
INSERT INTO `za_item_property` VALUES ('3', '4', '3', '8');
INSERT INTO `za_item_property` VALUES ('4', '6', '1', '3');
INSERT INTO `za_item_property` VALUES ('5', '6', '2', '5');
INSERT INTO `za_item_property` VALUES ('6', '6', '3', '8');
INSERT INTO `za_item_property` VALUES ('9', '5', '2', '4');
INSERT INTO `za_item_property` VALUES ('11', '5', '1', '3');
from t t2
where t2.itemid in (select itemid
from t t1
where t1.propid = 2
and valueid = 4)
and t2.propid = 3
and t2.valueid = 8
from za_item_property a,za_item_property b
where a.itemId=b.itemId
and a.propId = 2 and a.valueId =4
and b.propId = 3 and b.valueId =8