select aid from taglist where tid= '5' or tid = '15';
请各位看一下我说的,是同时包括5 和 15的aid,不是包括5 或 15 的
你没发现你的两个语句的结果是不一样的吗?mysql> select aid from taglist where aid in (select aid from taglist where tid=5 ) and tid=15; +-----+ | aid | +-----+ | 1 | | 2 | +-----+ 2 rows in set (0.00 sec)mysql> select a.aid from taglist as a inner join (select aid from taglist where tid=5) as b on a.aid=b.aid; +-----+ | aid | +-----+ | 1 | | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | +-----+ 8 rows in set (0.00 sec)我觉得这样应该就可以了: mysql> select a.aid -> from taglist as a left join taglist as b -> on a.id = b.id and a.aid = b.aid -> where a.tid = 5 and b.tid = 15; +-----+ | aid | +-----+ | 1 | | 2 | +-----+ 2 rows in set (0.00 sec)
select aid from taglist t where tid=5 and exists (select 1 from taglist where tid=15 and aid=t.aid)创建索引 create index x1 on taglist(aid,tid); create index x2 on taglist(tid);
select aid from taglist where tid= '5' and tid = '15' group by aid having count(distinct tid)>1
哈哈,笑而不语:mysql> select aid from taglist where tid= '5' and tid = '15' -> group by aid -> having count(distinct tid)>1; Empty set (0.06 sec)
) and tid=15;
+-----+
| aid |
+-----+
| 1 |
| 2 |
+-----+
2 rows in set (0.00 sec)mysql> select a.aid from taglist as a inner join (select aid from taglist where
tid=5) as b on a.aid=b.aid;
+-----+
| aid |
+-----+
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
+-----+
8 rows in set (0.00 sec)我觉得这样应该就可以了:
mysql> select a.aid
-> from taglist as a left join taglist as b
-> on a.id = b.id and a.aid = b.aid
-> where a.tid = 5 and b.tid = 15;
+-----+
| aid |
+-----+
| 1 |
| 2 |
+-----+
2 rows in set (0.00 sec)
create index x1 on taglist(aid,tid);
create index x2 on taglist(tid);
group by aid
having count(distinct tid)>1
哈哈,笑而不语:mysql> select aid from taglist where tid= '5' and tid = '15'
-> group by aid
-> having count(distinct tid)>1;
Empty set (0.06 sec)