时间戳

首页 > 解决方案 > 如何从连接表中返回一行

sql - 如何从连接表中返回一行

问题描述

字段类型：

Date1 = varchar(10)
Date2 = int

表：

tblName

Num(pk)         Name            Open
0909            John Doe        Yes
0908            Jason Kelly     Yes


tblOwes

Num(pk)     Date1           Date2           Amnt        Type
0909        11/14/2020      20201114        350.00      12
0909        11/15/2020      20201115        250.00      12
0909        10/29/2020      20201029        650.00      13
0909        09/12/2020      20200912        898.00      12
0908        08/29/2020      20200829        650.00      12
0908        09/15/2020      20200915        250.00      12
0908        09/14/2020      20200914        350.00      12
0908        08/12/2020      20200812        898.00      13

询问：

select tn.Num, tn.Name, to.Date1, to.Amnt
from tblName tn
inner join tblOwes to
    on tn.Num = to.Num
where to.Date1 = (select top 1 to.Date1 from tblOwes where to.Num = tn.Num order by to.Date1 Desc)

对于Type 为 12 且 Open 为 Yes的每个帐户，我只想要一个条目tblOwes，即最新的按列Date1tblName

期望的结果：

Num     Name            Date1       Amnt        Type        Open
0909    John Doe        11/15/2020  250.00      12          Yes
0908    Jason Kelly     09/15/2020  250.00      12          Yes

现在它为 John 返回四个条目，为 Jason 返回四个条目

标签： sqlsql-serverinner-join

解决方案

也许这是您正在寻找的查询

with o_cte as (
    select *, row_number() over (partition by num order by date1 desc) as rn
    from tblOwes
    where [Type]=12)
select n.Num, t.[Name], o.Date1, o.Amnt, o.[Type], n.[Open]
from tblName n
     join o_cte o on n.Num = o.Num
where n.[Open]='Yes'
      and o.rn=1
order by n.Num desc;

或者，如果 tblNames 表中的 'Name' 是唯一的（跨越 'num'），那么 PARTITION BY 将为 n.[Name] 并且查询可能如下所示

with o_cte as (
    select o.*, row_number() over (partition by n.[Name] order by date1 desc) as rn
    from tblName n
         join tblOwes o on n.Num = o.Num
    where o.[Type]=12
          and n.[Open]='Yes')
select n.Num, t.[Name], o.Date1, o.Amnt, o.[Type], n.[Open]
from tblName n
     join o_cte o on n.Num = o.Num
where 
      and o.rn=1
order by n.Num desc;

推荐阅读