u014638482 于 2016.03.28 15:41 提问

pre_common_member_profile：
uid（用户的uid）,
gender（性别）,
birthyear（出生的年），
birthmonth（出生的月）,
birthday（出生的日）

pre_home_friend：
uid（用户的uid），
fuid（加为好友的uid）

pre_home_friend_request：
fuid(发送好友请求的人)，
uid(发送给谁)

7个回答

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   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 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      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 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      2016.03.28 16:58

li953348869   2016.03.28 17:40

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 回复elainetian3: 你的排除个人觉得都是多余，and 和or希望你能分辨清楚，

u014638482 还有可能有大于570的 我这句是想排除它本身

zhangjs712   2016.03.29 09:27

pre_home_friend 的 uid字段建立索引
pre_home_friend_request 的 fuid建立索引

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

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 *