独立子查询,可以返回一个单独,或多个值(包括表),它不依赖于它所属的外部查询。
返回单独值子查询例子,返回作者为John最后论题所有回复的记录:
SELECT
*
FROM
dbo.SubTopic
WHERE
TopicId
=
(
SELECT MAX ( [ TopicId ] ) FROM dbo.Topic WHERE [ Author ] = ' John '
)
(
SELECT MAX ( [ TopicId ] ) FROM dbo.Topic WHERE [ Author ] = ' John '
)
也可以写成,首先宣告一个变量,把某作者发表最后的论题的ID保存至这个变量中,然后再查询所以回复记录等于这个变量:
DECLARE
@LastPubTopic
INT
=
(
SELECT
MAX
(
[
TopicId
]
)
FROM
dbo.Topic
WHERE
[
Author
]
=
'
John
'
)
SELECT * FROM dbo.SubTopic WHERE TopicId = @LastPubTopic
SELECT * FROM dbo.SubTopic WHERE TopicId = @LastPubTopic
返回多值子查询例子,那不能用等于(=)而是使用谓词(IN)。下面是查询回复大于等于5篇的回复记录:
SELECT
*
FROM
dbo.SubTopic
WHERE
TopicId
IN
(
SELECT [ TopicId ] FROM dbo.Topic WHERE Reply >= 5
)
(
SELECT [ TopicId ] FROM dbo.Topic WHERE Reply >= 5
)