SQL从多个表或连接中选择

I have two tables from which I need to get data in the same SELECT output. The thing is that I need to limit the amount of results.

我有两个表，需要从中获取相同SELECT输出中的数据。问题是我需要限制结果的数量。

Say I have an ID column that is unique in table1, but in table2 it has many rows with that ID.

假设我有一个ID列在表1中是唯一的，但是在表2中它有很多行有这个ID。

Now I just want to list how many different IDs I have in table1 and some other information stored in table2.

现在我只想列出表1中有多少不同的id以及表2中存储的其他信息。

How can I get the desired output I show in the end?

我如何才能得到我最后显示的所需输出?

To make my idea clear I used a "messenger" database for an example.

为了使我的想法清晰，我使用了一个“信使”数据库作为示例。

Tables

表


Id_thread            Date
1                    13Dic
2                    12Dic


Id_thread            Message        Name
1                    Hi             Someone
1                    Hi to you      Someone
2                    Help me?       Someother
2                    Yes!           Someother

Desired output

期望输出值


T1.Id_thread         T2.Name     T1.Date
1                    Someone     13Dic
2                    Someother   12Dic

5 个解决方案

#1

Use a JOIN and GROUP BY:

使用JOIN和GROUP BY:

SELECT t1.Id_thread, t2.Name, t1.Date
FROM t1
JOIN t2 ON t1.Id_thread = t2.Id_thread
GROUP BY t1.Id_thread

Note that if Name is the same for all rows in t2 that have the same Id_thread, that column probably should be in t1. If you fix that, you don't need the JOIN.

注意，如果t2中具有相同Id_thread的所有行的名称相同，则该列可能应该位于t1。如果您修复了它，您不需要连接。

#2

I'd join and use distinct:

我会加入并使用独特的:

SELECT DISTINCT t1.id_thread, t2.name, t1.date
FROM   t1
JOIN   t2 ON t1.id_thred = t2.id_thread

#3

Try this:

试试这个:

SELECT DISTINCT T1.Id_thread, T2.Name, T1.Date 
FROM T1 
LEFT OUTER JOIN T2 ON T1.Id_thread = T2.Id_thread

#4

select T1.Id_thread,T2.Name,T1.Date from T1 
inner join T2 on T1.Id_thread = T2.Id_thread
group by T1.Id_thread
order by T1.Id_thread

#5

You haven't specified how you want the limit the results from Table 2. Considering you just want one row, you can use a CROSS APPLY:

您还没有指定要如何限制表2的结果。考虑到你只想要一行，你可以使用交叉应用:

Select T1.Id_thread,T2Table.Name,T1.Date From T1 
Cross Apply (Select Top 1 T2.Name From T2 Where T2.Id_thread=T1.Id_thread) T2Table

You can specify other conditions in the inner Select statement if you wish.

如果愿意，可以在inner Select语句中指定其他条件。

#1