22,300
社区成员




--测试数据
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;
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