在某些字段上选择具有条件的另一个表的每个行连接的最小值

I have 2 table such:

我有2个这样的表:

       UserTelTable                        (userTable)        
CardNo  ID   tel     telorder            ID    Name     .....(other columns )
1      101   7777       4                101   Danny     
2      101   6666       1                102   Tanya     
3      101   5555       2                103   Susan     
4      102   4444       6                104   Gordon    
5      103   1234       1                  
6      104   4567       2                  
7      104   5678       3                 
8      101   4141       6                
9      101   5151       3                  
10     102   0000       3                 
11     102   1111       5                
12     104    7890      4                
13     104    1212      1

want the result to be like this:

希望结果如下:

Name   .....some columns of UserTAble   tel# TELPriorinty(Minimum)
Danny    .................                   5555    
Tanya    ...................                 0000
Susan     ................                   1234
Gordon     ...............                   1212

where the result showing the minimum value of "telorder" for each "userid",username,userfamily,.. and filtered by the date selected.

其中结果显示每个“userid”,用户名,userfamily,...的“telorder”的最小值,并按所选日期过滤。

2 个解决方案

#1

I'm assuming you have a mistake in the first row of the expected result. Use the subquery with group by to find the lowest telorder in UserTelTable and then appropriately join the tables.

我假设你在预期结果的第一行有一个错误。将子查询与group by一起使用以在UserTelTable中查找最低的telorder,然后适当地连接表。

select ut.*, utt.*
from userTable ut
join UserTelTable utt on ut.id = utt.id
join 
(
  select id, min(telorder) min_telorder
  from UserTelTable
  group by id
) t on utt.id = t.id and
       utt.telorder = t.min_telorder

#2

You could join the linked table with the a subquery on min value

您可以使用最小值的子查询加入链接表

  select a.name, b.tel
  from userTable a
  inner join UserTelTable b on a.id =b.id 
  inner join (

  select ID, min(telorder) telord
  from UserTelTable
  group by ID
  ) t on t.id =a.id and t.telord = b.telorder

#1