问题描述
限时送ChatGPT账号..我的数据库中有一些表 &我想通过使用 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屋!
更多推荐
[db:关键词]
发布评论