编写一个查询来获取一个数组，并在子查询中使用该数组

What I am trying to do is get the results from the first query pass them into an array and then use them in a sub query. Both queries work separately if I input the id's into the sub query manually. Is there a way of linking these two queries?

我要做的是获取来自第一个查询的结果，将它们传递到一个数组中，然后在子查询中使用它们。如果我手动将id输入到子查询中，这两个查询都可以独立工作。是否有连接这两个查询的方法?

I have used this code

我用过这段代码

$result = mysql_query("SELECT v2.video_id as v2id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC"); 

$values = array();

while ($row = mysql_fetch_array( $result )) {
  $values[] = $row['v2id'];
}

echo join(", ", $values);

$resultone = mysql_query("SELECT * FROM videos WHERE video_id IN (' . join(',', $values). ')"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

Thanks for your help.

谢谢你的帮助。

4 个解决方案

#1

Yes, it's called subquery (and what you use is not subquery, because it does not contain one query inside another.

是的，它被称为子查询(您使用的不是子查询，因为它不包含一个查询在另一个查询中。

SELECT * 
FROM videos 
WHERE video_id IN (
    SELECT v2.video_id 
    FROM VideoTags AS v1 
    JOIN VideoTags AS v2 USING ( tag_id ) 
    WHERE v1.video_id =1 AND v1.video_id <> v2.video_id 
    GROUP BY v2.video_id ORDER BY COUNT( * ) DESC
)

#2

This is enough

这是足够的

$resultone = mysql_query("SELECT * FROM videos WHERE video_id IN (SELECT v2.video_id as v2id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT (*) DESC)"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

But before using query like this, check your mysql version for subquery support.

但是在使用此类查询之前，请检查mysql版本的子查询支持。

#3

You could do this in one query.

您可以在一个查询中完成此操作。

$innersql = "SELECT v2.video_id FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id ) WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC"); 

$sql= mysql_query("SELECT * FROM videos WHERE video_id IN (" . $innersql .")"); 

while ($row = mysql_fetch_array( $resultone )) {
  echo "name ".$row['video_name'];
}

Hope this helps.

希望这个有帮助。

#4

SELECT * FROM VideoTags AS v1 JOIN VideoTags AS v2 USING ( tag_id )   
inner join video vi on vi.video_id = v1.video_id   
WHERE v1.video_id =1 AND v1.video_id <> v2.video_id GROUP BY v2.video_id ORDER BY COUNT( * ) DESC");

The count(*) does not make sence ?

伯爵(*)不讲道理吗?

#1