我有3个表;一个项目表、一个属性表和一个x_ref表,该表将项目链接到属性,并包含该属性的值:
CREATE TABLE IF NOT EXISTS `items` (
`item_id` INT(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=COMPRESSED;
CREATE TABLE IF NOT EXISTS `properties` (
`property_id` INT(11) NOT NULL AUTO_INCREMENT,
`property_name` VARCHAR(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=COMPRESSED;
CREATE TABLE IF NOT EXISTS `item_xref_properties` (
`item_id` INT(11) NOT NULL,
`property_id` INT(11) NOT NULL,
`property_value` DECIMAL(8,1) NOT NULL,
PRIMARY KEY (`item_id`,`property_id`),
INDEX (`item_id`),
INDEX (`property_id`),
INDEX (`property_value_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=COMPRESSED;
我有这样的查询:
获取所有包含属性'A', 'B'和'C'的项A在100到200之间"B"在50到80之间"C"在10到30之间
翻译过来就是:
SELECT itm.item_id
FROM items itm
JOIN item_types ityp
ON itm.type_id = ityp.type_id
JOIN item_xref_properties ixp1
ON itm.item_id = ixp1.item_id
AND ixp1.property_value
BETWEEN '100' AND '200'
JOIN properties prop1
ON ixp1.property_id = prop1.property_id
AND prop1.property_name = 'Property A'
JOIN item_xref_properties ixp2
ON itm.item_id = ixp2.item_id
AND ixp2.property_value
BETWEEN '50' AND '80'
JOIN properties prop2
ON ixp2.property_id = prop2.property_id
AND prop2.property_name = 'Property B'
JOIN item_xref_properties ixp3
ON itm.item_id = ixp3.item_id
AND ixp3.property_value_1
BETWEEN '10' AND '30'
JOIN properties prop3
ON ixp3.property_id = prop3.property_id
AND prop3.property_name = 'Property C'
非常直接,虽然有点不优雅。
我的问题是我需要像上面那样查询这个表,并且还找到不具有如下指定属性的项:
获取所有包含属性'A', 'B'和'C'的项A在100到200之间"B"在50到80之间"C"在10到30之间但是不包含属性'D'或'E'
我已经尝试了很多方法来翻译这个工作查询,但无济于事。这可能吗?或者我将不得不将表扁平化,并在项目表中为每个属性设置单独的列(不理想,因为有超过100个属性,并且该列表将来可能会增长)
任何帮助将非常感激!谢谢。
很抱歉这么简短的回复。
再看一遍,我看到了union:
SELECT itm.item_id
FROM items itm
JOIN item_types ityp
ON itm.type_id = ityp.type_id
JOIN item_xref_properties ixp1
ON itm.item_id = ixp1.item_id
AND ixp1.property_value
BETWEEN '100' AND '200'
JOIN properties prop1
ON ixp1.property_id = prop1.property_id
AND prop1.property_name = 'Property A'
UNION
SELECT itm.item_id
FROM items itm
JOIN item_types ityp
ON itm.type_id = ityp.type_id
JOIN item_xref_properties ixp2
ON itm.item_id = ixp2.item_id
AND ixp2.property_value
BETWEEN '50' AND '80'
JOIN properties prop2
ON ixp2.property_id = prop2.property_id
AND prop2.property_name = 'Property B'
UNION
SELECT itm.item_id
FROM items itm
JOIN item_types ityp
ON itm.type_id = ityp.type_id
JOIN item_xref_properties ixp3
ON itm.item_id = ixp3.item_id
AND ixp3.property_value_1
BETWEEN '10' AND '30'
JOIN properties prop3
ON ixp3.property_id = prop3.property_id
AND prop3.property_name = 'Property C'
您也将items连接到item_type,但对信息不做任何操作(只是确认item。Type_id是否合法?)。你可以把它去掉
然后,如果您所追求的是item_id,那么您可以在每次访问item_xref_properties_table时获得它,而无需访问items表,这将使事情变得更好。