请大神帮忙一个查询

[wolai66]

有两张表A,B.

需要查询的条件，A.fty=b.fty and a.code=b.code ,然后a的年份，要>=b的年，a的月份>=b的月份，如果有多个月份，选最大的那个。
例如图中，a.code=80560003 a.date=10/17/2017,对应的b的date有三个，要选符合条件最大的那个也就是17-10-01.
怎么做查询，最终是要以A表记录为准，连接B表。多谢。

[卖水果的net]

楼主百度一下 “outer apply ” 正好解决你的问题。 PS：如果希望别人帮你写出语句，建议你给建表语句，和 insert 形式的数据。

[听雨停了]

SELECT *
FROM   (
           SELECT *,
                  ROW_NUMBER() OVER(
                      PARTITION BY a.fty,
                      a.date,
                      a.code,
                      a.price ORDER BY MONTH(b.date) DESC
                  ) AS rn
           FROM   a
                  LEFT JOIN b
                       ON  a.fty = b.fty
                           AND a.code = b.code
                           AND YEAR(a.date) = YEAR(b.date)
                           AND MONTH(a.date) >= MONTH(b.date)
       ) AS a
WHERE  a.rn = 1

大致就是这样啦

[RINK_1]

select * from table_A A
outer apply
(select top 1 * from table_B where A.fty=fty and A.code=code and YEAR(A.[date])>=YEAR([date]) and MONTH(A.[date])>=MONTH([date]) order by MONTH([date]) desc) as B

[wolai66]

赶飞机，休假啦，下个星期再验证，上分。多谢啦

[wolai66]

出错啊

引用 2 楼 qq_37170555 的回复:

SELECT *
FROM   (
           SELECT *,
                  ROW_NUMBER() OVER(
                      PARTITION BY a.fty,
                      a.date,
                      a.code,
                      a.price ORDER BY MONTH(b.date) DESC
                  ) AS rn
           FROM   a
                  LEFT JOIN b
                       ON  a.fty = b.fty
                           AND a.code = b.code
                           AND YEAR(a.date) = YEAR(b.date)
                           AND MONTH(a.date) >= MONTH(b.date)
       ) AS a
WHERE  a.rn = 1

大致就是这样啦

Msg 8156, Level 16, State 1, Line 18 The column 'fty' was specified multiple times for 'a'.

[chenjiecao123]

SELECT a.fty AS A11fty,a.[date] AS A11date,a.code AS A11code,a.price AS A11price,t.B11date
FROM dbo.A11 AS a
OUTER APPLY(
SELECT TOP 1 b.fty AS B11fty,b.[date] AS B11date,b.code AS B11code
FROM dbo.B11 AS b
WHERE a.code = b.code
ORDER BY b.[date] DESC
) AS t

不过楼主好像说错了
例如图中，a.code=80560003 a.date=10/17/2017,对应的b的date有三个，要选符合条件最大的那个也就是17-10-01. 应该是18-12-11吧?