如何在两个表之间执行内部连接

本文介绍了如何在两个表之间执行内部连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有两张桌子： 问题表 qustion答案1 answer2 answer3 answer4 correct_ans sub_name 结果表 用户名name correctanswer totalquestions百分比结果sub_name i想要在这两者之间执行内连接，以便结果表从问题库表中读取结果并在sql server 2008.please帮助中如何处理。 我也有c＃的代码： SqlCommand cmd = new SqlCommand（@ SELECT COUNT（result.username）AS correct_ans FROM result INNER JOIN questionbank ON result.Q_id = questionbank.Q_id AND result.User_Ans = questionbank.Correct_Ans AND result.username = + username + ）; SqlCommand cmd1 = new SqlCommand（@ SELECT COUNT（Q_id）AS totalquestions FROM questionbank ;）; 解决方案 记住Daniel先前的结果是 PNAME NUMQUESTIONS NUMCORRECT Daniel 4 （null） 和我们将这些列定义为 NUMQUESTIONS int < b > not null < / b > 默认0， NUMCORRECT int非null默认为0，我们可以返回表并删除'not null default 0'位或者我们现在可以使用ISNULL功能来处理它。我会这样做... 更新 P SET NUMQUESTIONS = ISNULL （RX.NUMQUESTIONS， 0 ）， NUMCORRECT = ISNULL（RX.NUMCORRECT， 0 ） FROM 人P INNER JOIN （ SELECT P.P_ID，COUNT（ANSGIVEN） AS NUMQUESTIONS， C.NUMCORRECT AS NUMCORRECT FROM 人P INNER JOIN 结果R ON R.P_ID = P.P_ID LEFT OUTER JOIN （ SELECT P_ID，COUNT（*） AS NUMCORRECT FROM 结果 WHERE ISCORRECT = 1 GROUP BY P_ID ） AS C ON C.P_ID = P.P_ID GROUP BY P.P_ID，C.NUMCORRECT ） AS RX ON P.P_ID = RX.P_ID 现在让我们看一下Person表，看看我们有什么... SELECT * FROM P P PORT 给出 P_ID PNAME NUMQUESTIONS NUMCORRECT PC_CORRECT 1 Adam 4 2 0 2 Benjamin 4 4 0 3 Caleb 2 1 0 4 Daniel 4 0 0 5 Ezra 0 0 0 请注意我没有更新PC_CORRECT - 这是一个练习 - 它是 - 它可以添加到t他同样更新上面的查询，或在您的C＃程序中单独完成。 提示 - 注意 null 值并除以零！ [如果我通过在Person表中选择名字无意中冒犯了任何人，请注意它们实际上是1954年电影中的角色七兄弟的七个新娘 [ ^ ]而不是来自任何其他书籍 ，则接受它i have two tables: questionbank tablequstion answer1 answer2 answer3 answer4 correct_ans sub_name result tableusername name correctanswer totalquestions percentage result sub_namei want to perform inner join between these two so that result read from questionbank table and store in result table how i do it in sql server 2008.please help.also i have the code for c#:SqlCommand cmd = new SqlCommand(@"SELECT COUNT(result.username) AS correct_ans FROM result INNER JOIN questionbank ON result.Q_id = questionbank.Q_id AND result.User_Ans = questionbank.Correct_Ans AND result.username = " + username + " "); SqlCommand cmd1 = new SqlCommand(@"SELECT COUNT(Q_id) AS totalquestions FROM questionbank";); 解决方案 Remember Daniel's results earlier came out asPNAMENUMQUESTIONSNUMCORRECTDaniel 4 (null)and we defined those columns as NUMQUESTIONS int <b>not null</b> default 0,NUMCORRECT int not null default 0,We could go back to the table and remove the 'not null default 0' bits or we can handle it here and now with the ISNULL function. I'll do that ...UPDATE P SET NUMQUESTIONS=ISNULL(RX.NUMQUESTIONS,0), NUMCORRECT = ISNULL(RX.NUMCORRECT,0)FROM Person PINNER JOIN (SELECT P.P_ID, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.NUMCORRECT AS NUMCORRECT FROM Person P INNER JOIN Results R ON R.P_ID=P.P_ID LEFT OUTER JOIN (SELECT P_ID, COUNT(*) AS NUMCORRECT FROM Results WHERE ISCORRECT = 1 GROUP BY P_ID ) AS C ON C.P_ID=P.P_ID GROUP BY P.P_ID, C.NUMCORRECT ) AS RX ON P.P_ID=RX.P_IDNow let's look at the Person table and see what we have ...SELECT * FROM Person P order by PNAMEwhich givesP_IDPNAMENUMQUESTIONSNUMCORRECTPC_CORRECT1 Adam 4 2 0 2 Benjamin 4 4 0 3 Caleb 2 1 0 4 Daniel 4 0 0 5 Ezra 0 0 0Notice I haven't updated PC_CORRECT - that's an exercise for you - it can be added into the same update query above, or done separately in your C# program. Hint - Watch out for null values and division by zero![If I have inadvertently offended anyone by the choice of names in the Person table, be aware they are actually characters from the 1954 film "Seven Brides For Seven Brothers"[^] and not from any other book] 这篇关于如何在两个表之间执行内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！