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

weixin_44835628 2019-08-02 02:58:42
希望把一个购物表buyitem合并,把最后一次购买记的与perso合并n成一个最后购买状态表表 两个表和希望的结果请见附图。想用java的jdbc来读取最后。的结果特此求助。谢谢。,
...全文
36 点赞 收藏 4
写回复
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
    

回复
发动态
发帖子
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
社区公告
暂无公告