缺失交叉连接表（选择全部并选择或仅插入缺失行）

I have two table and have to fill in a list of missing values in one of the table based on the other one. First table has student's information and the second table has Grade related info, Grade and Grade description.

我有两个表,必须根据另一个表填写其中一个表中的缺失值列表。第一张表有学生的信息,第二张表有成绩相关信息,成绩和成绩说明。

Table One

ID    Name    yearWithUs       Grade     Course Level
1     Jim     2004             4          4
2     Jim     2004             4          1
2     Jim     2003             3          3
4     Jim     2002             2          3
4     Jim     2002             2          1
3     Jim     2001             1          2
3     Jim     2001             1          1

Table two -- logic is.. A Student in a higher Course Level can change to a lower Course Level at anytime during the semester. And It can only go downward 1 level at a time. Example: Jim in his second grade first was assigned to attend course in level 3. He need to attend course in level 2 first before he can attend course in level 1. Means. Row for course level 2 at jim's first grade is missing.

表二 - 逻辑是......在较高的课程水平的学生可以在学期期间的任何时间更改为较低的课程水平。它一次只能下降1级。例如:Jim在他的二年级第一年级被分配到3级课程。他需要先参加2级课程才能参加1级课程。吉姆一年级的2级课程缺失。

Table Two

ID    Grade      Grade_Desc        Course Level    Course Desc
1     1          First Grade          1              Basic
2     1          First Grade          2              Normal
3     1          First Grade          3              Hard
4     1          First Grade          4              Expert
5     2          Second Grade         1
6     2          Second Grade         2 
7     2          Second Grade         3
8     2          Second Grade         4
.     .             .
.     .             .
.     .             .

Logic of Table Two

表二的逻辑

ID    Grade      Grade_Desc        Course Level    Possible Move
1     1          First Grade          1              Null
2     1          First Grade          2              1   
3     1          First Grade          3              2 
4     1          First Grade          4              3 

Ouptput one ... how to use select statement to return Jim's Grade?

Ouptput one ...如何使用select语句返回Jim的成绩?

ID    Name    Grade_Desc            Grade     yerWithUs    Course Level
1     Jim    Fourth Grade           4           2004            4
2     Jim    Fourth Grade           4           2004            3
3     Jim    Fourth Grade           4           2004            2
4     Jim    Fourth Grade           4           2004            1
5     Jim    Third Grade            3           2003            3
6     Jim    Second Grade           2           2002            3
7     Jim    Second Grade           2           2002            2
8     Jim    Second Grade           2           2002            1
9     Jim    First Grade            2           2001            2
10    Jim    First Grade            2           2001            1

Output Two..How to retrieve only the missing row into a new temp table?

输出二..如何只将缺失的行检索到新的临时表中?

ID    Name    Grade_Desc            Grade     yearWithUs   Course Level
2     Jim    Fourth Grade           4           2004            3
3     Jim    Fourth Grade           4           2004            2
7     Jim    Second Grade           2           2002            2

I am currently is using a messy Cursor Statement to do it. The structure looks really messy and hard to debug return errors. I did a lot of research, and saw people use Cross Join to fill the missing portion which looks really clean (See example below)... I have tried the script it myself in many different way by using the cross join example below...obviously, I failed. I found a similar question in *..but I am not able to understand how does it work and why without looking at the data....I need help to understand how to use cross join to rerun missing row? and I am open to any other possible solution.

我目前正在使用凌乱的Cursor语句来做到这一点。该结构看起来非常混乱,很难调试返回错误。我做了很多研究,并看到人们使用Cross Join来填充看起来非常干净的缺失部分(参见下面的示例)...我已经通过使用下面的交叉连接示例以许多不同的方式自己尝试了脚本。显然,我失败了。我在*中发现了一个类似的问题..但是我无法理解它是如何工作的以及为什么不查看数据....我需要帮助才能理解如何使用交叉连接来重新运行丢失的行?我对任何其他可能的解决方案持开放态度。

 "SELECT  calendar.Date,
         Category.Cat,
         Score = ISNULL(Scores.Score, 0)
  FROM   Calendar
         CROSS JOIN Catogory
    LEFT JOIN Scores
        ON Scores.Cat = Category.Cat
        AND Scores.Date = Calendar.Date
    WHERE   Calendar.DayOfMonth = 1;"

Inserting missing rows with a join

使用连接插入缺少的行

Thank You

3 个解决方案

#1

This will produce that output:

这将产生该输出:

select distinct name, grade, Grade_Desc 
  from one 
 cross join two 

#2

If select is all you want then:

如果选择是你想要的全部:

Select row_number() over(order by (select 1)) as id, * from
(Select distinct name from t1)t1
cross join t2

Here is fiddle http://sqlfiddle.com/#!6/a8a42/3

这是小提琴http://sqlfiddle.com/#!6/a8a42/3

#3

Try this out:

试试这个:

Create #Temp

DECLARE @Name VARCHAR(100) = 'Jim'

SELECT  ROW_NUMBER() OVER (ORDER BY B.Grade DESC,B.CourseLevel DESC) ID,
        A.Name,
        B.Grade_Desc,
        B.Grade,
        A.YearWithUs,
        B.[Course Level] 
INTO #temp
FROM
(
    SELECT DISTINCT Name,YearWithUs,Grade
    FROM TableOne
    WHERE Name = @Name
) A
INNER JOIN TableTwo B
ON A.Grade = B.Grade

Output One

SELECT *
FROM #temp

Output Two into #OutputTwo(temp table)

SELECT A.* INTO #OutputTwo
FROM #temp A
LEFT JOIN TableOne B
ON A.Grade = B.Grade
AND A.[Course Level] = B.[Course Level]
WHERE A.Grade IS NULL AND A.[Course Level] IS NULL