java - MySQL可变商品数据模型的设计-PHP中文网问答

java - MySQL可变商品数据模型的设计

迷茫 2017-04-18 10:37:09

[Java讨论组]

550

迷茫

业精于勤，荒于嬉;行成于思，毁于随。

全部回复(2)

ringa_lee2017-04-18 10:39:09 2楼

没有人回答？

赞 +0

添加回复

伊谢尔伦2017-04-18 10:39:09 1楼

筛选时,查找"同时包含"多个特定配置条件的商品集合,可以这样写:

SELECT goods_id FROM (
    SELECT goods_id FROM attr WHERE attr_name = '内存' AND attr_value = '8GB'
    UNION ALL
    SELECT goods_id FROM attr WHERE attr_name = '存储' AND attr_value = '256GB'
) AS tmp
GROUP BY goods_id HAVING count(*) = 2;
+----------+
| goods_id |
+----------+
|        2 |
+----------+

表结构如下:

CREATE TABLE `attr` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `goods_id` int(10) unsigned NOT NULL,
    `attr_name`  varchar(255) NOT NULL DEFAULT '',
    `attr_value` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE KEY goods_id_attr_name (`goods_id`, `attr_name`),
    KEY attr_name_attr_value (`attr_name`, `attr_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(1,'内存','4GB');
INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(2,'内存','8GB');
INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(1,'存储','256GB');
INSERT INTO attr(goods_id, attr_name, attr_value) VALUES(2,'存储','256GB');
+----+----------+-----------+------------+
| id | goods_id | attr_name | attr_value |
+----+----------+-----------+------------+
|  1 |        1 | 内存      | 4GB        |
|  2 |        2 | 内存      | 8GB        |
|  3 |        1 | 存储      | 256GB      |
|  4 |        2 | 存储      | 256GB      |
+----+----------+-----------+------------+

赞 +0

添加回复

专题推荐

更多>

热门话题

热门教程

更多>