【新手求助】关于查询合并表求助。

[weixin_44835628]

希望把一个购物表buyitem合并，把最后一次购买记的与perso合并n成一个最后购买状态表表 两个表和希望的结果请见附图。想用java的jdbc来读取最后。的结果特此求助。谢谢。，

[RINK_1]

SELECT A.*,B.ITEM AS LASTITEM,B.BUYTIME AS LASTBUYTIME 
FROM PERSON A
LEFT JOIN
(SELECT * 
FROM BUYITEM AS A
WHERE NOT EXISTS (SELECT 1 FROM BUYITEM WHERE NAME=A.NAME AND BUYTIME>A.BUYTIME)) AS B ON A.NAME=B.NAME
    

[weixin_44835628]

非常感谢您，一会到家试试。

[二月十六]

--测试数据
if not object_id(N'Tempdb..#person') is null
	drop table #person
Go
Create table #person([id] int,[name] nvarchar(22),[sex] int)
Insert #person
select 1,N'张三',1 union all
select 2,N'李四',0 union all
select 3,N'王五',0
GO
if not object_id(N'Tempdb..#buyitem') is null
	drop table #buyitem
Go
Create table #buyitem([name] nvarchar(22),[item] nvarchar(22),[buytime] Date)
Insert #buyitem
select N'张三',N'手表','2009-12-30' union all
select N'王五',N'手机','2010-11-25' union all
select N'张三',N'电脑','2010-8-25'
Go
--测试数据结束
SELECT
    id,#person.name,sex,t.item AS lastitem,t.buytime AS lastbuytime
FROM
    #person
    LEFT JOIN
        (
            SELECT
                *,
                ROW_NUMBER() OVER (PARTITION BY
                                       name
                                   ORDER BY
                                       buytime DESC
                                  ) rn
            FROM
                #buyitem
        ) t
            ON t.name = #person.name
               AND rn = 1;

[weixin_44835628]

谢谢版主，确实好强。