2 u014638482 u014638482 于 2016.03.28 15:41 提问

求助MySQL的关于sql语句怎么写??三表的查询~~~请大神进来帮忙~~~~~

现在我有三个表:
表1:
pre_common_member_profile:
uid(用户的uid),
gender(性别),
birthyear(出生的年),
birthmonth(出生的月),
birthday(出生的日)
表2:
pre_home_friend:
uid(用户的uid),
fuid(加为好友的uid)
表3:
pre_home_friend_request:
fuid(发送好友请求的人),
uid(发送给谁)

要求:
查找表1,但是排除一定条件。

查询表1上面的五个字段,条件为uid=570并且uid不能等于(这两个条件:1、表2中uid=570的fuid的值。2、表3中的fuid=570的uid值。)

请尽量写出的语句效率高一点。

7个回答

u014638482
u014638482   2016.03.28 16:07
已采纳

@bdmh
select gender,uid,birthmonth,birthday,birthyear from pre_common_member_profile as p where destination = '济南' and p.uid <> 570 and p.uid < 570 and gender = 0 and uid not in(select fuid from pre_home_friend where uid=570 union select uid from pre_home_friend_request where fuid = 570) order by uid desc
怎么优化一下??

cyt546645810
cyt546645810   2016.03.28 15:48

select * from 表1 t1 where t1.uid = 570
and uid not in
(select fuid from 表2 where uid = 570
union
select uid from 表3 where fuid = 570);

u014638482
u014638482 select gender,uid,birthmonth,birthday,birthyear from pre_common_member_profile as p where destination = '济南' and p.uid <> 570 and p.uid < 570 and gender = 0 and uid not in(select fuid from pre_home_friend where uid=570 union select uid from pre_home_friend_request where fuid = 570) order by uid desc 怎么优化一下??
一年多之前 回复
bdmh
bdmh   Ds   Rxr 2016.03.28 15:46

select A.id,B.id,C.id from A inner join B on A.id=B.id inner join C on C.id=A.id

u014638482
u014638482 select gender,uid,birthmonth,birthday,birthyear from pre_common_member_profile as p where destination = '济南' and p.uid <> 570 and p.uid < 570 and gender = 0 and uid not in(select fuid from pre_home_friend where uid=570 union select uid from pre_home_friend_request where fuid = 570) order by uid desc 怎么优化一下??
一年多之前 回复
wojiushiwo945you
wojiushiwo945you   Ds   Rxr 2016.03.28 16:58

你的uid=570并且uid不能等于这两个条件直接是什么关系呢?

li953348869
li953348869   2016.03.28 17:40

直接select gender,uid,birthmonth,birthday,birthyear from pre_common_member_profile as p where destination = '济南' and p.uid < 570 and gender = 0 order by uid desc

uid not in(select fuid from pre_home_friend where uid=570 union select uid from pre_home_friend_request where fuid = 570) 这个没必要,这个始终取得的是空
uid<>570 ,你uid<570 了,还会等于570吗?

li953348869
li953348869 回复elainetian3: 你的排除个人觉得都是多余,and 和or希望你能分辨清楚,
一年多之前 回复
u014638482
u014638482 还有可能有大于570的 我这句是想排除它本身
一年多之前 回复
zhangjs712
zhangjs712   2016.03.29 09:27

这个SQL不难写,给你分析下SQL的优化
pre_home_friend 的 uid字段建立索引
pre_home_friend_request 的 fuid建立索引

pre_common_member_profile 的uid是主键的话,应该是有索引的。修改UID的索引的排序方式为 desc ,默认是ASC的,你可以看下

使用NOT EXISTS 替换not in;

然后看SQL的执行计划。看索引有没有生效

Mr__Hacker
Mr__Hacker   2016.03.29 17:07

select A.id,B.id,C.id from A inner join B on A.id=B.id inner join C on C.id=A.id
select A from B

sellect *

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!