可以用join,如select ta.* from ta join tb on ta.id=tb.id
忘了说了,我要求的交集是两个结果集的交集,不是两个表的交集!用楼上的例子也就是: select ta.*, tb.* from ta, tb using(id) where tb.rid = 78 and tb.c = 'true'; 和 select ta.*, tb.* from ta, tb using(id) where tb.rid = 64 and tb.c = 'true'; 的交集。而我现在如是查: select ta.*, tb.* from ta, tb using(id) where tb.rid = 78 and tb.c = 'true' and ta.id in (select ta.id from ta. tb using(id) where tb.rid = 64 and tb.c = 'true');有没有什么办法优化这样的语句?
select ta.*, tb.* from ta, tb using(id) where tb.rid = 78 and tb.c = 'true'; 这个查询里ta上没条件的?
code=SQL] select ta.*, tb.* from ta, tb where tb.rid = 78 and tb.c = 'true'; union all select ta.*, tb.* from ta, tb where tb.rid = 64 and tb.c = 'true'; [/code]
select t1.id from tb t1 inner join tb t2 using(id) where t1.rid = 78 and t2.rid = 64 and t1.c = 'true' and t2.c = 'true';然后再用这个去与ta 做联接查询。当然具体要看你的索引,键值的分布。 并没有什么特别的公式化的方法。
又忘了说了, ta, tb两个表的基本情况如下:create table ta(id int, name varchar(10)); insert into ta(id, name) values(1, 'name1'), (2, 'name2'), (3, 'name3'), (4, 'name4'); mysql> select * from ta; +------+-------+ | id | name | +------+-------+ | 1 | name1 | | 2 | name2 | | 3 | name3 | | 4 | name4 | +------+-------+create table tb(id int, rid int); insert into tb(id, rid) values(1, 78), (2, 64), (2, 78), (3, 78), (4, 78), (4, 64),(4, 56); select * from tb; +------+------+ | id | rid | +------+------+ | 1 | 78 | | 2 | 64 | | 2 | 78 | | 3 | 78 | | 4 | 78 | | 4 | 64 | | 4 | 56 | +------+------+ 现在我需求是求出以下两个集合的交集:mysql> select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 78; +------+-------+------+ | id | name | rid | +------+-------+------+ | 1 | name1 | 78 | | 2 | name2 | 78 | | 3 | name3 | 78 | | 4 | name4 | 78 | +------+-------+------+ 4 rows in set (0.00 sec)mysql> select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 64; +------+-------+------+ | id | name | rid | +------+-------+------+ | 2 | name2 | 64 | | 4 | name4 | 64 | +------+-------+------+ 2 rows in set (0.00 sec) 我现在的做法是: select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 64 and ta.id in (select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78); +------+-------+------+ | id | name | rid | +------+-------+------+ | 2 | name2 | 64 | | 4 | name4 | 64 | +------+-------+------+ 要求的结果就是在tb表中rid有为78的,且又有为64的,最后将结果列出来!
select ta.id, ta.name, tb.rid from ta inner join (select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78 or tb.rid=64)tb on ta.id = tb.id;
select ta.id, ta.name, tb.rid from ta, tb,tb as tc where ta.id = tb.id and tb.rid = 78 and ta.id = tc.id and tc.rid = 64;
select ta1.id, ta1.name, tb1.rid from ta ta1 inner join tb tb1 on ta1.id = tb1.id and tb1.rid = 64 inner join ta ta2 on ta1.id=ta2.id inner join tb tb2 on ta2.id = tb2.id and tb2.rid = 78;
如果说后边的筛选条件还包括了ta表中的一个字段,以上边的列子为例: select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 64 and ta.name like 'name%' and ta.id in (select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78 and ta.name like "name%"); 暂不考虑索引如何,按acmain版的方法,语句应该如何写呢?
josy和denniswwh写的,我先研究研究个
mysql> select ta.id,ta.name,tb.rid from ta inner join tb on ta.id=tb.id where t b.rid=64; +------+-------+------+ | id | name | rid | +------+-------+------+ | 2 | name2 | 64 | | 4 | name4 | 64 | +------+-------+------+ 2 rows in set (0.00 sec)
mysql> select ta.id,ta.name,tb.rid from ta inner join tb on ta.id=tb.id where b.rid=64 or tb.rid=78; +------+-------+------+ | id | name | rid | +------+-------+------+ | 1 | name1 | 78 | | 2 | name2 | 64 | | 2 | name2 | 78 | | 3 | name3 | 78 | | 4 | name4 | 78 | | 4 | name4 | 64 | +------+-------+------+ 6 rows in set (0.00 sec)
mysql> select ta.id,ta.name,tb.rid from ta inner join tb on ta.id=tb.id where b.rid=64 or tb.rid=78; +------+-------+------+ | id | name | rid | +------+-------+------+ | 1 | name1 | 78 | | 2 | name2 | 64 | | 2 | name2 | 78 | | 3 | name3 | 78 | | 4 | name4 | 78 | | 4 | name4 | 64 | +------+-------+------+ 6 rows in set (0.00 sec)
from ta
join tb on ta.id=tb.id
select ta.*, tb.* from ta, tb using(id) where tb.rid = 78 and tb.c = 'true';
和
select ta.*, tb.* from ta, tb using(id) where tb.rid = 64 and tb.c = 'true';
的交集。而我现在如是查:
select ta.*, tb.* from ta, tb using(id) where tb.rid = 78 and tb.c = 'true' and ta.id in (select ta.id from ta. tb using(id) where tb.rid = 64 and tb.c = 'true');有没有什么办法优化这样的语句?
这个查询里ta上没条件的?
select ta.*, tb.* from ta, tb where tb.rid = 78 and tb.c = 'true';
union all
select ta.*, tb.* from ta, tb where tb.rid = 64 and tb.c = 'true';
[/code]
又忘了说了,
ta, tb两个表的基本情况如下:create table ta(id int, name varchar(10));
insert into ta(id, name) values(1, 'name1'), (2, 'name2'), (3, 'name3'), (4, 'name4');
mysql> select * from ta;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
+------+-------+create table tb(id int, rid int);
insert into tb(id, rid) values(1, 78), (2, 64), (2, 78), (3, 78), (4, 78), (4, 64),(4, 56);
select * from tb;
+------+------+
| id | rid |
+------+------+
| 1 | 78 |
| 2 | 64 |
| 2 | 78 |
| 3 | 78 |
| 4 | 78 |
| 4 | 64 |
| 4 | 56 |
+------+------+
现在我需求是求出以下两个集合的交集:mysql> select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 78;
+------+-------+------+
| id | name | rid |
+------+-------+------+
| 1 | name1 | 78 |
| 2 | name2 | 78 |
| 3 | name3 | 78 |
| 4 | name4 | 78 |
+------+-------+------+
4 rows in set (0.00 sec)mysql> select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 64;
+------+-------+------+
| id | name | rid |
+------+-------+------+
| 2 | name2 | 64 |
| 4 | name4 | 64 |
+------+-------+------+
2 rows in set (0.00 sec)
我现在的做法是: select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 64 and ta.id in (select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78);
+------+-------+------+
| id | name | rid |
+------+-------+------+
| 2 | name2 | 64 |
| 4 | name4 | 64 |
+------+-------+------+
要求的结果就是在tb表中rid有为78的,且又有为64的,最后将结果列出来!
select ta.id, ta.name, tb.rid
from ta inner join
(select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78 or tb.rid=64)tb
on ta.id = tb.id;
ta.id,
ta.name,
tb.rid
from
ta, tb,tb as tc
where
ta.id = tb.id and tb.rid = 78
and
ta.id = tc.id and tc.rid = 64;
inner join tb tb1 on ta1.id = tb1.id and tb1.rid = 64
inner join ta ta2 on ta1.id=ta2.id
inner join tb tb2 on ta2.id = tb2.id and tb2.rid = 78;
如果说后边的筛选条件还包括了ta表中的一个字段,以上边的列子为例:
select ta.id, ta.name, tb.rid from ta, tb where ta.id = tb.id and tb.rid = 64 and ta.name like 'name%' and ta.id in (select ta.id from ta, tb where ta.id = tb.id and tb.rid = 78 and ta.name like "name%"); 暂不考虑索引如何,按acmain版的方法,语句应该如何写呢?
b.rid=64;
+------+-------+------+
| id | name | rid |
+------+-------+------+
| 2 | name2 | 64 |
| 4 | name4 | 64 |
+------+-------+------+
2 rows in set (0.00 sec)
b.rid=64 or tb.rid=78;
+------+-------+------+
| id | name | rid |
+------+-------+------+
| 1 | name1 | 78 |
| 2 | name2 | 64 |
| 2 | name2 | 78 |
| 3 | name3 | 78 |
| 4 | name4 | 78 |
| 4 | name4 | 64 |
+------+-------+------+
6 rows in set (0.00 sec)
b.rid=64 or tb.rid=78;
+------+-------+------+
| id | name | rid |
+------+-------+------+
| 1 | name1 | 78 |
| 2 | name2 | 64 |
| 2 | name2 | 78 |
| 3 | name3 | 78 |
| 4 | name4 | 78 |
| 4 | name4 | 64 |
+------+-------+------+
6 rows in set (0.00 sec)