从SQL Server中的两个表中检索数据

I am trying to retrieve data from two tables in SQL Server.

我试图从SQL Server中的两个表中检索数据。

Following are my two tables.

以下是我的两张桌子。

Registration:

Username Name Age aa Arun 20 bb Balu 15 ss Sai 25

用户名姓名年龄aa Arun 20 bb Balu 15 ss Sai 25
Marks:

dt userid mark 2014-02-10 aa 50 2014-02-11 aa 55 2014-02-09 bb 45

dt userid mark 2014-02-10 aa 50 2014-02-11 aa 55 2014-02-09 bb 45

I need to display the selection something like below.

我需要显示如下所示的选择。

Output:

Name    Username    Date        Marks
Arun    aa      2014-02-11      55
Balu    bb      2014-02-09      45

Please help

Thanks in advance.

提前致谢。

6 个解决方案

#1

Try this query!

试试这个查询!

Select r.Name,r.Username,m.Date,m.Marks from Registration r left join Marks m on r.Username=m.userid

#2

I am assumig this is what u expect...

我觉得这就是你所期待的......

 select substr(reg.username,4,length(reg.username)) as Name,
        min(substr(reg.username,1,2)) as Username,
        max(marks.dt) as Date,
        max(marks.mark) as Marks
        from registration reg, marks marks
        where marks.userid = substr(reg.username,1,2)
group by substr(reg.username,4,length(reg.username));

#3

You need to perform a RIGHT JOIN query something like

您需要执行类似的RIGHT JOIN查询

SELECT Reg.UserName, Reg.Name, Marks.dt, Marks.mark  
FROM Reg
RIGHT JOIN Marks
ON Reg.Username=Marks.userid;

What this does is it matches all the records from your two tables based on the common field between the two (ie your user id ). Since you can have more than one mark record per user id on your marks table, we need the RIGHT join, it will pick up all the matching entries from that table.

它的作用是根据两个表之间的公共字段(即您的用户ID)匹配两个表中的所有记录。由于您的标记表上每个用户ID可以有多个标记记录,因此我们需要RIGHT联接,它将从该表中获取所有匹配的条目。

If you need one on one relationship use the INNER JOIN else go with LEFT OR RIGHT queries

如果您需要一对一关系,请使用INNER JOIN,否则请使用LEFT或RIGHT查询

#4

Can you please try this:

你能试试这个:

select r.name
     , r.username
     , max(m.marks) 
  from Registration r 
  join marks m 
    on r.username=m.username 
 group by 
       m.username
     , m.marks
     , r.name

Hope this helps.

希望这可以帮助。

#5

Please try it

请试一试

select r.Username,r.Name,m.dt as 'Date',m.mark as 'Marks' from Registration r join Marks m on r.Username= m.userid

#6

You can Try this:

你可以尝试这个:

select R.Name,R.Username, M.dt, M.Marks from Registration R inner join 
Marks M on  R.Username = M.Username

Hope this will helps you...

希望这会对你有所帮助......

#1