2.1w+
社区成员
- 主页
【新手求助】关于查询合并表求助。
希望把一个购物表buyitem合并，把最后一次购买记的与perso合并n成一个最后购买状态表表
两个表和希望的结果请见附图。想用java的jdbc来读取最后。的结果特此求助。谢谢。，
...全文
请发表友善的回复…
发表回复
weixin_44835628 2019-08-02
谢谢版主，确实好强。
二月十六 2019-08-02
--测试数据
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 2019-08-02
非常感谢您，一会到家试试。
RINK_1 2019-08-02
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
