一个sql语句查询的问题

[kingwkb]

表a

aid bid

100 59
100 47
100 22
100 32
200 59
200 22
200 25
300 26
300 36

就这2个字段

要查询出bid=47和22的aid

[qdvictory]

select aid from a where bid=47 or bid =22;

[yokle]

select aid from a WHERE bid in (47,22)

[ichigo]

in

[yyai3]

select aid from a where bid=47 or bid=22 order by aid

[kingwkb]

@qdvictory
@yokle
@ichigo 要同时满足bid=47和22的aid，不是or

比如说200满足bid=22，不能满足bid=47，所以200就不能被查询出来，只有aid=100的才满足

[kingwkb]

@yyai3 是同时满足47和22的aid，不能用or

[yyai3]

select b.aid from a b inner join a c where b.aid=c.aid and b.bid=47 and c.bid =22

[qdvictory]

select aid from a where bid=47 and aid in (select aid from a where bid = 22);

[kingwkb]

@yyai3
@qdvictory 同时满足bid的值数目不确定，可能很多个，有没有简单的？

[yyai3]

select aid,count(*) from a where bid in () group by aid having count(*)=n

[yyai3]

要改成count(distinct bid) 去重吧

[qdvictory]

select * from (select if(count(*) > num,aid,0) as cid from a where bid in (22,47,...) group by cid order by cid desc) as s where s.cid != 0;

这是咋设计的表啊。。。

[alsotang]

我的答案跟#7一样
select aid from a as a1 inner join a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid

@yyai3

[qdvictory]

@yyai3 这个好～学习了～

[kingwkb]

@qdvictory
@yyai3 这个一个tag功能，我本来以为用and可以，但是。。。

aid是数据id 有个数据表，bid是tagid，另外还有个tag表保存tag id， name等

这个是保存数据id和tag id的关系表，就2列，一个数据可以有多个tag，就有了这个问题

麻烦请问下，这类问题怎么设计表好？

[qdvictory]

@kingwkb 不就是一个多对多的关系？
我一般用orm很轻易就解决了－ －

[yyai3]

@kingwkb 没有遇到过~ 但是我想我也会这样设计~~

[yokle]

回头一看 这么多人 回答了。 这氛围不错 +1

[colincat]

@kingwkb 这不就是子类和父类的关系吗？

tagkey tag_f_id tag_name
1 0 读书
2 1 科幻类
3 1 教育类
4 2 国内仙剑
5 0 电影

以上tag代表标签的主键 tag_f_id代表父节点ID 0代表是最高节点

然后 这就类似递归。不知道是不是你想要的

[alsotang]

@kingwkb 我觉得你这个问题应该是个比较经典的设计问题，可能你的这个方法已经算是比较常见的解决办法了吧。我对于SQL的接触比较少，但是在Rails中的话....has_many会自动处理好这些问题，建议楼主google一下，应该就会得到答案了。我提供提供关键字吧：“rails hasmany SQL 如何设计”，试试这个。

[alsotang]

@colincat SQL数据库按你这样的设计的话，那不是每次处理相关数据都要取出所有数据并且建立tree啊？

[colincat]

@alsotang 是的 需要这样，但是你可以用这个表构建一个数据结构，预先生成一个tree，只有在有新变动的时候需要重新生成tree，如果他说的是一种频道的话，可能变化应该不是很大吧

[ipconfiger]

（SELECT `aid` FROM `表a` WHERE `bid`=22） INTERSECT （SELECT `aid` FROM `表a` WHERE `bid`=47）

原因，用 `bid`=22 or `bid`=47 这个查询条件 或者 `bid` in (22,47),在mysql里都会丢索引，也就是会产生全表扫描，所以把每个条件单独用字查询取出后用INTERSECT取交集，即可解决

[alsotang]

@ipconfiger 问一下这位牛人..我13L的解答，效率上如何，跟您23楼这个比较的话呢...
(本人SQL处于入门水平)

[kingwkb]

@ipconfiger mysql是不支持INTERSECT的

[ipconfiger]

@kingwkb

可以用IN 子查询来代替，IN 子查询是可以match到索引的
SELECT `aid` FROM `表a` WHERE `bid`=47 AND `aid` IN (SELECT `aid` FROM `表a` WHERE `bid` = 22)

如果不确定数量，这个需求确实没有高效率的SQL可以解决，因为不管怎么优化都会产生临时表和filesort。

@alsotang 都group by了就别说效率了，小表无所谓，上百万的表这么写，访问一上去就等服务器冒烟吧

[kingwkb]

@ipconfiger 恩，看来in的效果还比较好，最起码都是using index, using where，虽然可能子查询多几个

感谢大家的回复

[alsotang]

@ipconfiger 是select aid from a as a1 inner join a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid，没有group by。你要表达的是inner join?

[ipconfiger]

@alsotang 你没跑过你的SQL吧，会报错的，亲。给你修改一下改成
select a1.aid from a as a1 inner join a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid;
这下不报错了，结果返回 Empty set (0.00 sec)
用我的sql实验，返回的是

+------+
| aid |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

我建的测试数据库数据和LZ给出的一致。

explain分析一下你的语句就知道错误在那里了


mysql> EXPLAIN
-> select a1.aid from table_a as a1 inner join table_a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+


Impossible WHERE

[alsotang]

@ipconfiger 写写指正。还请问，效率呢？对于index的使用呢？

[ipconfiger]

@alsotang 你那条错误的sql就不用纠结效率了，根本的不出正确结果效率是无用的。对于LZ这个命题的效率问题我之前说过了，很难有特别高效的写法，层层嵌套的IN 子查询或者 EXISTS 子查询的方式可以保证不会丢失索引，但是过多潜逃的子查询会产生大量的临时表，且排序的时候如果不是根据bid排序还会产生filesort消耗CPU和内存。修改一下设计，用冗余数据来空间换时间比较好

[napoleonu]

我造了点数据，测试了下。

mysql> show create table d\G
*************************** 1. row ***************************
Table: d
Create Table: CREATE TABLE `d` (
`aid` int(11) DEFAULT NULL,
`bid` int(11) DEFAULT NULL,
KEY `idx1` (`aid`,`bid`),
KEY `idx2` (`bid`,`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(*) from d;
+----------+
| count(*) |
+----------+
| 465005 |
+----------+
1 row in set (0.36 sec)

mysql> select count(*) from (select count(*) from d group by aid,bid) t1;
+----------+
| count(*) |
+----------+
| 465005 |
+----------+
1 row in set (0.29 sec)

mysql> select aid,count(*) from d group by aid;
+------+----------+
| aid | count(*) |
+------+----------+
| 1 | 16640 |
| 2 | 16640 |
| 3 | 16640 |
| 4 | 16640 |
| 5 | 16640 |
| 6 | 33197 |
| 7 | 32448 |
| 11 | 16640 |
| 12 | 16640 |
| 13 | 16640 |
| 14 | 16640 |
| 15 | 16640 |
| 16 | 16640 |
| 17 | 16640 |
| 21 | 16640 |
| 22 | 16640 |
| 23 | 16640 |
| 24 | 16640 |
| 25 | 16640 |
| 26 | 16640 |
| 31 | 16640 |
| 32 | 16640 |
| 33 | 16640 |
| 34 | 16640 |
| 35 | 16640 |
| 36 | 16640 |
+------+----------+
26 rows in set (0.22 sec)

mysql> select count(*) from d where aid=7;
+----------+
| count(*) |
+----------+
| 32448 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from d where aid=6;
+----------+
| count(*) |
+----------+
| 33197 |
+----------+
1 row in set (0.03 sec)

mysql> select sql_no_cache bid from d where aid=6 and bid in (select bid from d where aid=7);
| 331800 |
| 332000 |
| 332200 |
| 332400 |
| 332600 |
| 332800 |
+--------+
18221 rows in set (0.12 sec)

mysql> select sql_no_cache bid from d where aid in (6,7) group by bid having count(*)=2;
| 331800 |
| 332000 |
| 332200 |
| 332400 |
| 332600 |
| 332800 |
+--------+
18221 rows in set (0.08 sec)

mysql> select sql_no_cache t1.bid from d t1 ,d t2 where t1.aid=6 and t2.aid=7 and t1.bid=t2.bid;
| 331800 |
| 332000 |
| 332200 |
| 332400 |
| 332600 |
| 332800 |
+--------+
18221 rows in set (0.11 sec)

[napoleonu]

select sql_no_cache bid from d where aid=6 and bid in (select bid from d where aid=7);
select sql_no_cache bid from d where aid in (6,7) group by bid having count(*)=2;
select sql_no_cache t1.bid from d t1 ,d t2 where t1.aid=6 and t2.aid=7 and t1.bid=t2.bid;

三条SQL语句都经过反复执行，所消耗的时间也都稳定在如上的时间上。

就这个数据量来说，似乎

select sql_no_cache bid from d where aid in (6,7) group by bid having count(*)=2;

效率最高。

[kingwkb]

@napoleonu 你这个数据分布不好，虽然数据大，但aid, bid分布太少，group by aid和group by aid, bid至少也得达到上千个吧。

[napoleonu]

@kingwkb

group by aid,bid 上千万，也就是数据量在千万级别，我觉得效果没多大区别，如果你现在有上千万数据，跑下不就知道了。

aid 是你上文中说的场景中的 tag，group by aid 上千万，你有上千万的tag？

[kingwkb]

@napoleonu 您看错了吧，我说的是上千，没有“万”字，目前我还没有那么多数据，之后有的话一定跑跑试试

[napoleonu]

@kingwkb 嗷，那也没什么区别。