SELECT users.* FROM `users` where id in(select id from friendships where friendships.user_id = 3)
and users.id not in (select id from app_users where app_users.app_id = 1 AND app_users.user_id not in(app_users.user_id))
请将这个语句转换成连接形式(select xxx from a,b,c where X AND Y AND Z)类似这样的
因为mysql处理in非常慢,处理连接很快谢谢 100分送上
and users.id not in (select id from app_users where app_users.app_id = 1 AND app_users.user_id not in(app_users.user_id))
请将这个语句转换成连接形式(select xxx from a,b,c where X AND Y AND Z)类似这样的
因为mysql处理in非常慢,处理连接很快谢谢 100分送上
try:SELECT users.* FROM `users` inner join friendships
on friendships.id=users.id and friendships.user_id = 3
inner join app_users on app_users.id=users.id and app_users.app_id = 1
就是选择出没有安装该APP的所有朋友
是一样的表?有什么作用?
这句有什么作用?
SELECT users.* FROM `users` inner join friendships
on friendships.id=users.id
inner join app_users on app_users.id=users.id
where friendships.user_id = 3 and app_users.app_id = 1
SELECT users.* FROM `users` where exists (select 1 from friendships where friendships.user_id = 3 and friendships.id=users.id)
and not exists
not in (select 1 from app_users where app_users.app_id = 1 AND app_users.user_id
=users.id
//这个条件很有问题。不明白是用它来干什么
and app_users.user_id not in(app_users.user_id))
SELECT users.* FROM `users` where exists (select 1 from friendships where friendships.user_id = 3 and friendships.id=users.id)
and not exists
not in (select 1 from app_users where app_users.app_id = 1 AND app_users.user_id
=users.id
//这个条件很有问题。不明白是用它来干什么
and app_users.user_id not in(app_users.user_id))
app_users => app_id,user_id
users => id
现在是要获取 friendships.friend_id中的用户没有在app_users存在的并且app_id = 1 总的来说意思是
选择出用户的朋友没有安装应用的朋友app_users 记录如果存在,就是该usre_id已经安装了app_id这个应用了
现在SELECT users.* FROM `users` where id in(select user_id from friendships where friendships.user_id = 3)
and users.id not in (select id from app_users where app_users.app_id = 1 AND app_users.user_id not in(app_users.user_id)) select user_id from friendships where friendships.user_id = 3 => 这个是选择出用户的朋友的ID,第一楼的SQL似乎是id了,那个是有问题select user_id from app_users where app_users.app_id = 1 AND app_users.user_id **这个地方应该怎么做 not in(app_users.user_id**可能是我错了总的意思是选择出用户的朋友ID不存在app_users.user_id中的用户
and users.id not in (select id from app_users where app_users.app_id = 1 AND app_users.user_id != app_users.user_id) 应该是这个SQL后面的not in 变成 != 这个sql转换成join
会有结果吗?
on friendships.id=users.id
left join app_users on app_users.id=users.id
where friendships.user_id = 3 and app_users.app_id = 1 and isnull(app_users.id)
OR
导成*。SQL,上传到www.access911.net/csdn
,用WINRAR压缩