与SQL子查询
大家好我写了下面的查询结果从3个表(合并tblQuestions,tblReplies,tblTechnology
和如下与SQL子查询
SELECT tech.DatePosted,
tech.QuestionID,
tech.TechnologyName,
tblr3.TechID,
tblr3.QuestionTitle,
tblr3.UserName,
tblr3.ReplyCount,
tblr3.viewCount,
tblr3.date,
tblr3.RepliedName
FROM (SELECT tblr1.DatePosted,
tblr1.QuestionID,
tblr2.TechID,
tblr2.QuestionTitle,
tblr2.UserName,
tblr2.ReplyCount,
tblr2.viewCount,
tblr1.date,
tblr1.RepliedName
FROM (SELECT tblq.DatePosted,
tblq.TechID,
tblq.QuestionID,
tblq.RepliedName,
tblq.QuestionTitle,
tblq.UserName,
tblq.ReplyCount,
tblq.viewCount,
tblq.date
FROM (SELECT q.DatePosted,
q.TechID,
q.QuestionID,
q.QuestionTitle,
q.UserName,
q.ReplyCount,
q.viewCount,
r.date,
r.UserName AS RepliedName
FROM tblQuestions AS q, tblReplies AS r
WHERE r.QuestionID = q.QuestionID
AND r.TechID = q.TechID
AND q.TechID = 1) AS tblq,
(SELECT r.QuestionID,
max(r.date) AS PostedDate
FROM tblReplies AS r
GROUP BY QuestionID) AS tblr
WHERE tblq.QuestionID = tblr.QuestionID
AND tblq.date = tblr.PostedDate) AS tblr1
RIGHT OUTER JOIN
(SELECT q.QuestionID,
q.TechID,
q.UserName,
q.viewCount,
q.ReplyCount,
q.QuestionTitle
FROM tblQuestions AS q
WHERE q.TechID = 1) AS tblr2
ON tblr2.QuestionID = tblr1.QuestionID) AS tblr3
LEFT OUTER JOIN
(SELECT q.QuestionID,
q.DatePosted,
t.TechID,
t.TechName AS TechnologyName
FROM tblTechnology AS t, tblQuestions AS q
WHERE q.TechID = t.TechID) AS tech
ON tblr3.TechID = tech.TechID
AND tblr3.QuestionID = tech.QuestionID
AND tech.TechID = 1
这给了我下面的结果如预期,但得到NULL
显示所需要的结果的SQL查询在某些领域,其中数据为存在
代替的NULL
我需要拉出一些数据,其存在于tblQuestions
,也是否有可能减少查询。
从OP的评论:
我试图用replycount and viewcount
沿拉出第q uestiontitle,username,questionid from tblquestions
和张贴基于QuestionID
和技术的ID问题,从tblreplys
最新的回信您的查询基本上是这个:
SELECT
blah
FROM
(
(SELECT
blah
FROM
Questions
INNER JOIN Replies ON QuestionID AND TechID) tblr1
RIGHT JOIN Questions tblr2 ON QuestionID
) tblr3
LEFT JOIN Technologies ON tblr3.tblr1.QuestionID = Technologies.QuestionID
我知道这不是一个有效的查询,但忍受着我。 tblr1
包含问题和答案之间的INNER JOIN
。但是因为salary
问题没有答案,所以这些记录salary
问题的NULL
。问题的RIGHT JOIN
然后引入salary
问题,并存在于tblr3
结果集中,因为它是RIGHT JOIN
,即使tblr1
对于此问题为空。
的LEFT JOIN
再到技术是基于内tblr3
的QuestionID
这是真正的QuestionID
从tblr1
。通过观察在列的列表为tblr3
子查询,如果你改变tblr1.QuestionID
到tblr2.QuestionID
这将确保悬而未决的问题加入到Technology
表
编辑:我觉得下面的查询会给你的结果和简单得多:
WITH LatestReplies AS
(
SELECT
ReplyID,
QuestionID,
ReplyDate,
UserName,
Reply
FROM
(SELECT
ReplyID,
QuestionID,
ReplyDate,
UserName,
Reply,
ROW_NUMBER() OVER (PARTITION BY QuestionID ORDER BY ReplyDate DESC) ReplyOrder
FROM
tblReplies) A
WHERE
ReplyOrder = 1
)
SELECT
tblQuestions.DatePosted,
tblQuestions.QuestionID,
tblTechnology.TechnologyName,
tblQuestions.TechID,
tblQuestions.QuestionTitle,
tblQuestions.UserName,
tblQuestions.ReplyCount,
tblQuestions.ViewCount,
LatestReplies.ReplyDate,
LatestReplies.UserName RepliedName
FROM
tblQuestions
LEFT JOIN tblTechnology ON tblQuestions.TechID = tblTechnology.TechID
LEFT JOIN LatestReplies ON tblQuestions.QuestionID = LatestReplies.QuestionID
解决了毕竟
select
FI.QuestionID,
FI.QuestionTitle,
FI.UserName,
FI.DatePosted,
FI.[date],
FI.RepliedName,
FI.viewCount,
FI.ReplyCount,
FI.ReplyMsg,
TT.TechID,
TT.TechName
from
tblTechnology TT,
(select distinct
TQ.TechID,
TQ.QuestionID,
TQ.QuestionTitle,
TQ.UserName,
TQ.DatePosted,
TR.[date],
TR.UserName as RepliedName,
TQ.viewCount,
TQ.ReplyCount,
TR.ReplyMsg
from
tblQuestions TQ
LEFT OUTER JOIN tblReplies TR ON
TR.TechID=TQ.TechID
and TR.QuestionID = TQ.QUESTIONID
and TR.[date] in (select
MAX(TR.[date])
from
tblReplies TR
group by
TR.QuestionID)
) FI
where
FI.TechID=TT.TechID
and [email protected]
以下
虽然该查询中有一个错误。如果问题1有两个或两个以上的答案,并且问题二有一个或多个答案,如果问题1的答案与问题2的最新答案具有相同的[tblReplies.Date]值,那么问题1将得到多行 – Tobsey 2013-02-13 10:05:43
即使我有更多问题的答案,我也没有获得多行 – Dotnet 2013-02-13 11:15:22
我想如果两个不同问题的两个答案具有完全相同的[日期]值,我相信你会这样做,前提是其中一个答案是它的最新答案题。我建议你测试一下,如果我错了,那么你应该没问题。如果你只有3个答案,1,2和3以及两个问题,1和2.如果问题1的答案是1,问题2的答案是3,那么如果答案1和答案3在答案1中出现,你将得到重复同一时间。查看我创建的示例:http://www.sqlfiddle.com/#!3/cd6c6/1您可以看到第二季度的最新答案与Q1 – Tobsey 2013-02-13 11:38:53
你想用这个查询来实现什么?您的查询不容易,难以猜测。你能发布样本数据和预期结果吗?可能有更简单的方法来返回预期的结果,而不是使用如此多的子查询 – 2013-02-12 14:18:07
'rs',您可以检查我的图像以获取我想要实现的内容 – Dotnet 2013-02-12 14:20:42
您的图像显示预期数据,但不包含任何业务逻辑或条件。解释你的问题以及你试图用这个查询来达到的目标。 – 2013-02-12 14:22:31