SQL 查询单价的问题

nebuung 2018-06-13 08:38:13
图一是数据库的订单明细,怎么才能做出图二那样能根据时间的desc查出物料的最近三次采购订单号及采购单价?(图为单物料,应用场景为多物料)
听说使用游标可以搞定,但没找到思路,求大神指点,贴代码更可爱。


...全文
599 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
nebuung 2018-06-13
引用 4 楼 sinat_28984567 的回复:
有点问题,没看清是根据时间排序的,这样改一下
;WITH cte AS (
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY fitem ORDER BY fdate DESC) rn
FROM #T
)
SELECT a.fitem,
       a.funit,
       a.fcurr,
       a.fpono AS fpono1,
       a.fprice AS fprice1,
       b.fpono AS fpono2,
       b.fprice AS fprice2,
       c.fpono AS fpono3,
       c.fprice AS fprice3
FROM cte a
    LEFT JOIN cte b
        ON b.fitem = a.fitem
           AND a.rn = b.rn - 1
    LEFT JOIN cte c
        ON c.fitem = a.fitem
           AND a.rn = c.rn - 2
WHERE a.rn = 1;
学习了,谢谢斑竹。
回复
xiaoxiangqing 2018-06-13
先查出最近3次数据,排好序,再分组显示
回复
二月十六 2018-06-13
有点问题,没看清是根据时间排序的,这样改一下
;WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY fitem ORDER BY fdate DESC) rn
FROM #T
)
SELECT a.fitem,
a.funit,
a.fcurr,
a.fpono AS fpono1,
a.fprice AS fprice1,
b.fpono AS fpono2,
b.fprice AS fprice2,
c.fpono AS fpono3,
c.fprice AS fprice3
FROM cte a
LEFT JOIN cte b
ON b.fitem = a.fitem
AND a.rn = b.rn - 1
LEFT JOIN cte c
ON c.fitem = a.fitem
AND a.rn = c.rn - 2
WHERE a.rn = 1;


回复
二月十六 2018-06-13
游标效率比较低,试试这个
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([fdate] Date,[fpono] nvarchar(27),[fsupplier] int,[ftype] nvarchar(22),[fitem] nvarchar(25),[funit] nvarchar(24),[fqty] int,[fcurr] nvarchar(23),[fprice] decimal(18,12),[famount] decimal(18,12))
Insert #T
select '2017-12-11',N'1424-61',13056,N'MS',N'碳带',N'EACH',100,N'CNY',10.940171,1094.0171 union all
select '2017-10-16',N'1217-61',13056,N'MS',N'碳带',N'EACH',50,N'CNY',75.21368,3760.684 union all
select '2017-07-06',N'875-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-07-06',N'876-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-07-06',N'880-61',13056,N'MS',N'碳带',N'EACH',5,N'CNY',75.213675,376.068375 union all
select '2017-06-05',N'765-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-06-05',N'766-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-04-05',N'565-61',61001,N'IN',N'12636',N'EACH',225000,N'USD',0.46,103500 union all
select '2017-04-05',N'566-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-04-05',N'567-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-04-05',N'568-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-03-09',N'484-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-03-09',N'484-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2017-03-06',N'447-61',13056,N'MS',N'碳带',N'EACH',10,N'CNY',75.2137,752.137 union all
select '2016-12-16',N'262-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2016-12-16',N'263-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-12-16',N'264-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-12-16',N'265-61',61001,N'IN',N'12636',N'EACH',270000,N'USD',0.47147,127296.9 union all
select '2016-12-16',N'266-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2016-12-16',N'267-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2016-12-16',N'268-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2016-12-16',N'269-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.46,82800 union all
select '2016-12-07',N'200-61',13056,N'MS',N'碳带',N'EACH',3,N'CNY',75.213675,225.641025 union all
select '2016-11-04',N'85-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-10-14',N'36-61',13056,N'MS',N'碳带',N'EACH',26,N'CNY',75.2137,1955.5562 union all
select '2016-09-30',N'10-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-09-30',N'11-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-09-30',N'11-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-09-30',N'12-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.47147,84864.6 union all
select '2016-09-30',N'13-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.4715,84870 union all
select '2016-09-30',N'14-61',61001,N'IN',N'12636',N'EACH',180000,N'USD',0.4715,84870 union all
select '2016-09-30',N'9-61',61001,N'IN',N'12636',N'EACH',360000,N'USD',0.47147,169729.2
Go
--测试数据结束
;WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY fitem ORDER BY fprice DESC) rn
FROM #T
)
SELECT a.fitem,
a.funit,
a.fcurr,
a.fpono AS fpono1,
a.fprice AS fprice1,
b.fpono AS fpono2,
b.fprice AS fprice2,
c.fpono AS fpono3,
c.fprice AS fprice3
FROM cte a
LEFT JOIN cte b
ON b.fitem = a.fitem
AND a.rn = b.rn - 1
LEFT JOIN cte c
ON c.fitem = a.fitem
AND a.rn = c.rn - 2
WHERE a.rn = 1;


回复
nebuung 2018-06-13
引用 1 楼 sinat_28984567 的回复:
先用rownum分组取各个物料的前三名形成rn列自增列作为临时表,然后临时表自己关联自己三次每次都是rn=rn-1 物料=物料这样关联,然后就取到三个订单号和三个单价依次排列的结果了。 楼主如果能给出测试数据就更可爱了,可以帮你写代码语句,贴图片没法弄测试数据。
斑竹大大您要的测试数据,我这里拿了两个fitem 如果用游标能搞吗,我对游标在这的应用有兴趣 fdate fpono fsupplier ftype fitem funit fqty fcurr fprice famount 2017-12-11 00:00:00.000 1424-61 13056 MS 碳带 EACH 100 CNY 10.940171 1094.0171 2017-10-16 00:00:00.000 1217-61 13056 MS 碳带 EACH 50 CNY 75.21368 3760.684 2017-07-06 00:00:00.000 875-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-07-06 00:00:00.000 876-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-07-06 00:00:00.000 880-61 13056 MS 碳带 EACH 5 CNY 75.213675 376.068375 2017-06-05 00:00:00.000 765-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-06-05 00:00:00.000 766-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-04-05 00:00:00.000 565-61 61001 IN 12636 EACH 225000 USD 0.46 103500 2017-04-05 00:00:00.000 566-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-04-05 00:00:00.000 567-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-04-05 00:00:00.000 568-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-03-09 00:00:00.000 484-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-03-09 00:00:00.000 484-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2017-03-06 00:00:00.000 447-61 13056 MS 碳带 EACH 10 CNY 75.2137 752.137 2016-12-16 00:00:00.000 262-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 263-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-12-16 00:00:00.000 264-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-12-16 00:00:00.000 265-61 61001 IN 12636 EACH 270000 USD 0.47147 127296.9 2016-12-16 00:00:00.000 266-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 267-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 268-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-16 00:00:00.000 269-61 61001 IN 12636 EACH 180000 USD 0.46 82800 2016-12-07 00:00:00.000 200-61 13056 MS 碳带 EACH 3 CNY 75.213675 225.641025 2016-11-04 00:00:00.000 85-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-10-14 00:00:00.000 36-61 13056 MS 碳带 EACH 26 CNY 75.2137 1955.5562 2016-09-30 00:00:00.000 10-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 11-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 11-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 12-61 61001 IN 12636 EACH 180000 USD 0.47147 84864.6 2016-09-30 00:00:00.000 13-61 61001 IN 12636 EACH 180000 USD 0.4715 84870 2016-09-30 00:00:00.000 14-61 61001 IN 12636 EACH 180000 USD 0.4715 84870 2016-09-30 00:00:00.000 9-61 61001 IN 12636 EACH 360000 USD 0.47147 169729.2
回复
二月十六 2018-06-13
先用rownum分组取各个物料的前三名形成rn列自增列作为临时表,然后临时表自己关联自己三次每次都是rn=rn-1 物料=物料这样关联,然后就取到三个订单号和三个单价依次排列的结果了。 楼主如果能给出测试数据就更可爱了,可以帮你写代码语句,贴图片没法弄测试数据。
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2018-06-13 08:38
社区公告
暂无公告