一个表两个列:id 属性
1 red
2 blue
1 big
7 red
2 small
3 big
4 small
5 big
6 small
7 small
4 blue我想用最高效的办法把属性满足red并且同时满足small的id选出来,(这里面只有id=7满足)请问怎么做?
1 red
2 blue
1 big
7 red
2 small
3 big
4 small
5 big
6 small
7 small
4 blue我想用最高效的办法把属性满足red并且同时满足small的id选出来,(这里面只有id=7满足)请问怎么做?
+------+-------+
| id | prop |
+------+-------+
| 1 | red |
| 2 | blue |
| 1 | big |
| 7 | red |
| 2 | small |
| 3 | big |
| 4 | small |
| 5 | big |
| 6 | small |
| 7 | small |
| 4 | blue |
+------+-------+
11 rows in set (0.00 sec)mysql>
mysql> select a.id
-> from t_dodont a ,t_dodont b
-> where a.id=b.id
-> and a.prop='red'
-> and b.prop='small';
+------+
| id |
+------+
| 7 |
+------+
1 row in set (0.06 sec)mysql>
比如 表中会有多少记录?
同一ID下平均会有多少记录?
同一属性值下平均会有多少记录?一楼的语句在下面这种情况下(同一ID下只有2~3条记录,存在id,prop的唯一主键)应该是比较理想的。
mysql> show index from t_dodont;
+----------+------------+---------------+--------------+-----------
| Table | Non_unique | Key_name | Seq_in_index | Column_nam
+----------+------------+---------------+--------------+-----------
| t_dodont | 1 | uk_t_dodont_1 | 1 | id
| t_dodont | 1 | uk_t_dodont_1 | 2 | prop
+----------+------------+---------------+--------------+-----------
2 rows in set (0.06 sec)