PHP+MYSQL 子查询 交集（一个数据表查询结果供另一个 SQL查询调用）

什么是MYSQL 子查询

MYSQL 子查询是将一个 SELECT 语句的查询结果作为中间结果，供另一个 SQL 语句调用。

查询语句如下：

$sql = "SELECT * FROM e_user WHERE id IN (SELECT uid FROM subject WHERE fun>80)";

等同于

SELECT * FROM e_user WHERE id IN(1,2)

一个例子，查询在指定分类ID下的包含了指定关键词的文章

<?php

$catsarr=array("做网站","建网站","网站制作","网站建设","自己做网站");

$showcatarr = "('53','98','211','100','95','102','99','103','267','216','104','212','1','1892','3130','140')";

for($i=0;$i<count($catsarr);$i++){?>

<div class="xwz-wzlist-item">

<div class="xwz-wzlist-title">

<h3><?php echo $catsarr[$i];?>知识</h3>

</div>

<div class="ask-list">

<?php

$vars = $wpdb -> get_results("SELECT * FROM `wp_posts` where post_status='publish' and post_type='post' and (post_title like '%".$catsarr[$i]."%') and ID IN (SELECT object_id FROM `wp_term_relationships` WHERE term_taxonomy_id IN ".$showcatarr.") ORDER BY ID DESC limit 0,8", ARRAY_A);



echo "<ul>";



foreach($vars as $var){

echo '<li><a href="'.get_permalink($var['ID']).'" target="_blank">'.$var['post_title'].'</a></li>';

};

echo "</ul>";

?>

</div>

</div>

<?php }?>

标量子查询

$sql = "SELECT * FROM e_user WHERE id=(SELECT min(uid) FROM subject)";

查询之后插入到另一个表

INSERT INTO other(id,sid,name,age,subject,total) SELECT null,s.id, s.name,s.age,j.subject,j.total from SUBJECT as j LEFT JOIN student as s on s.id=j.sid

除了子查询之外，还有内连接查询。

INNER JOIN（内连接）：

取得两个表中存在连接匹配关系的记录

$sql="SELECT * FROM subject as a INNER JOIN e_user as b ON a.uid=b.id";

//也可以用以下方法

SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid

LEFT JOIN (左外连):会取得左表（table1）全部记录，即使右表（table2）并无对应匹配记录

$sql="SELECT * FROM subject as a LEFT JOIN e_user as b ON a.uid=b.id";

在上面的例子中，对于右表中没有对应匹配的数据记录，其所有的列都被置为 NULL，因此要查询这部分记录（如在上面例子中体现为查找 id=4 这类无对应用户的记录），可以附加 IS NULL 条件：

$sql="SELECT * FROM subject as a LEFT JOIN e_user as b ON a.uid=b.id WHERE b.id IS NULL";

RIGHT JOIN(右外连): 会取得右表（table2）全部记录，即使左表（table2）并无对应匹配记录

$sql="SELECT * FROM subject as a RIGHT JOIN e_user as b ON a.uid=b.id";

除了常用的两个表连接之外，SQL（MySQL） JOIN 语法还支持多表连接

SELECT article.aid,article.title,user.username,type.typename FROM article INNER JOIN user

ON article.uid=user.uid INNER JOIN type ON article.tid=type.tid

//LEFT JOIN

SELECT article.aid,article.title,user.username,type.typename FROM article LEFT JOIN user

ON article.uid=user.uid LEFT JOIN type ON article.tid=type.tid

//RIGHT JOIN

SELECT article.aid,article.title,user.username,type.typename FROM article RIGHT JOIN user

ON article.uid=user.uid RIGHT JOIN type ON article.tid=type.tid

WHERE 表达式中运算符说明：