从表中获取唯一值 [英] Get Unique values from tables

问题描述

大家好

我正在使用sql server数据库.我有两个表studentinfo和studentfees.

studentinfo具有以下值
==============================

Hi All

I am using sql server database. I have two tables studentinfo and studentfees.

studentinfo has following values
===============================

studentid  studentname address    dob          isactive
    1         a        chennai    10.02.1988     no
    2         b        madurai    15.2.1987      no
    1         a        din        10.02.1988     yes
    3         c        trichy     16.2.1986      no
    2         b        madurai    15.2.1987      yes

学生费具有以下值
================================

studentfees has following values
=================================

studentid    class  fee     isactive
   1           I     2000    no
   2           II    4000    no
   1           I     458     yes
   3           III   545     no
   2           II    4000    yes

我想将两个表都链接起来，结果将如下所示
< pre>

studentid学生名地址dob班级费用已激活
1 a din 10.02.1988 I 458是
3 c trichy 16.2.1986 III 545 no
2 b madurai 15.2.1987 II 400是

每当更新记录时，两个表都将被插入，而先前的记录将处于非活动状态.我需要使用Studentid使用不同的值，但它不应显示重复的值，而只需要显示最后插入的记录即可显示

请帮帮我

谢谢&问候

Justin Diraviam.I

I want to get both table to be linked and the result will be look like following
<pre>

studentid studentname address dob class fee isactive
1 a din 10.02.1988 I 458 yes
3 c trichy 16.2.1986 III 545 no
2 b madurai 15.2.1987 II 400 yes

whenever the update the records both tables will be inserted and previous record will be inactive. I need a distinct values using studentid but it should not display duplicate values, only the last inserted records needed to display

Please help me

Thanks & Regards

Justin Diraviam.I

推荐答案

贾斯汀，你好，

您的数据具有冗余性.您在第二张表中的单个学生证的费用"有所不同.因此，当您将使用左联接来获取合并的数据时，您将获得重复项.由于无法跟踪您最后插入的行，因此有两个选择:首先删除重复的条目，其次，您应该添加一列以记录每个条目的时间戳&那么您将能够获得唯一值而无需删除重复项:

对于第一个选项，我为您创建了一个示例查询:

我假设您的表分别为s1和s2.

Hi Justin,

you have redundancy in your data. You have "fee" different for a single student id in your second table. So when you will use left join to fetch merged data, you will get duplicates. Because of there is no way to track which row you''ve inserted last, you go with two options: first remove the duplicate entries, second you should add a column to record timestamp for every entry & then you would be able to get the unique values without removing duplicates:

for the first option, I''ve created a sample query for you:

I''ve assumed your tables as s1 and s2 accordingly.

select s1.studentid, s1.studentname, s1.address, s1.dob, s2.class, s2.fee, s2.isactive

from s1 left join s2 on s1.studentid=s2.studentid;

希望这会有所帮助！

快乐编码:)
Sunny_K

Hope this helps!

Happy Coding :)
Sunny_K

这篇关于从表中获取唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！