如何在两个表之间执行内部连接 [英] how to perform inner join between two tables

问题描述

我有两张桌子：

问题表

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 ;）; 

解决方案

Prasad Avunoori指向正确的方向 - 你需要一个地方来存储如何每个人都回答了每个问题。

您当前的结果表正在尝试存储汇总结果，但您目前无处存储详细信息！



这是一个有效的例子......警告 - 它相当长



创建一个能保存细节的表格。它需要链接到参加测验的人，并且还链接到已回答的问题。

例如......

   -   这包含按人排序的单个结果 
  CREATE  表结果（
 P_ID  int  不  null ，  -   这将链接到（新）人员表 
 Q_ID  int  不  null ，  -   这将链接到问题表 
 ANSGIVEN  int  不  null ，  -   此人对问题的回答 
 ISCORRECT  bit  不  null  默认  0 ，  -   响应是否正确（1）或错误（0） 
  foreign   key （P_ID） references  Person（P_ID），  -   注意为简单起见，我使用了最简单的键定义 
  foreign   key （Q_ID） references 问题（Q_ID），
 主要 密钥（Q_ID，P_ID）
 ）

外键列P_ID和Q_ID是您将创建的连接的基础。我已经包含了一个主键，在这个阶段并不是绝对必要的，但总是值得考虑（性能）



在你创建那个表之前你会需要某个地方来保存摘要结果和人员详细信息

我在这里创建了一个新表，但这与您原来的结果表类似

   -   将保留回答问题的人的摘要详细信息 
  CREATE  表人（
 P_ID  int   identity （ 1 ， 1 ），  -  为我自动生成唯一标识符 
 PNAME  varchar （ 50 ）不  null ，  -   人名 
 NUMQUESTIONS  int  不  null  默认  0 ，  -   他们已回答了多少问题 - 从0开始 
 NUMCORRECT  int  不  null  默认  0 ，  -   他们得到了多少问题 
 PC_CORRECT  float  不  null   default   0 。 0 ，  -   百分比正确 
  PRIMARY   KEY （P_ID）
）

我创建了这个问题表，我可以运行这些查询（跟随）

   -   将保留问题，答案和正确答案的数量 
  CREATE   TABLE 问题（
 Q_ID  int   identity （ 1 ， 1 ），  -   为我自动生成唯一的问题编号 
问题 varchar （ 50 ） not not   null ，  -   问题 
 ANSWER1  varchar （ 50 ）不  null ，  -   可能的答案......必须全部四个......  
 ANSWER2  varchar （ 50 ）不  null ，
 ANSWER3  varchar （ 50 ） 不  null ，
 ANSWER4  varchar （ 50 ）不  null ，
 CORRECT  int  不  null ，  -   正确答案的编号 
  PRIMARY   KEY （Q_ID）
）

让我们创建一些测试数据。 />
设置此测验的一些用户...

  INSERT   INTO 人员（PNAME） VALUES （'  Adam '）
  INSERT   INTO 人（PNAME） VALUES （'  Benjamin'）
  INSERT   INTO 人（PNAME） VALUES （  Caleb'）
  INSER T   INTO 人（PNAME） VALUES （ '  Daniel'）
  INSERT   INTO 人（PNAME） VALUES （'  Ezra'）

兴趣点 - 我只提供了PNAME列，因为所有其他值都默认为0

现在设置一些问题.. 。

  INSERT   INTO 问题 VALUES （' 问题1'，'   A'，'  B'，'  C'，'  D'， 1 ）
  INSERT   INTO 问题 VALUES （' 问题2'，'  '，'  B'，'   C'，'  D'， 2 ）
  INSERT   INTO 问题 VALUES （' 问题3'，'  A'，'  B'，'  C'，'  D'， 3 ）
  INSERT   INTO 问题 VALUES （' 问题4'，'  A'，'   B'，'  C'，'  D'， 4  ）

兴趣点 - 因为我提供除Q_ID之外的所有值（我不应该尝试插入）我不需要列出我正在使用的列 - 不像插入到人。



最后，设置一些结果

   -    Adam的答案 - 有些错误 
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 1 ， 1 ， 1 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 1 ，< span class =code-digit> 2 ， 1 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 1 ， 3 ， 3 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 1 ， 4 ， 1  ）
 
   -    Benjamin的结果 - 好吧！ 
 < span class =code-keyword> INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 2 ， 1 ， 1  ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 2 ， 2 ， 2 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN）< span class =code-keyword> VALUES （ 2 ， 3 ， 3  ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 2 ， 4 ， 4 ）
 
   -    Caleb的结果 - 他没有完成 
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 3 ， 1 ， 1 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN ） VALUES （ 3 ， 2 ，< span class =code-digit> 1 ）
 
   -    Daniel's结果 - 所有wro ng： - （ 
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN）  VALUES （ 4 ， 1 ， 4 ）
  INSERT   INTO 结果（ P_ID，Q_ID，ANSGIVEN） VALUES （ 4 ， 2 ， 3 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 4 ， 3 ， 2 ）
  INSERT   INTO 结果（P_ID，Q_ID，ANSGIVEN） VALUES （ 4 ，  4 ， 1 ）
 
   -   没有Ezra的结果，他还没有开始 

让我们来看看这些结果。兴趣点，我将给每个表名一个 ALIAS ，例如'结果'将被称为'R'，'问题'将被称为'Q'

  SELECT  P.PNAME，R.ANSGIVEN， Q.CORRECT，R.ISCORRECT 
  FROM 结果R 
  INNER   JOIN 问题Q  ON  R.Q_ID = Q.Q_ID 
  INNER   JOIN 人P  ON  R.P_ID = P.P_ID 
  ORDER   BY  R.P_ID 

给我们

 PNAME ANSGIVEN CORRECT ISCORRECT 
 Adam 1 1 0 
 Adam 1 2 0 
 Adam 3 3 0 
 Adam 1 4 0 
 Benjamin 4 4 0 
 Benjamin 3 3 0 
 Benjamin 2 2 0 
 Benjamin 1 1 $ 
 Caleb 1 1 0 
 Caleb 1 2 0 
 Daniel 3 2 0 
 Daniel 4 1 0 
丹尼尔2 3 0 
 Daniel 1 4 0 

嗯...当我插入测验结果时我没有找到正确的答案，所以我所有的ISCORRECT字段仍为0，这意味着错误或错误。那是不对的！

我们现在就更正了......但是，这个查询很方便向您展示如何从连接表中获取值以更新另一个表。

它还说明了我使用表别名的原因 - 你认为这么清楚吗？

 更新 R 
  SET  ISCORRECT =  CASE   WHEN  R.ANSGIVEN = Q .CORRECT 那么  1   ELSE   0   END  
  FROM 结果R 
  INNER   JOIN 问题Q  ON  R .Q_ID = Q.Q_ID 

现在我们已经准备好了你一直在等待的结果！让我们试试这个查询...

  SELECT  PNAME，COUNT（ANSGIVEN） AS  NUMQUESTIONS，C.NUMCORRECT 
  FROM 人P 
  INNER   JOIN 结果R  ON  R.P_ID = P.P_ID 
  INNER   JOIN （ SELECT  P_ID，COUNT（*） AS  NUMCORRECT 
  FROM 结果 WHERE  ISCORRECT =  1  
  GROUP   BY  P_ID 
）< span class =code-keyword> AS  C  ON  C.P_ID = P.P_ID 
  GROUP   BY  PNAME，C.NUMCORRECT 
  ORDER   BY  PNAME 

兴趣点，第二个INNER JOIN不是表，而是另一个查询。我之所以这样做是因为我只想在这一点上计算出正确答案，但我仍然需要所有结果来回答问题总数



和产生...

 PNAME NUMQUESTIONS NUMCORRECT 
 Adam 4 2 
 Benjamin 4 4 
 Caleb 2 1 

挂在分钟，哪里是以斯拉？丹尼尔在哪里？



好​​的 - 以斯拉没有回答任何问题，但丹尼尔回答了所有4个问题。他弄错了，但他确实回答了他们！



问题是因为我用了 INNER JOIN 子查询。该子查询只返回一个值 WHERE ISCORRECT = 1

所以Daniel和Ezra都不会出现在该列表中。

所以我需要在子查询中执行 LEFT OUTER JOIN 以确保Daniel ......好吧......没有留下out！

  SELECT  PNAME，COUNT（ANSGIVEN） AS  NUMQUESTIONS，C。 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  PNAME，C.NUMCORRECT 
  ORDER   BY  PNAME 

结果是......

 PNAME NUMQUESTIONS NUMCORRECT 
 Adam 4 2 
 Benjamin 4 4 
 Caleb 2 1 
 Daniel 4（null）

那更好。以斯拉仍然不在名单中，但这没关系，因为他没有回答任何问题。但如果我确实希望看到他的结果，我必须将第一个 INNER JOIN 更改为 LEFT OUTER JOIN 以及。



让我们将该选择转换为更新，以便我们可以将结果输入到Person表中。



我们将整个查询包装成一个子查询，我们将调用该子查询RX的结果，然后将JOIN RX加入到Person表中通过P_ID列。

 更新 P  SET  NUMQUESTIONS = RX.NUMQUESTIONS， 
 NUMCORRECT = RX.NUMCORRECT 
  FROM 人P 
  INNER  < span class =code-keyword> JOIN  
（ SELECT  P.P_ID，COUNT（ANSGIVEN） AS  NUMQUESTIONS，C.NUMCORRECT  AS  NUMCORRECT 
  FROM  Person 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 

* Groan * - 现在我收到错误

Quote：

无法插入值列'NUMCORRECT'为NULL，表'Person';列不允许空值。

记住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年电影中的角色七兄弟的七个新娘

[ ^ ]而不是来自任何其他书籍

最简单的方法就是创建一个视图并使用视图名称代替您的表名，

当您从sql server创建视图时，您也会发现加入代码，

享受编码

如果为你工作

，则接受它

i have two tables:
questionbank table
qustion answer1 answer2 answer3 answer4 correct_ans sub_name


result table
username name correctanswer totalquestions percentage result sub_name

i 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";);

解决方案

Prasad Avunoori is pointing you in the right direction - You need somewhere to store how each person has answered each question.
Your current results table is attempting to store summary results, but you currently have nowhere to store the detail!

Here's is a worked example ... warning - it's rather long

Create a table that will hold the detail. It needs to link to the Person who took the quiz and and also link to the Question that was answered.
For example...

-- This holds the individual results by Person by Question
CREATE TABLE Results(
	P_ID int not null,	-- this will link to a (new) Person table
	Q_ID int not null,	-- this will link to the Question table
	ANSGIVEN int not null,	-- The response that the person gave to the question
	ISCORRECT bit not null default 0,	-- Whether or not the response was correct (1) or wrong (0)
	foreign key (P_ID) references Person(P_ID),	-- Note I've used the simplest definition of keys for simplicity
	foreign key (Q_ID) references Question(Q_ID),
	primary key (Q_ID, P_ID)
)

The foreign key columns P_ID and Q_ID are fundamental to the joins you will create. I've included a Primary key which isn't strictly necessary at this stage, but is always worth considering (for performance)

Before you can create that table you will need somewhere to hold the summary results and Person details
I've created a new table here, but this is similar to your original Results table

-- Will hold summary details of the people answering questions
CREATE TABLE Person(
	P_ID int identity(1,1),		-- Unique identifier is automatically generated for me
	PNAME varchar(50) not null,	-- Person's name
	NUMQUESTIONS int not null default 0,	-- How many questions they have answered - starts at 0
	NUMCORRECT int not null default 0,		-- How many questions they got right
	PC_CORRECT float not null default 0.0,	-- Percentage correct
	PRIMARY KEY (P_ID)
)

And I created this Questions table so that I could run these queries (to follow)

-- Will hold the questions, answers and number of correct answer
CREATE TABLE Question(
	Q_ID int identity(1,1),	-- Unique question number is automatically generated for me
	QUESTION varchar(50) not null,	-- The question
	ANSWER1 varchar(50) not null,	-- Possible answers ... must have all four ...
	ANSWER2 varchar(50) not null,
	ANSWER3 varchar(50) not null,
	ANSWER4 varchar(50) not null,
	CORRECT int not null,	-- The number of the correct answer
	PRIMARY KEY(Q_ID)
)

Let's create some test data.
Set up some Users of this quiz ...

INSERT INTO Person (PNAME) VALUES ('Adam')
INSERT INTO Person (PNAME) VALUES ('Benjamin')
INSERT INTO Person (PNAME) VALUES ('Caleb')
INSERT INTO Person (PNAME) VALUES ('Daniel')
INSERT INTO Person (PNAME) VALUES ('Ezra')

Point of interest - I've only provided the PNAME column as all the other values have default 0
Now set up some questions...

INSERT INTO Question VALUES ('Question 1', 'A', 'B', 'C', 'D', 1)
INSERT INTO Question VALUES ('Question 2', 'A', 'B', 'C', 'D', 2)
INSERT INTO Question VALUES ('Question 3', 'A', 'B', 'C', 'D', 3)
INSERT INTO Question VALUES ('Question 4', 'A', 'B', 'C', 'D', 4)

Point of interest - because I'm providing all of the values except Q_ID (which I should never attempt to insert) I don't need to list the columns I'm using - unlike the insert into Person.

Finally, Set up some results

-- Adam's answers - Some right some wrong
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,2,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,3,3)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(1,4,1)

-- Benjamin's Results - All right!
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,2,2)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,3,3)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(2,4,4)

-- Caleb's results - he didn't finish
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(3,1,1)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(3,2,1)

-- Daniel's results - All wrong :-(
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,1,4)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,2,3)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,3,2)
INSERT INTO Results (P_ID, Q_ID, ANSGIVEN) VALUES(4,4,1)

-- No results for Ezra, he hasn't started yet

Let's look at those results. Point of interest, I'm going to give each table name an ALIAS e.g. 'Results' will be called 'R' and 'Question' will be called 'Q'

SELECT P.PNAME, R.ANSGIVEN,Q.CORRECT, R.ISCORRECT
FROM Results R
INNER JOIN Question Q ON R.Q_ID=Q.Q_ID
INNER JOIN Person P ON R.P_ID=P.P_ID
ORDER BY R.P_ID

which gives us

PNAME		ANSGIVEN	CORRECT		ISCORRECT
Adam 		1 			1 		0 
Adam 		1 			2 		0 
Adam 		3 			3 		0 
Adam 		1 			4 		0 
Benjamin 	4 			4 		0 
Benjamin 	3 			3 		0 
Benjamin 	2 			2 		0 
Benjamin 	1 			1 		0 
Caleb 		1 			1 		0 
Caleb 		1 			2 		0 
Daniel 		3 			2 		0 
Daniel 		4 			1 		0 
Daniel 		2 			3 		0 
Daniel 		1 			4 		0

Hmm... when I was inserting the Results of the quiz I didn't look up the correct answer, so all my ISCORRECT fields are still 0, which means false or wrong. And that's not right!
We'll correct that now ... However, this query is handy for showing you how to get values from a joined table to update another table.
It also demonstrates why I used Table Aliases - so much clearer don't you think?

UPDATE R
SET ISCORRECT = CASE WHEN R.ANSGIVEN = Q.CORRECT THEN 1 ELSE 0 END
FROM Results R
INNER JOIN Question Q ON R.Q_ID=Q.Q_ID

Now we're ready to get the Results you've been waiting for! Let's try this query...

SELECT PNAME, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.NUMCORRECT
FROM Person P
INNER JOIN Results R ON R.P_ID=P.P_ID
INNER 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 PNAME, C.NUMCORRECT
ORDER BY PNAME

Point of interest, that second INNER JOIN isn't to a table, but to another query. I've done that because I only want to count the correct answers in that bit, but I still need all of the results for the total count of questions answered

And that produces ...

PNAME		NUMQUESTIONS		NUMCORRECT
Adam 		4 				2 
Benjamin 	4 				4 
Caleb 		2 				1 

Hang on minute, where's Ezra? Where's Daniel?

Ok - Ezra didn't answer any questions, but Daniel answered all 4 questions. He got them all wrong, but he did answer them!

The problem is because I used INNER JOIN on the sub-query. That sub-query only returns a value WHERE ISCORRECT = 1
So neither Daniel nor Ezra is going to appear in that list.
So I need to do a LEFT OUTER JOIN onto the sub-query to make sure Daniel ... well ... isn't left out!

SELECT PNAME, COUNT(ANSGIVEN) AS NUMQUESTIONS, C.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 PNAME, C.NUMCORRECT
ORDER BY PNAME

And the results are ...

PNAME		NUMQUESTIONS	NUMCORRECT
Adam 		4 				2 
Benjamin 	4 				4 
Caleb 		2 				1 
Daniel 		4 				(null)

That's better. Ezra still isn't in the list, but that's ok because he didn't answer any questions. But if I did want to see his results as well I'd have to change that first INNER JOIN to a LEFT OUTER JOIN as well.

Let's convert that select into an update so we can get the results onto our Person table.

We will wrap up that entire query into a sub query, we will "call" the results of that sub-query RX, and then JOIN RX to the Person table via the P_ID columns.

UPDATE P SET NUMQUESTIONS=RX.NUMQUESTIONS,
            NUMCORRECT = RX.NUMCORRECT
FROM Person P
INNER 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_ID

*Groan* - now I get an error

Quote:

Cannot insert the value NULL into column 'NUMCORRECT', table 'Person'; column does not allow nulls.

Remember Daniel's results earlier came out as

PNAME	NUMQUESTIONS	NUMCORRECT
Daniel 		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 P
INNER 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_ID

Now let's look at the Person table and see what we have ...

SELECT * FROM Person P order by PNAME

which gives

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

Notice 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]

Simplest way just create a view and use view name in place of your table name,
and when you creating your view from sql server you will find joining code also,
enjoy coding
accept it if its work for you

这篇关于如何在两个表之间执行内部连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！