在你的程序中可以循环实现这些条件的添加。select goodid from 上表 t where exists (select 1 from 上表 where goodid=t.goodid and attrname='屏幕尺寸' and attrvalue='29寸') and exists (select 1 from 上表 where goodid=t.goodid and attrname='外观颜色' and attrvalue='白色') and exists (select 1 from 上表 where goodid=t.goodid and attrname='外观类型' and attrvalue='平面直角')
或者如下,也可以用程序来生成多个条件select goodid from 上表 group by goodid having sum(IF(attrname='屏幕尺寸' and attrvalue='29寸',1,0)) + sum(IF(attrname='外观颜色' and attrvalue='白色',1,0)) + sum(IF(attrname='外观类型' and attrvalue='平面直角',1,0)) = 3
提两个问题, 这个表是不是设计的不够好?应该把attrname做列名更合适的吧。 还有个问题,能不能够用group by 先分组了再挑出要找的那个goodid,如果用这个办法怎么写句子?
第一个问题:对,表结构完全可以改为: 自增id 商品ID 屏幕尺寸 外观颜色 外观类型 id goodid size color type 第二个问题:可以先分组将相同goodid的attrvalue的字段值作字符串相连,然后再在结果中查询,假设表名为image: select goodid from (select goodid,group_concat(attrvalue) as attribute from image group by goodid) as newtable where attribute='29寸,白色,平面直角';
补充:上面第二问的答案: select goodid from (select goodid,group_concat(attrvalue) as attribute from image group by goodid) as newtable where attribute='29寸,白色,平面直角'; 这条语句可以更简短些,改成: select goodid,group_concat(attrvalue) as attribute from image group by goodid having attribute='29寸,白色,平面直角';
楼上比较简单, 也可以: select a.* from tta1 a inner join tta1 b on a.goodid=b.goodid inner join tta1 c on a.goodid=c.goodid where a.attrname='屏幕尺寸' and a.attrvalue='29寸' and b.attrname='外观颜色' and b.attrvalue='白色' and c.attrname='外观类型' and c.attrvalue='平面直角'
楼上比较简单, 也可以: select a.* from tta1 a inner join tta1 b on a.goodid=b.goodid inner join tta1 c on a.goodid=c.goodid where a.attrname='屏幕尺寸' and a.attrvalue='29寸' and b.attrname='外观颜色' and b.attrvalue='白色' and c.attrname='外观类型' and c.attrvalue='平面直角'
一楼,二楼已经给你答案了啊?! 建议提问后多少看一下别人给你的回复吧。 mysql> select * from tx; +----+--------+----------+-----------+ | id | goodid | attrname | attrvalue | +----+--------+----------+-----------+ | 1 | 1 | 屏幕尺寸 | 29寸 | | 2 | 1 | 外观颜色 | 白色 | | 3 | 1 | 外观类型 | 平面直角 | | 4 | 2 | 屏幕尺寸 | 35寸 | | 5 | 2 | 外观颜色 | 白色 | | 6 | 2 | 外观类型 | 普通 | | 7 | 3 | 屏幕尺寸 | 29寸 | | 8 | 3 | 外观颜色 | 红色 | | 9 | 3 | 外观类型 | 平面直角 | +----+--------+----------+-----------+ 9 rows in set (0.00 sec)mysql> mysql> select goodid -> from tx t -> where exists (select 1 from tx where goodid=t.goodid and attrname='屏幕尺寸' and attrvalue='29寸') -> and exists (select 1 from tx where goodid=t.goodid and attrname='外观颜色' and attrvalue='白色') -> and exists (select 1 from tx where goodid=t.goodid and attrname='外观类型' and attrvalue='平面直角'); +--------+ | goodid | +--------+ | 1 | | 1 | | 1 | +--------+ 3 rows in set (0.02 sec)mysql> mysql> select goodid -> from tx -> group by goodid -> having sum(IF(attrname='屏幕尺寸' and attrvalue='29寸',1,0)) + -> sum(IF(attrname='外观颜色' and attrvalue='白色',1,0)) + -> sum(IF(attrname='外观类型' and attrvalue='平面直角',1,0)) = 3; +--------+ | goodid | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)mysql>
select goodid,group_concat(attrvalue) as attribute from image group by goodid having attribute='29寸,白色,平面直角' 这个不好, 如果数据库中的顺序换一下的话就查不出来了, 如 变成了 '29寸,平面直角,白色'
from 上表 t
where exists (select 1 from 上表 where goodid=t.goodid and attrname='屏幕尺寸' and attrvalue='29寸')
and exists (select 1 from 上表 where goodid=t.goodid and attrname='外观颜色' and attrvalue='白色')
and exists (select 1 from 上表 where goodid=t.goodid and attrname='外观类型' and attrvalue='平面直角')
from 上表
group by goodid
having sum(IF(attrname='屏幕尺寸' and attrvalue='29寸',1,0)) +
sum(IF(attrname='外观颜色' and attrvalue='白色',1,0)) +
sum(IF(attrname='外观类型' and attrvalue='平面直角',1,0)) = 3
这个表是不是设计的不够好?应该把attrname做列名更合适的吧。
还有个问题,能不能够用group by 先分组了再挑出要找的那个goodid,如果用这个办法怎么写句子?
自增id 商品ID 屏幕尺寸 外观颜色 外观类型
id goodid size color type
第二个问题:可以先分组将相同goodid的attrvalue的字段值作字符串相连,然后再在结果中查询,假设表名为image:
select goodid from (select goodid,group_concat(attrvalue) as attribute from image group by goodid) as newtable where attribute='29寸,白色,平面直角';
select goodid from (select goodid,group_concat(attrvalue) as attribute from image group by goodid) as newtable where attribute='29寸,白色,平面直角';
这条语句可以更简短些,改成:
select goodid,group_concat(attrvalue) as attribute from image group by goodid having attribute='29寸,白色,平面直角';
也可以:
select a.* from tta1 a inner join tta1 b on a.goodid=b.goodid inner join tta1 c on a.goodid=c.goodid
where a.attrname='屏幕尺寸' and a.attrvalue='29寸' and b.attrname='外观颜色' and b.attrvalue='白色' and c.attrname='外观类型' and c.attrvalue='平面直角'
也可以:
select a.* from tta1 a inner join tta1 b on a.goodid=b.goodid inner join tta1 c on a.goodid=c.goodid
where a.attrname='屏幕尺寸' and a.attrvalue='29寸' and b.attrname='外观颜色' and b.attrvalue='白色' and c.attrname='外观类型' and c.attrvalue='平面直角'
建议提问后多少看一下别人给你的回复吧。
mysql> select * from tx;
+----+--------+----------+-----------+
| id | goodid | attrname | attrvalue |
+----+--------+----------+-----------+
| 1 | 1 | 屏幕尺寸 | 29寸 |
| 2 | 1 | 外观颜色 | 白色 |
| 3 | 1 | 外观类型 | 平面直角 |
| 4 | 2 | 屏幕尺寸 | 35寸 |
| 5 | 2 | 外观颜色 | 白色 |
| 6 | 2 | 外观类型 | 普通 |
| 7 | 3 | 屏幕尺寸 | 29寸 |
| 8 | 3 | 外观颜色 | 红色 |
| 9 | 3 | 外观类型 | 平面直角 |
+----+--------+----------+-----------+
9 rows in set (0.00 sec)mysql>
mysql> select goodid
-> from tx t
-> where exists (select 1 from tx where goodid=t.goodid and attrname='屏幕尺寸' and attrvalue='29寸')
-> and exists (select 1 from tx where goodid=t.goodid and attrname='外观颜色' and attrvalue='白色')
-> and exists (select 1 from tx where goodid=t.goodid and attrname='外观类型' and attrvalue='平面直角');
+--------+
| goodid |
+--------+
| 1 |
| 1 |
| 1 |
+--------+
3 rows in set (0.02 sec)mysql>
mysql> select goodid
-> from tx
-> group by goodid
-> having sum(IF(attrname='屏幕尺寸' and attrvalue='29寸',1,0)) +
-> sum(IF(attrname='外观颜色' and attrvalue='白色',1,0)) +
-> sum(IF(attrname='外观类型' and attrvalue='平面直角',1,0)) = 3;
+--------+
| goodid |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)mysql>
这个不好,
如果数据库中的顺序换一下的话就查不出来了,
如
变成了 '29寸,平面直角,白色'