SQL查询语句，内连接，外连接

这几天做软件综合设计的项目，我负责的是服务端，因为目前打算从前端转型到后台，所以需要补足后台相关的知识。关于项目方面的东西就不介绍了，因为文章的主题是SQL，直接给出数据表的设计图。

表之间的连接线表示外键。

以项目中的查询为例，讲述嵌套查询语句以及表之间的连接。

1、获取某一问题的所有回答（回答者、回答时间、回答内容）

分析：

回答者username在User表，回答时间post_time在Post表，回答内容content在Answer表，将数据拆分到不同的表目的是为了减少冗余，但是查询起来就不方便了。这种情况下就要连接User、Post、Answer三张表。那怎么连呢？Post表中uid和aid是外键，分别引用到User表和Answer表，这就是我们连接三个表的桥梁。

解答：

假设要查找qid为9的问题的所有回答

SELECT username, post_time, content
FROM User as U, Post as P, Answer as A
Where U.uid = P.uid AND P.aid = A.aid AND A.qid = 9

这里使用的是内连接（INNER JOIN），也称为等值连接。

2、获取用户所提问题的信息（提问时间、标题、内容、qid、回答数）

分析：

提问时间ask_time在Ask表，标题title和内容detail在Question表，qid在Question、Ask和Answer表都有，回答数需要用到COUNT函数统计Answer表中相同qid的行数。

这个查询涉及到Ask、Question、Answer三张表。麻烦的地方在于直接对Answer表使用COUNT函数只能统计出有回答的问题的回答数量，对于那些没有回答的问题则无能为力。此时我们要使用外连接（OUTER JOIN）；内连接只显示能够连接上的行，而外连接能把连接不上的行也显示出来。外连接的语法格式为：FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id。

上面的table2就是要连接的Answer表，现在问题是table1怎么来。

解答：

假设要查找uid为9的用户所提的所有问题的信息。

步骤1：构造table1

SELECT Q.qid, Q.title, Q.detail, A.ask_time
FROM Question AS Q, Ask  AS A
WHERE Q.qid = A.qid AND A.uid = 9

步骤2：

SELECT T.ask_time, T.title, T.detail, T.qid, COUNT(ANS.aid) AS answers
FROM table1 as T LEFT OUTER JOIN Answer AS ANS
WHERE T.qid = ANS.qid
GROUP BY T.qid

将步骤1的查询语句代入到步骤2中，得到嵌套的查询语句：

SELECT T.ask_time, T.title, T.detail, T.qid, COUNT(ANS.aid) AS answers
FROM (
    SELECT Q1.qid, Q1.title, Q1.detail, A.ask_time
    FROM Ask AS A, Question AS Q1
    WHERE A.qid = Q1.qid AND A.uid = $uid
) AS T LEFT OUTER JOIN Answer AS ANS
ON T.qid = ANS.qid
GROUP BY T.qid

这样的查询语句称为包含子查询的查询语句。子查询不仅能够包含在FROM子句中，也还能包含在WHERE子句中。

3、获取附近问题的信息（提问者，提问时间，经纬度，标题，内容，回答数）

分析：

客户端会将用户的经度和纬度发送过来，我要做的是筛选出以用户为圆心的，半径为3km范围以内的问题。也就是说要在查询语句中加上条件判断。由于又涉及到问题的回答数，我们依然需要外连接Answers表通过COUNT（Answers.aid）来计数。

步骤1：筛选出经度范围为[115, 117]，纬度范围为[37, 40]内的问题

SELECT * FROM question AS Q1 
WHERE 115 <= Q1.longitude AND Q1.longitude <= 117 
AND 37 <= Q1.latitude AND Q1.latitude <= 40

记为T1

步骤2:：连接Answers表，统计问题的回答数

SELECT T1.qid, T1.title, T1.detail, T1.longitude, T1.latitude, COUNT(ANS.aid) AS answers
FROM T1 LEFT OUTER JOIN Answers AS ANS
ON T1.qid = ANS.qid
GROUP BY T1.qid

记为T2

步骤3：从User表和Ask表中选出username和ask_time

SELECT U.username, A.ask_time, T2.qid, T2.title, T2.detail, T2.longitude, T2.latitude, answers
FROM USER AS U, Ask AS A, T2
WHERE U.uid = A.uid AND A.qid = T2.qid

将以上查询合并成一条查询语句：

SELECT U.username, A.ask_time, T2.qid, T2.title, T2.detail, T2.longitude, T2.latitude, answers
FROM USER AS U, Ask AS A,
(
	SELECT T1.qid, T1.title, T1.detail, T1.longitude, T1.latitude, COUNT(ANS.aid) AS answers
	FROM (
	        SELECT * FROM question AS Q1 
                WHERE 115 <= Q1.longitude AND Q1.longitude <= 117 
		AND 37 <= Q1.latitude AND Q1.latitude <= 40
	)AS T1 LEFT OUTER JOIN answer AS ANS
	ON T1.qid = ANS.qid
	GROUP BY T1.qid
) AS T2
WHERE U.uid = A.uid AND A.qid = T2.qid;