mysql 查询选择取决于 2 个表格 [英] mysql query select depending on 2 tabels

问题描述

我已经找到了我想要的 php 代码.但是我的网站速度太慢了，因为它不断重复，一遍又一遍，

I've reached the php code that I want. But my website is too slow because it keeps repeating and round over and over again,

$pages= mysql_query("SELECT * FROM ex_instagram_p WHERE type = '".follow."' AND active=1 And username !='".$username."' ORDER BY cpc DESC" );
$prow = mysql_fetch_array($pages);



Do{
$dollowed = mysql_query("SELECT * FROM exchanges WHERE user = '".$username."' AND exid = '".$prow['id']."'");

$followed = mysql_num_rows($dollowed);
;}while($followed > 0 && $prow = mysql_fetch_array($pages));

其实我可以稍微解释一下代码，我需要从第一个表中选择最高 CPC 行但也要确保:

Actually I can explain the code a little more, I need to choose the maximum CPC row from the first table But also to make sure that:

type = '".follow."' AND active=1 And username !='".$username."' ORDER BY cpc DESC"

问题来了，在继续之前，我需要确保第一个表的id"字段在第二个表上没有用户名的记录，

And here comes the problem, before continue I need to make sure that the 'id' field form the first table does't got a record on the second table with the user username,

是否得到结果，它将使用下一行再次重复，一遍又一遍，直到找到满足两个表的结果.这个方法太重了

is it got result it'll repeat again using the next row, over and over until finding a result that satisfies both tables. This method is soooo heavy

我希望得到一个更简单更轻松的方法，谢谢

I hope to get a simpler and lighter way, thanks

我试过这样做，但它不起作用

I've tried to do this but it does not work

$prow= mysql_query("SELECT *
 FROM (mysql_query("SELECT * FROM ex_instagram_p WHERE type = '".follow."' AND active=1         
And username !='".$username."' ORDER BY cpc DESC" );)
INNER JOIN (mysql_query("SELECT * FROM exchanges WHERE user = '".$username."'");)
ON ex_instagram_p.id=exchanges.exid";); 

推荐答案

您可以在一个查询中完成此操作.遵循逻辑有点困难，但我认为这是您想要的查询:

You can do this in one query. It is a bit hard to follow the logic, but I think this is the query that you want:

SELECT i.*
FROM ex_instagram_p i join
     exchanges e
     on i.id = e.exid
WHERE i.type = '".follow."' AND i.active=1 And
      i.username <> '".$username."' and e.user = '".$username."'
ORDER BY i.cpc DESC
LIMIT 1;

在数据库中执行工作通常比在应用程序中循环遍历行要快得多.毕竟，这正是数据库的优势所在——管理和查询大量数据.

Doing the work in the database is generally much faster than cycling through rows in the application. After all, that is what databases are good for -- managing and querying large amounts of data.

这篇关于mysql 查询选择取决于 2 个表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！