22,209
社区成员
发帖
与我相关
我的任务
分享如何使用left join 或right join 取最新日期的记录
下图是两个表的字段和记录，如何显示poline 表所有记录和只显示一条vendor 最新日期记录按vendate查。
另外，如果要对poline 的ponum , partnum 筛选又要怎样写语句，谢谢。
...全文
请发表友善的回复…
发表回复
baidu_35289351 2017-01-11
- 打赏
- 举报
他这个，不就是先按送货明显表分类统计，取最大日期，然后用订单明显 关联吗？
不过，楼主的怪怪的。他竟然不统计送货数量，而只取最后1次的数量。
--借楼上数据 SQL2000
declare @poline table(PoNum int,PartNum VARCHAR(100),Qty INT ,[Date] datetime,[DEC] nvarchar(50),Salesid VARCHAR(100));
insert into @poline
select '32001' ,'X222-1',200,'2017/1/3',N'产品1','S01' union all
select '32001' ,'PPPP-1',300,'2017/1/3',N'产品2','S01' union all
select '32002' ,'X222-1',500,'2017/1/6',N'产品1','S02'
declare @vendor table(PoNum int,PartNum VARCHAR(100),[DEC] nvarchar(50),VenQty INT ,VenDate datetime,Venid VARCHAR(100));
insert into @vendor
select '32001' ,'X222-1',N'产品1',100,'2017/1/7','V01' union all
select '32001' ,'X222-1',N'产品1',50,'2017/1/8','V01' union all
select '32001' ,'X222-1',N'产品1',50,'2017/1/8','V01' union all
select '32001' ,'PPPP-1',N'产品2',300,'2017/1/8','V02'
--取送货明显的最后日期和数量
select p.PoNum,p.PartNum,p.Qty,p.Date,p.DEC,vp.VenQty,vp.VenDate from @poline p
left join(select distinct v.PoNum,v.PartNum,v.VenQty,v.VenDate from @vendor v,
(select PoNum,PartNum,max(VenDate) as VenDate from @vendor group by PoNum,PartNum)vv
where v.PoNum=vv.PoNum and v.PartNum= vv.PartNum and v.VenDate=vv.VenDate)vp
on p.PoNum=vp.PoNum and p.PartNum=vp.PartNum
--取送货明显的最后日期，累计送货数量
select p.PoNum,p.PartNum,p.Qty,p.Date,p.DEC,vp.VenQty,vp.VenDate from @poline p
left join(select PoNum,PartNum,max(VenDate) as VenDate, sum(VenQty) as VenQty from @vendor group by PoNum,PartNum)vp
on p.PoNum=vp.PoNum and p.PartNum=vp.PartNum
sky0813 2017-01-11
- 打赏
- 举报
谢谢你们，我测试一下
道素 2017-01-11
- 打赏
- 举报
不明白你为啥非只能用join，下面的写法将两次group变成一次group的方式，有点取巧
将日期和数量何在一起取MAX，日期放前面，那么最新的日期就在最前面，然后再重新拆分两部
但还是找到一种办法
declare @poline table(ponum int,PartNum VARCHAR(100),Qty INT ,[Date] datetime,[DESC] nvarchar(50),Salesid VARCHAR(100));
insert into @poline values
('32001' ,'X222-1',200,'2017/1/3',N'产品1','S01')
,('32001' ,'PPPP-1',300,'2017/1/3',N'产品2','S01')
,('32002' ,'X222-1',500,'2017/1/6',N'产品1','S02')
declare @vendor table(ponum int,PartNum VARCHAR(100),[DESC] nvarchar(50),VenQty INT ,VenDate datetime,Venid VARCHAR(100));
insert into @vendor values
('32001' ,'X222-1',N'产品1',100,'2017/1/7','V01')
,('32001' ,'X222-1',N'产品1',50,'2017/1/8','V01')
,('32001' ,'X222-1',N'产品1',50,'2017/1/8','V01')
,('32001' ,'PPPP-1',N'产品2',300,'2017/1/8','V02')
select *,PARSENAME(vv.newest,1) AS VenQty,PARSENAME(vv.newest,2) AS VenDate FROM @poline AS p
LEFT JOIN (
SELECT v.ponum,v.PartNum, MAX(CONVERT(VARCHAR,v.VenDate,111)+'.'+LTRIM(v.VenQty)) AS newest
FROM @vendor AS v GROUP BY v.ponum,v.PartNum
) AS vv ON vv.ponum=p.ponum AND vv.PartNum=p.PartNum
ponum PartNum Qty Date DESC Salesid ponum PartNum newest VenQty VenDate
32001 X222-1 200 2017-01-03 00:00:00.000 产品1 S01 32001 X222-1 2017/01/08.50 50 2017/01/08
32001 PPPP-1 300 2017-01-03 00:00:00.000 产品2 S01 32001 PPPP-1 2017/01/08.300 300 2017/01/08
32002 X222-1 500 2017-01-06 00:00:00.000 产品1 S02 NULL NULL NULL NULL NULL
Tiger_Zhao 2017-01-11
- 打赏
- 举报
版本是 2000 早不说
要求 vendor 有自增 ID。
要求 vendor 有自增 ID。
SELECT p.PoNum, p.PartNum, p.Qty, p.Date, p.DEC,
v.VenQty, v.VenDate
FROM poline p
LEFT JOIN (
SELECT PoNum, PartNum,
MAX(ID) AS MAX_ID
FROM vendor
GROUP BY PoNum, PartNum
) vg
ON p.PoNum = vg.PoNum
AND p.PartNum = vg.PartNum
LEFT JOIN vendor v
ON vg.MAX_ID = v.ID
/* 筛选
WHERE p.PoNum = 32001
AND p.PartNum = 'PPPP-1'
*/Tiger_Zhao 2017-01-11
- 打赏
- 举报
/* 测试数据
WITH poline(PoNum,PartNum,Qty,Date,DEC) AS (
SELECT 32001,'X222-1',200,'2017-01-03','产品1' UNION ALL
SELECT 32001,'PPPP-1',300,'2017-01-03','产品2' UNION ALL
SELECT 32002,'X222-1',500,'2017-01-06','产品1'
),
vendor(PoNum,PartNum,VenQty,VenDate) AS (
SELECT 32001,'X222-1',100,'2017-01-07' UNION ALL
SELECT 32001,'X222-1', 50,'2017-01-08' UNION ALL
SELECT 32001,'X222-1', 50,'2017-01-08' UNION ALL
SELECT 32001,'PPPP-1',300,'2017-01-08'
)
*/
SELECT p.PoNum, p.PartNum, p.Qty, p.Date, p.DEC,
v.*
FROM poline p
OUTER APPLY (
SELECT TOP 1 VenQty, VenDate
FROM vendor
WHERE PoNum = p.PoNum
AND PartNum = p.PartNum
ORDER BY VenDate DESC
) v
/* 筛选
WHERE p.PoNum = 32001
AND p.PartNum = 'PPPP-1'
*/无筛选结果
PoNum PartNum Qty Date DEC VenQty VenDate
----------- ------- ----------- ---------- ----- ----------- ----------
32001 X222-1 200 2017-01-03 产品1 50 2017-01-08
32001 PPPP-1 300 2017-01-03 产品2 300 2017-01-08
32002 X222-1 500 2017-01-06 产品1 NULL NULL有筛选结果
PoNum PartNum Qty Date DEC VenQty VenDate
----------- ------- ----------- ---------- ----- ----------- ----------
32001 PPPP-1 300 2017-01-03 产品2 300 2017-01-08sky0813 2017-01-11
- 打赏
- 举报
测试了出错，sql2000不支持row_number吗
zbdzjx 2017-01-11
- 打赏
- 举报
vendor表加一列ROW_NUMBER() over(partition by ponum, partnum order by Vendate) rn，poline 表left join它，最后where rn=1。
sky0813 2017-01-11
- 打赏
- 举报
是的，有重复记录的。还有一个表poheard是唯一ponum ，但现不调用此表。
sky0813 2017-01-11
- 打赏
- 举报
是的，表2中不一定有记录，还没送货前是没有记录的，那么这三个栏位显示空值
卖水果的net 2017-01-11
- 打赏
- 举报
表1 中的 第三条记录（2017/1/6 、 产品2），在表 2 中没有相应的记录？
zbdzjx 2017-01-11
- 打赏
- 举报
感觉单纯的用left join 或right join有难度吧。按你给的数据，vendor表中居然有重复记录。
sky0813 2017-01-11
- 打赏
- 举报
看来语句都有些复杂，放在visual studio 有点麻烦，不如在代码中直接更新这3个栏位到poline 表中。只要有新送货记录就更新，你们看是否合常规？
sky0813 2017-01-10
- 打赏
- 举报
sky0813 2017-01-10
- 打赏
- 举报
显示效果如下，后面还要按ponum partnum筛选
sky0813 2017-01-10
- 打赏
- 举报
显示效果如下，后面还要按ponum partnum筛选
