SQL Server 2008 加入 [英] SQL Server 2008 joining

问题描述

我的数据库中有一些表 &我想通过使用 join 将它们组合起来，但我遇到了问题.让我说清楚.我已将 Id_num(列名)指定为 Stu_info 表中的主键 &其他表有一个 Id_num 列来存储 ID 号.

I have some tables in my database & I want to combine them by using join but I have got a problem. Let me make it clear. I have assigned Id_num (column name) as primary key in the Stu_info table & the other tables have an Id_num column to store ID number.

所有表都具有将存储 ID 号的列的相同名称 (Id_num).我试图创建一个视图来连接表以从特定表中获取特定数据.如果我使用 INNER JOIN 那么它不会因为一些空值而显示任何行.但是我使用了 LEFT OUTER JOIN 它正在工作.但它产生了一个不能接受的问题.

All tables have same name (Id_num) of that column which will store ID number. I have tried to make a view to connect tables to get specific data from specific tables. If I use INNER JOIN then it doesn't show any rows because of some null vales. However I have used LEFT OUTER JOIN it is working. But it makes a problem which is not acceptable.

代码:

SELECT     
    dbo.Stu_info.Id_num, dbo.Development_fee.Dvf, dbo.Stu_info.Stu_name, 
    dbo.Stu_info.Dep_name, dbo.Tuition_fee.Acy, dbo.Tuition_fee.Tui_fee, 
    dbo.Registration_fee.Reg_fee, dbo.Form_fill_up_fee.Acy AS Expr1, 
    dbo.Form_fill_up_fee.FFF, dbo.Examination_fee.E_typ, dbo.Examination_fee.Exm_fee, 
    dbo.monthly_instal.Instm, dbo.monthly_instal.Paid
FROM
    dbo.Stu_info 
LEFT OUTER JOIN
    dbo.Tuition_fee ON dbo.Stu_info.Id_num = dbo.Tuition_fee.Id_num 
LEFT OUTER JOIN
    dbo.Registration_fee ON dbo.Stu_info.Id_num = dbo.Registration_fee.Id_num 
LEFT OUTER JOIN
    dbo.Examination_fee ON dbo.Stu_info.Id_num = dbo.Examination_fee.Id_num 
LEFT OUTER JOIN
    dbo.monthly_instal ON dbo.Stu_info.Id_num = dbo.monthly_instal.Id_num 
LEFT OUTER JOIN
    dbo.Development_fee ON dbo.Stu_info.Id_num = dbo.Development_fee.Id_num 
LEFT OUTER JOIN
    dbo.Form_fill_up_fee ON dbo.Stu_info.Id_num = dbo.Form_fill_up_fee.Id_num

例如Development_fee表的Dvf列有一个数据&Tuition_fee 表的Tui_fee 列有六个数据，学生的ID 为LAB10161117.

For example Dvf column of Development_fee table has one data & Tui_fee column of Tuition_fee table has six data for a student who has following ID LAB10161117.

现在如果您尝试使用以下代码

Now if you try to use following code

Select * from View_1
Where Id_num = 'LAB10161117' 

要获取以下 ID LAB10161117 的数据，那么您假设获取以下信息.喜欢

to get data for following ID LAB10161117 then you suppose to get following information. Like

ID number : LAB10161117
-----------------------------------------
Development Fee  ---- Tuition Fee ---- Other columns
10000            ----  1000
Null             ----  1000
Null             ----  1000
Null             ----  1000
Null             ----  1000
Null             ----  1000

因为Development_fee 表的Dvf 列有一个LAB10161117 的条目，即10000 &Tui_fee 表的Tuition_fee 列有LAB10161117 的六个条目，分别是1000, 1000, 1000, 1000, 1000, 1000.但它不像上面的信息那样显示.当我执行以下代码以显示该 ID 的数据时

Because Dvf column of Development_fee table has one entry for LAB10161117 which is 10000 & Tui_fee column of Tuition_fee table has six entries for LAB10161117 which are 1000, 1000, 1000, 1000, 1000, 1000. But it doesn't show like above information. When I execute following code to show data for that ID

Select * from View_1
Where Id_num = 'LAB10161117' 

然后它显示错误的信息，如

then it shows wrong information like

ID number : LAB10161117
-----------------------------------------
Development Fee  ---- Tuition Fee ---- Other columns

10000            ----  1000
10000        ----  1000
10000       ----  1000
10000        ----  1000
10000       ----  1000
10000       ----  1000

这是不对的！这意味着与 Tuition_fee 表匹配，它有六行，它再次生成了与 Development_fee 表相同的值 &再次(六次)尽管 Development_fee 表只有一行用于具有以下 ID LAB10161117 的人.

which is not right! It means to match with Tuition_fee table which has six rows it has been generated same value of Development_fee table again & again (six times) though Development_fee table has only one row for that person who has following ID LAB10161117.

我只想准确地显示表中存在的行&我想停止行重复.你能帮我解决这个问题吗?请帮助我摆脱这个问题.谢谢.

All I want to show row exactly what is present there in table & I want to stop row duplication. Would you please help me to solve this problem? Please help me to get rid of this problem. Thank you.

推荐答案

您应该考虑使用 UNION 而不是 JOIN.

You should consider to work with UNION rather then a JOIN.

简化您的查询，联合可能看起来像这样

Simplifying your query the union might look like this

SELECT           dbo.Stu_info.Id_num, dbo.Development_fee.Dvf
FROM             dbo.Stu_info 
LEFT OUTER JOIN  dbo.Tuition_fee ON dbo.Stu_info.Id_num = dbo.Tuition_fee.Id_num 
UNION
SELECT           dbo.Stu_info.Id_num, dbo.Registration_fee.Reg_fee
FROM             dbo.Stu_info 
LEFT OUTER JOIN  dbo.Registration_fee ON dbo.Stu_info.Id_num = dbo.Registration_fee.Id_num 

这篇关于SQL Server 2008 加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！